一、连接列值
db2/oracle/postgresql
select name(字段)||' Works AS a '（文字）||job as msg from emp where deptno=10;
mysql
select concat(name,' works as a',job) as msg from emp where deptno=10;
sql server
select name+' works as a '+job as msg from emp where deptno=10;
二、使用条件逻辑 select name,salary,
case when salary<=2000 then 'low' case when salary>=4000 then 'over' else 'ok' end as status from emp
三、限制返回的行数 db2
select * from emp fetch first 5 rows only
mysql/postgresql
select * from emp limit 5
oracle
select * from emp rownum<=5
sql server
select top 5 * from emp
四、随机返回记录
db2
select name,job from emp order by rahnd() fetch first 5 rows only
mysql
select name,job from emp order by rand() limit 5
postgresql
select * from emp order by random() limit 5
oracle
select * from (select name,job from emp order by dbms_random.value()) where rownum<=5
sql server
select top 5 name,job from emp order by newid()
五、将空值转换成实际值
select coalesce(comm,0) from emp
不为空则返回comm值，空则返回0，comm类型与0类型必须一致
六、按子串排序(取消后面2位) db2/mysql/oracle/postgresql
select name,job from emp order by substr(job,length(job)-2)
sql server
select name,job from emp order by substring(job,len(job)-2,2)
七、对字母数字混合的数据排序 oracle/postgresql
select data from emp v order (data,replace(translate(data,'0123456789','##########'),'#',''),'')
select data from emp order by replace(translate(data,'0123456789','##########'),'#','') db2
select * from (select ename||' '||cast(deptno as char(2)) as data from emp ) v order by replace (data,replace(translate(data,'##########','0123456789'),'#',''),'')
select * from (select name||' '||cast(deptno as char(2)) as data from emp v order by replace(translate(data,'##########','0123456789'),'#','')
mysql/sqlserver
当前不支持translate函数，无解决方案
八、处理排序空值
db2/mysql/postgresql/sqlserver
select name,sal,comm from (select name,sal,comm case when comm is null then 0 else 1 end as is_null from emp) x order by is_null desc,comm oracle
select name,sal,comm from emp order by comm nulls last//all nulls last select name,sal,comm from emp order by comm nulls first//all nulls first
by
replace
九、根据数据项的键排序
select name,sal,job,comm from emp order by case when job='salesman' then comm else sal end
十、记录集的叠加
//使用union子句相当于对使用union all子句的结果使用distinct
select ename as ename_and_dname,deptno from emp where deptno=10 union all select '----------',null from t1 union all select dname,deptno from dept
十一、从一个表红查找另一个表没有的值 db2/postgresql
select deptno from dept except select deptno from emp
oracle
select deptno from dept minus select deptno from emp
mysql/sqlserver
select deptno from dept where deptno not in (select deptno from emp)
十二、在一个表中查找与其他表不匹配的记录 db2/mysql/postgresql/sqlserver
select d.* from dept d left outer join emp e on(d.deptno=e.deptno) where e.deptno is null
oracle
select d.* from dept d,emp e where d.deptno=e.deptno (+) and e.deptno is null
十三、向查询中增加联接而不影响其他联接 db2/mysql/postgresql/sqlserver
select e.ename,d.loc,eb.received from emp e join dept d on (e.deptno=d.deptno) left join emp_bonus eb on (e.empno=eb.empno) order by 2
oracle
select e.ename,d.loc,eb.received from emp e,dept d,emp_bonus eb where e.deptno=d.deptno and e.empno=eb.empno (+) order by 2
select e.ename,d.loc,(select eb.received from emp_bonus eb where eb.empno=e.empno) as received from emp e,dept d where e.deptno=d.deptno order by 2
十四、检测两个表中是否有相同的数据
解决原理：
1、首先，查找处表emp中存在而视图v中没有的行
2、然后合并（union all）在视图v中存在，而在表emp中没有的行
十五、识别和消除笛卡尔积
在from子句对表进行联接来返回正确的结果集：
select e.ename,d.loc from emp e,dept d where e.deptno=10 and d.deptno =e.deptno
十六、聚集与联接
mysql/postgresql
select deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonus from (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type=1 then .1 when eb.type=2 then .2 else .3 end as bonus from emp e,emp_bonus eb where e.empno=eb.empno and e.deptno=1) x group by deptno
db2/oracle/sqlserver
select distinct deptno,total_sal,total_bonus from (select e.empno,e.ename,sum(distinct e.sal) over (partition by e.deptno) as total_sal,e.deptno,sum(e.sal*case when eb.type=1 then .1 when eb.type=2 then .2 else .3 end) over (partition by deptno) as total_bonus from emp e,emp_bonus eb where e.empno=eb.empno and e.deptno=10) x
十七、聚集与外联接
db2/mysql/postgresql/sqlserver
select deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonus from (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type is null then 0 when eb.type=1 then .1 when eb.type=2 then .2 else .3 end as bonus from emp e left outer join emp_bonus eb on(e.empno=eb.empno) where e.deptno=10) group by deptno
select distinct deptno,total_sal,total_bonus from (select e.empno,e.ename,sum(distinct e.sal) over (partition by e.deptno) as total_sal,e.deptno,sum(e.sal*case when eb.type is null then 0 when eb.type=1 then .1 when eb.type =2 then .2 else .3 end) over (partition by deptno) as total_bonus from emp e left outer join emp_bonus eb on (e.empno=eb.empno) where e.deptno=10) x
oracle
select deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonus from (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type is null then 0 when eb.type=1 then .1 when eb.type=2 then .2 else .3 end as bonus from emp e,emp_bonus eb where e.empno=eb.empno (+) and e.deptno=10) group by deptno
十八、从多个表中返回丢失的数据 db2/mysql/postgresql/sqlserver
select d.deptno,d.dname,e.ename from dept d full outer join emp e on (d.deptno=e.deptno)
select d.deptno,d.dname,e.ename from dept d right outer join emp e on(d.deptno=e.deptno) union select d.deptno,d.dname,e.ename from dept d left outer join emp e on (d.deptno=e.deptno)
oracle
select d.deptno,d.dname,e.ename from dept d,emp e where d.deptno=e.deptno (+) union select d.deptno,d.dname,e.ename from dept d,emp e where d.deptno(+)=e.deptno
十九、在运算和比较时使用null值
select ename,comm from emp where coalesce(comm,0)<(select comm from emp where ename='WARD')
二十、从一个表向另外的表中复制行
insert into dept_east (deptno,dname,loc) select deptno,dname,loc from dept where loc in('NEW YORK','BOSTON')
二十一、复制表定义 db2
create table dept_2 like dept
oracle/mysql/postgresql
create table dept_2 as select * from dept where 1=0
sqlserver
select * into dept_2 from dept where 1=0
二十二、一次向多个表中插入记录 oracle
insert all when loc in('NEW YORK','BOSTON') then into dept_east (deptno,dname,loc) values(deptno,dname,loc)
when loc='CHICAGO' then into dept_mid (deptno,dname,loc) values(deptno,dname,loc) else into dept_west (deptno,dname,loc) values(deptno,dname,loc) select deptno,dname,loc from dept
db2
insert into (select * from dept_west union all select * from dept_east union all select * from dept_mid) select * from dept
mysql/postgresql/sqlserver 不支持多表插入操作
二十三、阻止对某几列插入 在表中创建一个视图，该视图将只显示允许用户进行操作的列，强制所有的插入操作都通过该视图进行
create view new_emps as select empno,ename,job from emp
二十四、用其他表中的值更新 db2/mysql
update emp e set(e.sal,e.comm)=(select ns.sal,ns.sal/2 from new_sal ns where ns.deptno=e.deptno) where exists(select nul from new_sa ns where ns.deptno=e.deptno)