sql cookbook学习笔记db2.docx

1、sql cookbook学习笔记db21. 对表中记录随机排序：order by rand();2. Coalesce() coalesce(case MIDINIT when then null else MIDINIT end, case substr(LASTNAME,1,1) when A then null else end, )3. 处理字母数字混合的字符串：只想按着字母处理，或只想按着数字处理。Translate(data,目标格式,要替换的字符),raplace(data,要替换的内容,替换的目标)，通过两次替换得到最终想要的字符或数字。-限制，要连续的！4. 空值排序：用一个

2、子查询将null值与其他值用数字表示，在新的里再用这个值参与排序。另外，null first 或，5. 在order by后面可以使用case表达式，从而实现根据不同的标准对数据进行排序。Order of 是指按查询表中的某个进行排序，使用相同的顺序。6. 从一张表中查找另一张表中相同的值，使用in7. 从 一张表中查找另一张表中没有的值，用集合讲，就是求集合A-集合B，方法一：方法二：方法三：方法四：区别：a、 Except不会返回重复行，不用担心NULL。b、 Not in等价于not (谓词1 or 谓词2)，如果其中之一为NULL，会导致结果为NULL，造成没有结果返回。In 和not

3、 in本质上是OR运算，因而计算逻辑OR时处理NULL的方式不同，产生的结果也不同。等价运算。运算过程：在SQL中，true or null 的结果是true,false or null的结果是NULL。另外，not in,在后一个表中是空表时，会返回左边表中的所有值。-where 后面的运算结果是Null,不会返回任何值。等价于false.-如果运算符是and,则结果永远为空，等价于false-根据验证结果猜测。c、 Exists运算只有两个结果，真或没有返回值。如果是前者，not exists为假。否则一律为真。如果有一个相等的，则导致结果为假。否则只要没有相等的，一律为真。8. Dist

4、inct的替代方法。本例是求 并集-交集，当中的部分内容使用了这种方法：9. 在运算或比较中使用NULL值。NULL值有时表示是还没有，这时意味着null是有值的，代表最小。如提成，NULL表示的是还没有提成，是最少的。在查找提成最少的人的时候，要特别处理这个字段为Null的。办法-coalesce(data,0)10. 插入数据：可以在insert插入时使用default，插入的值是定义表时默认的值。11. 一次向多个表中插入记录。关键是能定位某条记录能插入到哪种表中。原理是使用视图。12. 删除重复记录。这里有一个技巧，使用集合函数min来得到集合中不重复的部分13. 计算字符在字符串中出

