我和sql有个约会 - sql总结(三)
元数据查询列举模式中的表DB2查询SYSCAT.TABLES。select tabnamefrom syscat.tableswhere tabschema = 'db_example'Oracle查询SYS.ALL_TABLES。select table_namefrom all_tableswhere owner = 'db_example'PostgreSQL、MySQL 和 SQL Ser
元数据查询
列举模式中的表
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
更多推荐
所有评论(0)