我和sql有个约会 - sql总结（三）

元数据查询

列举模式中的表

DB2

查询SYSCAT.TABLES。

select tabname 
from syscat.tables 
where tabschema = 'db_example' 

Oracle
查询SYS.ALL_TABLES。

select table_name 
from all_tables 
where owner = 'db_example' 

PostgreSQL、MySQL 和 SQL Server
查询INFORMATION_SCHEMA.TABLES。

select table_name 
from information_schema.tables 
where table_schema = 'db_example'

列举字段

DB2

查询SYSCAT.COLUMNS。

select colname, typename, colno 
from syscat.columns 
where tabname = 'EMP' 
and tabschema = 'db_example' 

Oracle
查询ALL_TAB_COLUMNS。

select column_name, data_type, column_id 
from all_tab_columns 
where owner = 'db_example' 
and table_name = 'EMP' 

PostgreSQL、MySQL 和 SQL Server
查询INFORMATION_SCHEMA.COLUMNS。

select column_name, data_type, ordinal_position 
from information_schema.columns 
where table_schema = 'db_example' 
and table_name = 'EMP'

列举索引列

DB2

查询SYSCAT.INDEXES。

select a.tabname, b.indname, b.colname, b.colseq 
from syscat.indexes a, 
syscat.indexcoluse b 
where a.tabname = 'EMP' 
and a.tabschema = 'SMEAGOL' 
and a.indschema = b.indschema 
and a.indname = b.indname 

Oracle
查询SYS.ALL_IND_COLUMNS。

select table_name, index_name, column_name, column_position 
from sys.all_ind_columns 
where table_name = 'EMP' 
and table_owner = 'SMEAGOL' 

PostgreSQL
查询PG_CATALOG.PG_INDEXES和INFORMATION_SCHEMA.COLUMNS。

select a.tablename,a.indexname,b.column_name 
from pg_catalog.pg_indexes a, 
information_schema.columns b 
where a.schemaname = 'SMEAGOL' 
and a.tablename = b.table_name 

MySQL
使用SHOW INDEX命令。

show index from emp 

SQL Server
查询SYS.TABLES、SYS.INDEXES、SYS.INDEX_COLUMNS和SYS.COLUMNS。

select a.name table_name, 
b.name index_name, 
d.name column_name, 
c.index_column_id 
from sys.tables a, 
sys.indexes b, 
sys.index_columns c, 
sys.columns d.  
where a.object_id = b.object_id 
and b.object_id = c.object_id 
and b.index_id = c.index_id 
and c.object_id = d.object_id 
and c.column_id = d.column_id 
and a.name = 'EMP'

列举约束

DB2

查询SYSCAT.TABCONST和SYSCAT.COLUMNS。

select a.tabname, a.constname, b.colname, a.type 
from syscat.tabconst a, 
syscat.columns b 
where a.tabname = 'EMP' 
and a.tabschema = 'SMEAGOL' 
and a.tabname = b.tabname 
and a.tabschema = b.tabschema 

Oracle
查询SYS.ALL_CONSTRAINTS和SYS.ALL_CONS_COLUMNS。

select a.table_name, 
a.constraint_name, 
b.column_name, 
a.constraint_type 
from all_constraints a, 
all_cons_columns b 
where a.table_name = 'EMP' 
and a.owner = 'SMEAGOL' 
and a.table_name = b.table_name 
and a.owner = b.owner 
and a.constraint_name = b.constraint_name 

PostgreSQL、MySQL 和 SQL Server
查询INFORMATION_SCHEMA.TABLE_CONSTRAINTS和INFORMATION_SCHEMA.KEY_COLUMN_USAGE。

select a.table_name, 
a.constraint_name, 
b.column_name, 
a.constraint_type 
from information_schema.table_constraints a, 
information_schema.key_column_usage b 
where a.table_name = 'EMP' 
and a.table_schem = 'SMEAGOL' 
and a.table_name = b.table_name 
and a.table_schema = b.table_schema 
and a.constraint_name = b.constraint_name