5、现的次数：注意这里使用了除法，原因是LL占两个字节，计算结果是2乘以出现次数长度，所以要除以重复字符创长度得到的才是次数。14. 将字符和数字数据分离这里用了几个函数，一是lower，字母全部小写。二是repeat，不用一直写26个。15. 提取字符串中的所有数字，但数字出现的位置未知，其他字符是什么也不知道：首先将数字弄没，得到剩下的字符；再把这些字符 转译 成相同字符，并去掉；得到的就是只有数字的了。16. 行变列-将行变为列with t(workdept,phoneno_str,trn,ttol)as(select WORKDEPT,cast(PHONENO as varchar(100

6、0),rn,tol from (select WORKDEPT, PHONENO,row_number() over(partition by workdept) as rn,count(*) over(partition by workdept) as tol from DB2ADMIN.EMPLOYEE)btwhere rn=1 union allselect t.workdept,phoneno_str| |coalesce(phoneno,),trn+1,ttol from t ,(select WORKDEPT, PHONENO,row_number() over(partition

7、 by workdept) as rn from DB2ADMIN.EMPLOYEE)bt1where t.workdept=bt1.workdept and bt1.rn=trn+1 and trn+1=ttol)select * from t where trn=ttol17. 将上述结果返回来变，变回原来的样子：select strip(tstr),substr(tstr,pos+1,locate( ,tstr,pos+1)-pos-1) from ( select |PHONENO_STR| as tstr,case pos when 1 then 1 else pos+1 end a

8、s pos,cast(translate(substr(PHONENO_STR,pos,1),#, ) as varchar(1) as ch from DB2ADMIN.TT , (select row_number() over() as pos from DB2ADMIN.TT,DB2ADMIN.TT,DB2ADMIN.TT,DB2ADMIN.TT,DB2ADMIN.TT) p where poslength(PHONENO_STR) )t where ch=# or pos=1 order by 118. 将每条记录中的字符串中的字符进行排序技巧点：a. 在max函数中使用case表达

9、式。通过这种方式实现了逐个位置安置字符。b. 通过使用对全表排序来的得到一个1、2、3、。的列表，而不用递归。问题是可能不够，就是列的最大数还不够将一条记录拆为单个字符。c. 拆单个字符的方法d. 拆单个字符后，再排列，加行号。e. 问题，case那块，是有限制的，超过6个的就不能用了。19. 计算平均值问题：Null不参与运算，导致结果出错。20. 生成累乘积技巧：A 计算累计和B 用对数、指数互换实现累计乘21. 计算累计差技巧：a. 仍然利用累计和，但是要识别第一行b. 通过对行加行标识实现标识行22. 计算模式：技巧：a.这样做的好处是能找出所有最大的，而不是一个。23. 查找中间值：

10、技巧：a 通过ceil及。得到整除顶及底的结果。b 多增加的几个列，把要用到的中间结果都记录下来。c Where中通过or包含两种情况24. 求总和的百分比。首先求部门和，然后再计算这个和在总和中的百分比。技巧：使用partition by25. 确定两个日期之间的工作日数技巧：a 在sum中使用case表达式，且case表达式中使用in谓词进行判断。b 使用dataname函数c Max函数中使用case表达式，相当于group byd 通过t500列出每一天26. 确定两个日期直接的月数和年数，既要算年，也要算月。用年算月。27. 确定两个日期之间的秒分时数。用天算的，不是很精确28. 计

11、算一年中周内各个日期的次数技巧：a. 获得一年中第一天的方法b. 用下一年的第一天减去一天作为本年年尾29. 计算相邻时间的相差天数，关键是查找到最近的那天的方法30. 通过2月的最后一天判断该年是否是闰年：with t(p) as (select hiredate from emp)select p, case day(p-(dayofyear(p)-1) day)+2 month)-1 day) when 28 then else end from t31. 确定月的第一天和最后一天：使用day函数：32. 像日历一样打印一个月的每一天with t7(num) as (values 1)

12、union all select num+1 from t7 where num7),tt(p) as (values current date),trlt(p1,p2,p3,p4,p5,p6,p7,flag) as (select max(case pos when 1 then day end),max(case pos when 2 then day end),max(case pos when 3 then day end),max(case pos when 4 then day end),max(case pos when 5 then day end),max(case pos

13、when 6 then day end),max(case pos when 7 then day end),1from(select (p-day(p) day +1 day)-dayofweek(p-day(p) day +1 day) day+num day as day,row_number() over() as pos from tt,t7)trowunion allselect p1+7 day,p2+7 day,p3+7 day,p4+7 day,p5+7 day,p6+7 day,p7+7 day,flag+1 from trlt where flag5)select cas

14、e month(p1) when (select month(p) from tt) then char(day(p1) else end ,case month(p2) when (select month(p) from tt) then char(day(p2) else end ,case month(p3) when (select month(p) from tt) then char(day(p3) else end ,case month(p4) when (select month(p) from tt) then char(day(p4) else end ,case mo

15、nth(p5) when (select month(p) from tt) then char(day(p5) else end ,case month(p6) when (select month(p) from tt) then char(day(p6) else end ,case month(p7) when (select month(p) from tt) then char(day(p7) else end from trlt33. 在同一个表中各个行之间进行比较，求符合条件的记录，注意比较次数，通过限制实现只比较一次34. 查找同一组或分区中行之间的差。在这个例子中，充分发挥了标量子查询的优势，查找具有某种特定的某个值，想不出其他不用标量子查询的方法。复杂于，如果有人在同一天被雇佣，如果不使用标量子查询，想不出还有其他办法。35. 补充范围内丢失的值。