DB2 常用函数实例总结

--查询某个用户下所有的表名及创建时间
select creator,name,ctime from sysibm.systables where type='T' and creator='DB2ADMIN';
--查询某个用户下某张表中的字段名，类型，长度
SELECT tabschema,tabname,colname,typename,length FROM SYSCAT.COLUMNS WHERE TABNAME='EMP' AND TABSCHEMA='DB2ADMIN';


--查询当前时间
select current date from sysibm.sysdummy1;
--分页查询
select * from emp order by empno limit 0,3;
--左/右边不足5位补0
select lpad('123',5,0) from sysibm.sysdummy1;
select rpad('123',5,0) from sysibm.sysdummy1;
--TRUNC()/TRUNCATE()与ROUND()函数
SELECT TRUNCATE(345.6789,2) FROM sysibm.sysdummy1; --346.6700
SELECT TRUNC(345.6789,2) FROM sysibm.sysdummy1; --346.6700
SELECT TRUNC(345.6789) FROM sysibm.sysdummy1; --345.0000

SELECT ROUND(345.6789,2) FROM sysibm.sysdummy1; --346.6800
SELECT ROUND(345.6789) FROM sysibm.sysdummy1; --346.0000


--查询重复数据
select * from ( select empno,row_number() over(order by empno) rn from emp) e1 where empno in (
	   select empno from ( select empno,row_number() over(order by empno) rn from emp) e2
	   where e1.rn > e2.rn
	   and e1.empno = e2.empno
);

select * from ( select empno,row_number() over(order by empno) rn from emp) e1 where exists (
	   select empno from ( select empno,row_number() over(order by empno) rn from emp) e2 
	   where e1.rn > e2.rn
	   and e1.empno = e2.empno
);

select * from ( select empno,row_number() over(partition by empno order by empno) rn from emp ) where rn>1
--删除重复的数据
delete from ( select empno,row_number() over(partition by empno order by empno) rn from emp ) where rn>1

--列转行 行转列
--stu_score
--cla_no  stu_no  score
--   1   20160001   70
--   2   20160001   80
--   1   20160002   90
--   2   20160002   85
--转
--cla_no stu_score_20160001 stu_score_20160002
--   1            70                 90
--   2            80                 85

SELECT S.cla_no, Q.stu_no, Q.score
FROM (
	 --列转行
	 SELECT cla_no,
	   		max(case when stu_no = 20160001 then score end ) as stu_score_20160001,
	   		max(case when stu_no = 20160002 then score end ) as stu_score_20160002
	 FROM stu_score
	 GROUP BY cla_no
	  ) AS S,
     TABLE (VALUES(20160001, S.stu_score_20160001),
                  (20160002, S.stu_score_20160002))
            AS Q(stu_no, score);

--查询各个领域前多少名(各个部门工资排名最高的两个的职员姓名)
select deptno,ename,sal
from emp
where (
	select count(1)
	from emp e
	where e.deptno = emp.deptno
		and e.empno != emp.empno
		and e.sal > emp.sal
    ) < 2
order by deptno,sal desc;