列举非索引外键

DB2

查询SYSCAT.TABCONST、SYSCAT.KEYCOLUSE、SYSCAT.INDEXES和SYSCAT.INDEXCOLUSE。

select fkeys.tabname, 
fkeys.constname, 
fkeys.colname, 
ind_cols.indname 
from ( 
select a.tabschema, a.tabname, a.constname, b.colname 
from syscat.tabconst a, 
syscat.keycoluse b 
where a.tabname = 'EMP' 
and a.tabschema = 'SMEAGOL' 
and a.type = 'F' 
and a.tabname = b.tabname 
and a.tabschema = b.tabschema 
) fkeys 
left join 
( 
select a.tabschema, 
a.tabname, 
a.indname, 
b.colname 
from syscat.indexes a, 
syscat.indexcoluse b 
where a.indschema = b.indschema 
and a.indname = b.indname 
) ind_cols 
on ( fkeys.tabschema = ind_cols.tabschema 
and fkeys.tabname = ind_cols.tabname 
and fkeys.colname = ind_cols.colname ) 
where ind_cols.indname is null 

Oracle
查询SYS.ALL_CONS_COLUMNS、SYS.ALL_CONSTRAINTS和SYS.ALL_IND_COLUMNS。

select a.table_name, 
a.constraint_name, 
a.column_name, 
c.index_name 
from all_cons_columns a, 
all_constraints b, 
all_ind_columns c 
where a.table_name = 'EMP' 
and a.owner = 'SMEAGOL' 
and b.constraint_type = 'R' 
and a.owner = b.owner 
and a.table_name = b.table_name 
and a.constraint_name = b.constraint_name 
and a.owner = c.table_owner (+) 
and a.table_name = c.table_name (+) 
and a.column_name = c.column_name (+) 
and c.index_name is null 

PostgreSQL
查询INFORMATION_SCHEMA.KEY_COLUMN_USAGE、INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS、

INFORMATION_SCHEMA.COLUMNS和PG_CATALOG.PG_INDEXES。

select fkeys.table_name, 
fkeys.constraint_name, 
fkeys.column_name, 
ind_cols.indexname 
from ( 
select a.constraint_schema, 
a.table_name, 
a.constraint_name, 
a.column_name 
from information_schema.key_column_usage a, 
information_schema.referential_constraints b 
where a.constraint_name = b.constraint_name 
and a.constraint_schema = b.constraint_schema 
and a.constraint_schema = 'SMEAGOL' 
and a.table_name = 'EMP' 
) fkeys 
left join 
( 
select a.schemaname, a.tablename, a.indexname, b.column_name 
from pg_catalog.pg_indexes a, 
information_schema.columns b 
where a.tablename = b.table_name 
and a.schemaname = b.table_schema 
) ind_cols 
on ( fkeys.constraint_schema = ind_cols.schemaname 
and fkeys.table_name = ind_cols.tablename 
and fkeys.column_name = ind_cols.column_name ) 
where ind_cols.indexname is null 

MySQL
使用SHOW INDEX命令获取诸如索引名称、索引列和列位置序号之类的索引信息。除此之外，我们还可以通过查询INFORMATION_SCHEMA.KEY_COLUMN_USAGE列出表的外键。对于MySQL 5 而言，外键虽然默认是加入索引的，但事实上却可以被删掉。要确认外键列的索引是否已经被删除，可以针对特定的表执行SHOW INDEX命令，并比较其输出结果与

INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME的异同。如果KEY_COLUMN_USAGE里有 对应的COLUMN_NAME，但是SHOW INDEX输出的结果里却没有，那么就说明该列没有索引。

SQL Server

查询SYS.TABLES、SYS.FOREIGN_KEYS、SYS.COLUMNS、SYS.INDEXES和SYS.INDEX_COLUMNS。

