Exists and IN, Not Exists and Not IN, LAG() and LEAD()
来源:互联网 发布:hdt记牌器mac 编辑:程序博客网 时间:2024/06/07 09:59
select distinct score_level from t_test order by score_level;
create table T_SCORE_LEVEL(level_id int, level_name varchar2(30));
insert into T_SCORE_LEVEL values(1,'Excelent');
insert into T_SCORE_LEVEL values(2,'Good');
insert into T_SCORE_LEVEL values(3,'Passed');
insert into T_SCORE_LEVEL values(4,'NonPassed');
insert into T_SCORE_LEVEL values(null,'Worse');
--The example of the subquery in ORDER BY statement
select score_level,max(score),min(score) from t_test group by score_level
--left outer join T_SCORE_LEVEL on T_SCORE_LEVEL.LEVEL_ID = t_test.score_level
order by (select T_SCORE_LEVEL.LEVEL_NAME from T_SCORE_LEVEL where T_SCORE_LEVEL.LEVEL_ID =t_test.score_level);
update t_test set score_level= 5 where score<40;
--Difference between EXISTS and IN, Not Exists and Not IN
select count(*) from t_test where not exists (select * from t_score_level where t_score_level.level_id = t_test.score_level);
select count(*) from t_test where t_test.score_level not in (select level_id from t_score_level);
select count(*) from t_test where exists (select * from t_score_level where t_score_level.level_id = t_test.score_level);
select count(*) from t_test where t_test.score_level in (select level_id from t_score_level);
select count(*) from t_test left outer join t_score_level on t_score_level.level_id = t_test.score_level;
select level_id from t_score_level order by level_id desc;
select * from t_score_level where exists (select null from dual);
select * from t_score_level where not exists (select null from dual);
select * from t_test where exists (select null from dual);
select * from t_test where not exists (select null from dual);
select * from t_score_level t1 where not exists (select null from t_score_level t2 where t1.level_id=t2.level_id);
select * from t_score_level t1 where t1.level_id not in (select t2.level_id from t_score_level t2);
select * from t_score_level t1 where exists (select 1 from t_score_level t2 where t1.level_id=t2.level_id);
select * from t_score_level t1 where t1.level_id in (select t2.level_id from t_score_level t2);
--the example for funcation LAG() and LEAD()
with a as (select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 23 id,'c' name from dual
union
select 14 id,'d' name from dual
union
select 8 id,'e' name from dual
)
select id,name,lag(id,2,'') over(order by name),lead(id,1,'')over(order by name) from a
- Exists and IN, Not Exists and Not IN, LAG() and LEAD()
- NOT EXISTS and NOT IN区别
- EXISTS and NOT EXISTS
- oracle exists and not exists
- in and exists
- MYSQL IF NOT EXISTS AND IF EXISTS
- SQL 范围查询问题遇见与扩展学习 IN; NOT IN; NOT EXISTS;BETWEEN AND
- oracle exists and not exist
- oracle exists and not exist
- mySql:in,exists,not exists
- IN, EXISTS, NOT IN, NOT EXISTS
- in、not in、exists、not exists
- exists,in 和 not in ,not exists
- IN/NOT IN---EXISTS/NOT EXISTS理解
- in、exists、not in、not exists
- oracle in,exists,not in,not exists
- in,exists,not in ,not exists效率
- Exists,not Exists,in,not in
- 理解Javascript_06_理解对象的创建过程
- 从devkit8000分析设备驱动程序工作过程
- 理解Javascript_09_Function与Object
- Android将联系人读取到LISTVIEW中遇到的问题!
- JavaScript调用函数的方法
- Exists and IN, Not Exists and Not IN, LAG() and LEAD()
- 算法导论15.2-1
- 开博
- ExtJS Combobox 如何设置默认和取值问题
- 在ScrollView中加入ListView时
- 大家一起动手达双赢~~哈哈
- PHP 的一些高效写法
- 格式金额
- 有时候批处理是你的高效帮手,别忘了