SQL Cookbook 系列 - 高级查找

1. 给结果集分页
2. 跳过表中n行
3. 在外联接中使用or逻辑
4. 确定哪些行是彼此互换的
5. 选择前n个记录
6. 找到包含最大值最小值的记录
7. 存取“未来”行
8. 轮换行值
9. 给结果分等级
10. 抑制重复

 

1.给结果集分页

db2/oracle/sqlserver:

select sal from (

select row_number() over(order by sal) as rn,sal

from emp

) where rn between 0 and 5;

mysql/postgresql:

select sal from emp

order by sal limit 5 offset 0;

Note: offset指定要跳过的行数,常用

 

2.跳过表中n行

db2/oracle/sqlserver:

select sal from (

select row_number() over(order by sal) as rn,sal

from emp

) x where mod(x.rn,2)=1;

mysql/postgresql:

select sal from (

select sal,(select count(*) from emp b where b.sal<=a.sal) as rn

from emp a

) x where mod(x.rn,2)=1;

Note:有用的案例，是隔n行取数据

 

3.在外联接中使用or逻辑

第一种方案：

select e.*,d.deptno,d.loc

from dept d left join emp e

on (d.deptno=e.deptno and(e.deptno=10 or e.deptno=10))

order by 2;

第二种方案：

select e.*,d.deptno,d.loc

from dept d left join (

select * from emp where deptno in (10,20)

) e on (e.deptno=d.deptno) order by 2;

Note：都是不错的方法

 

4.确定哪些行是彼此互换的

select v1.* from V v1,V v2

where v1.test=v2.test

and v2.test=v1.test

and v1.test<=v2.test;

Note:一个表中的记录存在交换列值的情况

 

5.选择前n个记录

db2/oracle/sqlserver:

select sal from (

select row_number() over(order by sal) as rn,sal

from emp

) x where x.rn<=5;

mysql/postgresql:

select sal from (

select sal,(select count(*) from emp b where b.sal<=a.sal) as rn

from emp a

) x where x.rn<=5;

Note：有用的案例

 

6.找到包含最大值和最小值的记录

db2/oracle/sqlserver:

select * from (

select *, min(sal)over() min_sal,max(sal)over() max_sal

from emp

) x where sal in (min_sal,max_sal);

mysql/postgresql:

select * from emp where sal in (

(select min(sal) from emp),

(select max(sal) from emp)

);

Note：常用的案例

 

7.存取“未来”行

db2/mysql/postgresql/sqlserver:

select ename,sal,hiredate from (

select a.ename,a.sal,a.hiredate,

(select min(hiredate) from emp b

where b.hiredate>a.hiredate

and b.sal>a.sal) as next_sal_grte,

(select min(hiredate) from emo b

where b.hiredate>a.hiredate) as next_hire

from emp a

) x where next_sal_grte=next_hire;

oracle:

select ename,sal,hiredate from (

select ename.sal,hiredate,

lead(sal)over(order by hiredate) next_sal

from emp

) where sal<next_sal;

Note：有顺序的数据查找

 

8.轮换行值

db2/mysql/postgresql/sqlserver:

select e.ename,e.sal,coalesce(

(select min(sal) from emp d where d.sal>e.sal),

(select min(sal) from emp)

) as forward,coalesce(

(select max(sal) from emp d where d.sal<e.sal),

(select max(sal) from emp)

) as rewind

from emp e order by 2;

oracle:

select ename,sal,

nvl(lead(sal)over(order by sal),min(sal)over()) forward,

nvl(lag(sal)over(order by sal),max(sal)over()) rewind

from emp order by 2;

Note：有用，但是目前没遇到场景

 

9.给结果分等级

db2/oracle/sqlserver:

select dense_rank()over(order by sal) rnk,sal from emp;

mysql/postgresql:

select (select count(distinct b.sal) from emp b where b.sal<=a.sal) as rnk,

a.sal

from emp a;

Note：和根据列值填充等级字段的列值区别不大

 

10.抑制重复

db2/oracle/sqlserver/mysql/postgresql:

select distinct job from emp;

db2/oracle/sqlserver:

select job from (

select job,row_number()over(partition by job order by job) rn

from emp

) where rn=1;

Note：超级常用