select fkeys.table_name, 
fkeys.constraint_name, 
fkeys.column_name, 
ind_cols.index_name 
from ( 
select a.object_id, 
d.column_id, 
a.name table_name, 
b.name constraint_name, 
d.name column_name 
from sys.tables a 
join 
sys.foreign_keys b 
on ( a.name = 'EMP' 
and a.object_id = b.parent_object_id 
) 
join 
sys.foreign_key_columns c 
on ( b.object_id = c.constraint_object_id ) 
join 
sys.columns d 
on ( c.constraint_column_id = d.column_id 
and a.object_id = d.object_id 
) 
) fkeys 
left join 
( 
select a.name index_name, 
b.object_id, 
b.column_id 
from sys.indexes a, 
sys.index_columns b 
where a.index_id = b.index_id 
) ind_cols 
on ( fkeys.object_id = ind_cols.object_id 
and fkeys.column_id = ind_cols.column_id ) 
where ind_cols.index_name is null

描述Oracle数据字典视图

查询DICTIONARY视图，并列出数据字典视图及其目的。

select table_name, comments 
 from dictionary 
 order by table_name;

查询 DICT_COLUMNS，并找出某个数据字典视图的各列含义。

select column_name, comments 
 from dict_columns 
 where table_name = 'ALL_TAB_COLUMNS';

字符串处理

遍历字符串

select substr(e.ename,iter.pos,1) as C 
from (select ename from emp where ename = 'KING') e, 
(select id as pos from t10) it
where iter.pos <= length(e.ename)

嵌入引号

select 'g''day mate' qmarks from t1 union all 
select 'beavers'' teeth' from t1 union all 
select '''' from t1

统计字符出现的次数

select (length('10,CLARK,MANAGER')- 
length(replace('10,CLARK,MANAGER',',','')))/length(',') 
as cnt  
from t1 

删除不想要的字符

DB2

使用内置函数TRANSLATE和REPLACE删除不想要的字符和字符串。

select ename, 
replace(translate(ename,'aaaaa','AEIOU'),'a','') stripped1, 
sal, 
replace(cast(sal as char(4)),'0','') stripped2 
from emp 

MySQL 和 SQL Server
MySQL 和 SQL Server 没有提供TRANSLATE函数，因而需要多次调用 REPLACE 函数。

select ename, 
replace( 
replace( 
replace( 
replace( 
replace(ename,'A',''),'E',''),'I',''),'O',''),'U','') 
as stripped1, 
sal, 
replace(sal,0,'') stripped2 
from emp 

Oracle 和 PostgreSQL
使用内置函数TRANSLATE和REPLACE删除不想要的字符和字符串。

select ename, 
replace(translate(ename,'AEIOU','aaaaa'),'a') 
as stripped1, 
sal, 
replace(sal,0,'') as stripped2 
from emp

按照子字符串排序

DB2、Oracle、MySQL 和 PostgreSQL

使用内置函数LENGTH和SUBSTR，根据字符串的特定部分排序。

select ename 
from emp 
order by substr(ename,length(ename)-1,2) 

SQL Server
使用SUBSTRING函数和LEN函数，根据子字符串的特定部分排序。

select ename 
from emp 
order by substring(ename,len(ename)-1,2)

创建分隔列表

DB2

使用WITH子句递归地查询创建分隔列表。

 1 with x (deptno, cnt, list, empno, len) 
 2 as ( 
 3 select deptno, count(*) over (partition by deptno), 
 4 cast(ename as varchar(100)), empno, 1 
 5 from emp 
 6 union all 
 7 select x.deptno, x.cnt, x.list ||','|| e.ename, e.empno, x.len+1 
 8 from emp e, x 
 9 where e.deptno = x.deptno 
10 and e.empno > x. empno 
11 ) 
12 select deptno,list 
13 from x 
14 where len = cnt 

MySQL
使用内置函数GROUP_CONCAT创建分隔列表。

select deptno, 
group_concat(ename order by empno separator, ',') as emps 
from emp  
group by deptno 

Oracle
使用内置函数SYS_CONNECT_BY_PATH创建分隔列表。

select deptno, 
ltrim(sys_connect_by_path(ename,','),',') emps 
from (  
select deptno, 
ename,  
row_number() over  
(partition by deptno order by empno) rn, 
count(*) over 
(partition by deptno) cnt 
from emp 
)  
where level = cnt 
start with rn = 1 
connect by prior deptno = deptno and prior rn = rn-1 

PostgreSQL
PostgreSQL 没有提供用于创建分隔列表的标准内置函数，因而需要提前知道列表里有多少

个元素。知道了最大的列表长度，就能确定在使用置换和字符串拼接等传统手段创建列表

时需要附加多少个值。

select deptno, 
rtrim(  
max(case when pos=1 then emps else '' end)|| 
max(case when pos=2 then emps else '' end)|| 
max(case when pos=3 then emps else '' end)|| 
max(case when pos=4 then emps else '' end)|| 
max(case when pos=5 then emps else '' end)|| 
max(case when pos=6 then emps else '' end),',' 
) as emps  
from (  
select a.deptno, 
a.ename||',' as emps, 
d.cnt, 
(select count(*) from emp b 
where a.deptno=b.deptno and b.empno <= a.empno) as pos 
from emp a, 
(select deptno, count(ename) as cnt 
from emp 
group by deptno) d 
where d.deptno=a.deptno 
) x 
group by deptno 
order by 1 

SQL Server
使用WITH子句递归地查询创建分隔列表。

with x (deptno, cnt, list, empno, len) 
as ( 
select deptno, count(*) over (partition by deptno), 
cast(ename as varchar(100)), 
empno,  
1  
from emp 
union all  
select x.deptno, x.cnt,  
cast(x.list + ',' + e.ename as varchar(100)), 
e.empno, x.len+1 
from emp e, x 
where e.deptno = x.deptno 
and e.empno > x. empno 
) 
select deptno,list 
from x 
where len = cnt 
order by 1

解析IP地址

DB2

使用WITH子句递归地查询针对 IP 地址的循环操作，同时使用SUBSTR函数可以很容易解

析 IP 地址。在 IP 地址开头加上一个英文句号，这样每一组数字的开头位置都有英文句号，

因而我们能以相同的方式处理所有的四组数字。

with x (pos,ip) as ( 
values (1,'.92.111.0.222') 
union all 
select pos+1,ip from x where pos+1 <= 20  
) 
select max(case when rn=1 then e end) a, 
max(case when rn=2 then e end) b, 
max(case when rn=3 then e end) c, 
max(case when rn=4 then e end) d 
from ( 
select pos,c,d, 
case when posstr(d,'.') > 0 then substr(d,1,posstr(d,'.')-1) 
else d 
end as e, 
row_number() over(order by pos desc) rn 
from ( 
select pos, ip,right(ip,pos) as c, substr(right(ip,pos),2) as d 
from x 
where pos <= length(ip) 
and substr(right(ip,pos),1,1) = '.' 
) x 
) y 

MySQL
使用函数SUBSTR_INDEX很容易解析 IP 地址。

select substring_index(substring_index(y.ip,'.',1),'.',-1) a, 
substring_index(substring_index(y.ip,'.',2),'.',-1) b, 
substring_index(substring_index(y.ip,'.',3),'.',-1) c, 
substring_index(substring_index(y.ip,'.',4),'.',-1) d 
from (select '92.111.0.2' as ip from t1) y 

Oracle
使用内置函数SUBSTR和INSTR解析和遍历 IP 地址。

select ip,  
substr(ip, 1, instr(ip,'.')-1 ) a, 
substr(ip, instr(ip,'.')+1, 
instr(ip,'.',1,2)-instr(ip,'.')-1 ) b, 
substr(ip, instr(ip,'.',1,2)+1, 
instr(ip,'.',1,3)-instr(ip,'.',1,2)-1 ) c, 
substr(ip, instr(ip,'.',1,3)+1 ) d 
from (select '92.111.0.2' as ip from t1) 

PostgreSQL
使用内置函数SPLIT_PART解析 IP 地址。

select split_part(y.ip,'.',1) as a, 
split_part(y.ip,'.',2) as b, 
split_part(y.ip,'.',3) as c, 
split_part(y.ip,'.',4) as d 
from (select cast('92.111.0.2' as text) as ip from t1) as y 

SQL Server
使用WITH子句递归地查询针对 IP 地址的循环操作，同时使用SUBSTR函数可以很容易解析

IP 地址。在 IP 地址开头加上英文句号，这样每一组数字的开头位置都有英文句号，因而

我们能以相同的方式处理全部 4 组数字。

with x (pos,ip) as ( 
select 1 as pos,'.92.111.0.222' as ip from t1 
union all 
select pos+1,ip from x where pos+1 <= 20 
) 
select max(case when rn=1 then e end) a, 
max(case when rn=2 then e end) b, 
max(case when rn=3 then e end) c, 
max(case when rn=4 then e end) d 
from ( 
select pos,c,d, 
case when charindex('.',d) > 0 
then substring(d,1,charindex('.',d)-1) 
else d 
end as e, 
row_number() over(order by pos desc) rn  
from ( 
select pos, ip,right(ip,pos) as c, 
substring(right(ip,pos),2,len(ip)) as d 
from x 
where pos <= len(ip) 
and substring(right(ip,pos),1,1) = '.' 
) x 
) y

数值处理

计算平均值

select deptno, avg(sal) as avg_sal 
from emp 
group by deptno

查找最小值和最大值

select deptno, min(sal) as min_sal, max(sal) as max_sal 
from emp 
group by deptno

求和

select deptno, sum(sal) as total_for_dept 
from emp 
group by deptno

计算行数

select count(*) from emp

计算非Null值的个数

select count(comm) from emp

当执行COUNT(*)操作时，实际上是在统计行数（而不会去管实际的值是什么，这就是为什

么Null值和非Null值都会被计入总数）。但是，如果针对某一列执行COUNT操作，我们却

是在计算该列非Null值的个数。

累计求和

DB2 和 Oracle

使用SUM函数的窗口函数版本进行累计求和。

select ename, sal, 
sum(sal) over (order by sal,empno) as running_total 
from emp 
order by 2 

MySQL、PostgreSQL 和 SQL Server

select e.ename, e.sal, 
(select sum(d.sal) from emp d 
where d.empno <= e.empno) as running_total 
from emp e  
order by 3

计算累计乘积

DB2 和 Oracle

使用窗口函数SUM OVER，并利用对数来模拟乘法。

select empno,ename,sal, 
exp(sum(ln(sal))over(order by sal,empno)) as running_prod 
from emp 
where deptno = 10

MySQL、PostgreSQL 和 SQL Server
我们仍然需要使用对数求和，但是这些数据库不支持窗口函数，因而改用标量子查询。

select e.empno,e.ename,e.sal, 
(select exp(sum(ln(d.sal))) 
from emp d 
where d.empno <= e.empno 
and e.deptno=d.deptno) as running_prod 
from emp e 
where e.deptno=10

计算累计差

DB2 和 Oracle

使用窗口函数SUM OVER计算累计差。

select ename,sal, 
sum(case when rn = 1 then sal else -sal end) 
over(order by sal,empno) as running_diff 
from (  
select empno,ename,sal, 
row_number() over(order by sal,empno) as rn  
from emp 
where deptno = 10 
) x  

MySQL、PostgreSQL 和 SQL Server
使用标量子查询计算累计差。

select a.empno, a.ename, a.sal, 
(select case when a.empno = min(b.empno) then sum(b.sal) 
else sum(-b.sal) 
end  
from emp b  
where b.empno <= a.empno 
and b.deptno = a.deptno ) as rnk 
from emp a  
where a.deptno = 10

计算众数

DB2 和 SQL Server

使用窗口函数DENSE_RANK对工资值出现的次数进行排序，以帮助我们找到众数。

select sal 
from ( 
select sal, 
dense_rank() over(order by cnt desc) as rnk  
from (  
select sal, count(*) as cnt 
from emp 
where deptno = 20 
group by sal 
) x 
) y 
where rnk = 1

Oracle

select max(sal) 
keep(dense_rank first order by cnt desc) sal 
from ( 
select sal, count(*) cnt 
from emp 
where deptno=20 
group by sal 
)  

MySQL 和 PostgreSQL
使用子查询查找众数。

select sal 
from emp 
where deptno = 20 
group by sal 
having count(*) >= all ( select count(*) 
from emp 
where deptno = 20 
group by sal )

计算百分比

MySQL 和 PostgreSQL

DEPTNO等于10的工资总额除以全体工资总额。

select (sum( 
case when deptno = 10 then sal end)/sum(sal) 
)*100 as pct 
from emp  

DB2、Oracle 和 SQL Server
使用内嵌视图和窗口函数SUM OVER来得到全体工资总额以及DEPTNO等于 10 的工资总额。

然后，在外层查询中执行除法和乘法。

select distinct (d10/total)*100 as pct 
from ( 
select deptno, 
sum(sal)over() total, 
sum(sal)over(partition by deptno) d10 
from emp 
) x 
where deptno=10

聚合Null列

select avg(coalesce(comm,0)) as avg_comm 
from emp 
where deptno=30

计算平均值时去掉最大值和最小值

MySQL 和 PostgreSQL

使用子查询去掉最大值和最小值。

select avg(sal) 
from emp 
where sal not in ( 
(select min(sal) from emp), 
(select max(sal) from emp) 
) 

DB2、Oracle 和 SQL Server
使用内嵌视图以及窗口函数MAX OVER和MIN OVER来生成结果集，可以很容易去掉最大值和

最小值。

select avg(sal) 
from ( 
select sal, min(sal) over()min_sal, max(sal)over() max_sal 
from emp  
) x 
where sal not in (min_sal,max_sal)

日期运算

年月日加减法

DB2

支持针对日期的加法和减法运算，但不论加上一个数还是减去一个数，后面都要指定对应

的时间单位。

select hiredate -5 day as hd_minus_5D, 
hiredate +5 day as hd_plus_5D, 
hiredate -5 month as hd_minus_5M, 
hiredate +5 month as hd_plus_5M, 
hiredate -5 year as hd_minus_5Y, 
hiredate +5 year as hd_plus_5Y 
from emp 
where deptno = 10 

Oracle
若要加上或减去若干天，使用加法或减法即可。若要加减若干个月或年，则需要使用ADD_

MONTHS函数。

select hiredate-5 as hd_minus_5D, 
hiredate+5 as hd_plus_5D, 
add_months(hiredate,-5) as hd_minus_5M, 
add_months(hiredate,5) as hd_plus_5M, 
add_months(hiredate,-5*12) as hd_minus_5Y, 
add_months(hiredate,5*12) as hd_plus_5Y 
from emp 
where deptno = 10 

PostgreSQL
使用加减法，并使用INTERVAL关键字指定要加上或者减去的时间单位。在指定INTERVAL

值的时候，必须使用英文单引号。

1 select hiredate - interval '5 day' as hd_minus_5D, 
2 hiredate + interval '5 day' as hd_plus_5D, 
3 hiredate - interval '5 month' as hd_minus_5M, 
4 hiredate + interval '5 month' as hd_plus_5M, 
5 hiredate - interval '5 year' as hd_minus_5Y, 
6 hiredate + interval '5 year' as hd_plus_5Y 
7 from emp 
8 where deptno=10 

MySQL
使用加减法，并使用INTERVAL关键字指定要加上或者减去的时间单位。不同于上述

PostgreSQL 的解决方案，指定INTERVAL值不必使用英文单引号。

select hiredate - interval 5 day as hd_minus_5D, 
hiredate + interval 5 day as hd_plus_5D, 
hiredate - interval 5 month as hd_minus_5M, 
hiredate + interval 5 month as hd_plus_5M, 
hiredate - interval 5 year as hd_minus_5Y, 
hiredate + interval 5 year as hd_plus_5Y 
from emp 
where deptno=10 

除此之外，还可以使用DATE_ADD函数，如下所示。

select date_add(hiredate,interval -5 day) as hd_minus_5D, date_add(hiredate,interval 5 day) as hd_plus_5D, 
date_add(hiredate,interval -5 month) as hd_minus_5M, 
date_add(hiredate,interval 5 month) as hd_plus_5M, 
date_add(hiredate,interval -5 year) as hd_minus_5Y, date_add(hiredate,interval 5 year) as hd_plus_5DY 
from emp 
where deptno=10 

SQL Server
使用DATEADD函数在给定日期值的基础上加上或者减去若个时间单位。

select dateadd(day,-5,hiredate) as hd_minus_5D, dateadd(day,5,hiredate) as hd_plus_5D, 
dateadd(month,-5,hiredate) as hd_minus_5M, 
dateadd(month,5,hiredate) as hd_plus_5M, 
dateadd(year,-5,hiredate) as hd_minus_5Y, 
dateadd(year,5,hiredate) as hd_plus_5Y 
from emp 
where deptno = 10

计算两个日期之间的天数

使用内嵌视图找出 WARD 和 ALLEN 的HIREDATE。然后，使用DAYS函数从一个HIREDATE

里减去另一个。

select days(ward_hd) - days(allen_hd) 
from ( 
select hiredate as ward_hd 
from emp 
where ename = 'WARD' 
) x, 
( 
select hiredate as allen_hd  
from emp 
where ename = 'ALLEN' 
) y 

Oracle 和 PostgreSQL
使用内嵌视图找出 WARD 和 ALLEN 的HIREDATE，然后相减。

select ward_hd - allen_hd 
from ( 
select hiredate as ward_hd 
from emp 
where ename = 'WARD' 
) x, 
( 
select hiredate as allen_hd  
from emp 
where ename = 'ALLEN' 
) y 

MySQL 和 SQL Server
使用DATEDIFF函数找出两个日期之间相差多少天。MySQL 的DATEDIFF函数只需要两个参

数（两个将要相减的日期值），并且相对较早的日期值应该作为第一个参数以避免出现负

数（SQL Server 正好相反）。SQL Server 的DATEDIFF函数可以返回指定的时间单位（本例

中我们希望以天为单位）。下面给出了 SQL Server 的解决方案。

select datediff(day,allen_hd,ward_hd) 
from ( 
select hiredate as ward_hd 
from emp 
where ename = 'WARD' 
) x, 
( 
select hiredate as allen_hd  
from emp 
where ename = 'ALLEN' 
) y 

对于 MySQL 而言，只需去掉DATEDIFF函数的第一个参数，并翻转ALLEN_HD和WARD_HD的
顺序即可。

从给定日期值里提取年月日时分秒

DB2 实现了一组内置函数帮助我们方便地提取出一个日期值的每一个组成部分。这些函数

分别被命名为HOUR、MINUTE、SECOND、DAY、MONTH和YEAR，直观地表明它们要返回的时间

单位。如果我们想要“天”，那就调用DAY函数；如果想要“小时”，那就调用HOUR函数，

等等。示例如下。

select hour( current_timestamp ) hr, 
minute( current_timestamp ) min, 
second( current_timestamp ) sec, 
day( current_timestamp ) dy, 
month( current_timestamp ) mth, 
year( current_timestamp ) yr 
from t1 

Oracle

使用TO_CHAR和TO_NUMBER函数从一个日期值里提取各种时间单位。

select to_number(to_char(sysdate,'hh24')) hour, 
to_number(to_char(sysdate,'mi')) min, 
to_number(to_char(sysdate,'ss')) sec, 
to_number(to_char(sysdate,'dd')) day, 
to_number(to_char(sysdate,'mm')) mth, 
to_number(to_char(sysdate,'yyyy')) year 
from dual 

PostgreSQL
使用TO_CHAR和TO_NUMBER函数从一个日期值里提取各种时间单位。

select to_number(to_char(current_timestamp,'hh24'),'99') as hr, 
to_number(to_char(current_timestamp,'mi'),'99') as min, 
to_number(to_char(current_timestamp,'ss'),'99') as sec, 
to_number(to_char(current_timestamp,'dd'),'99') as day, 
to_number(to_char(current_timestamp,'mm'),'99') as mth, 
to_number(to_char(current_timestamp,'yyyy'),'9999') as yr 
from t1 

MySQL

使用DATE_FORMAT函数从一个日期值里提取各种时间单位。

select date_format(current_timestamp,'%k') hr, 
date_format(current_timestamp,'%i') min, 
date_format(current_timestamp,'%s') sec, 
date_format(current_timestamp,'%d') dy, 
date_format(current_timestamp,'%m') mon, 
date_format(current_timestamp,'%Y') yr 
from t1 

SQL Server
使用DATEPART函数从一个日期值里提取各种时间单位。

select datepart( hour, getdate()) hr,  
datepart( minute,getdate()) min,  
datepart( second,getdate()) sec,  
datepart( day, getdate()) dy,  
datepart( month, getdate()) mon,  
datepart( year, getdate()) yr  
from t1 

高级查询

结果集分页

DB2、Oracle 和 SQL Server

使用窗口函数ROW_NUMBER OVER实现排序，并且在WHERE子句中指定我们希望返回的行。例

如，返回第 1 到第 5 行。

select sal 
 from ( 
select row_number() over (order by sal) as rn,  
sal  
 from emp  
 ) x  
 where rn between 1 and 5

MySQL 和 PostgreSQL

对于这两种数据库而言，滚动结果集非常容易，因为它们支持LIMIT和OFFSET子句。使用

LIMIT子句指定要返回的行数，使用OFFSET子句指定要跳过的行数。例如，按照工资排序

返回最前面的 5 行。

select sal 
 from emp 
 order by sal limit 5 offset 0

跳过n行记录

DB2、Oracle 和 SQL Server

使用窗口函数ROW_NUMBER OVER为每一行分配一个序号，这样就可以借助模函数跳过我们不

想要的行了。DB2 和 Oracle 的模函数是MOD。SQL Server 则使用%操作符。下面的例子使

用MOD跳过编号为偶数的行。

select ename 
from ( 
select row_number() over (order by ename) rn, 
ename 
from emp 
) x 
where mod(rn,2) = 1 

MySQL 和 PostgreSQL
这两种数据库不提供支持排序或为每一行数据编排序号的内置函数，因而需要使用标量子

查询来模拟实现类似功能（本例中根据员工名字排序），然后使用模函数跳过不需要的行。

select x.ename 
from ( 
select a.ename, 
(select count(*) 
from emp b 
where b.ename <= a.ename) as rn 
from emp a 
)x 
where mod(x.rn,2) = 1

删除重复项

使用DISTINCT关键字从结果集里删除重复项。

select distinct job from emp 

另外，也可以通过GROUP BY达到同样目的。

select job from emp group by job