sql cookbook学习笔记db2.docx

sql cookbook学习笔记db2.docx

《sql cookbook学习笔记db2.docx》由会员分享，可在线阅读，更多相关《sql cookbook学习笔记db2.docx（15页珍藏版）》请在冰豆网上搜索。

sqlcookbook学习笔记db2

1.对表中记录随机排序：

orderbyrand（）;

2.Coalesce（）coalesce（case"MIDINIT"when''thennullelse"MIDINIT"end,casesubstr（"LASTNAME",1,1）when'A'thennullelse'µÚ¶þ¸öλÖÃ'end,'µÚÈý¸öλÖÃ'）

3.处理字母数字混合的字符串：

只想按着字母处理，或只想按着数字处理。

Translate（data,’目标格式’,’要替换的字符’）,raplace（data,’要替换的内容’,’替换的目标’），通过两次替换得到最终想要的字符或数字。

--限制，要连续的！

4.空值排序：

用一个子查询将null值与其他值用数字表示，在新的里再用这个值参与排序。

另外，nullfirst或，，

5.在orderby后面可以使用case表达式，从而实现根据不同的标准对数据进行排序。

Orderof是指按查询表中的某个进行排序，使用相同的顺序。

6.从一张表中查找另一张表中相同的值，使用in

7.从一张表中查找另一张表中没有的值，用集合讲，就是求集合A-集合B，

方法一：

方法二：

方法三：

方法四：

区别：

a、Except不会返回重复行，不用担心NULL。

b、Notin等价于not（谓词1or谓词2），如果其中之一为NULL，会导致结果为NULL，造成没有结果返回。

In和notin本质上是OR运算，因而计算逻辑OR时处理NULL的方式不同，产生的结果也不同。

等价运算。

运算过程：

在SQL中，trueornull的结果是true,falseornull的结果是NULL。

另外，notin,在后一个表中是空表时，会返回左边表中的所有值。

---where后面的运算结果是Null,不会返回任何值。

等价于false.

--如果运算符是and,则结果永远为空，等价于false--------根据验证结果猜测。

c、Exists运算只有两个结果，真或没有返回值。

如果是前者，notexists为假。

否则一律为真。

如果有一个相等的，则导致结果为假。

否则只要没有相等的，一律为真。

8.Distinct的替代方法。

本例是求并集-交集，当中的部分内容使用了这种方法：

9.在运算或比较中使用NULL值。

NULL值有时表示是还没有，这时意味着null是有值的，代表最小。

如提成，NULL表示的是还没有提成，是最少的。

在查找提成最少的人的时候，要特别处理这个字段为Null的。

办法--------coalesce（data,0）

10.插入数据：

可以在insert插入时使用default，插入的值是定义表时默认的值。

11.一次向多个表中插入记录。

关键是能定位某条记录能插入到哪种表中。

原理是使用视图。

12.删除重复记录。

这里有一个技巧，使用集合函数min来得到集合中不重复的部分

13.计算字符在字符串中出现的次数：

注意这里使用了除法，原因是LL占两个字节，计算结果是2乘以出现次数长度，所以要除以重复字符创长度得到的才是次数。

14.将字符和数字数据分离

这里用了几个函数，一是lower，字母全部小写。

二是repeat，不用一直写26个。

15.提取字符串中的所有数字，但数字出现的位置未知，其他字符是什么也不知道：

首先将数字弄没，得到剩下的字符；再把这些字符转译成相同字符，并去掉；得到的就是只有数字的了。

16.行变列-将行变为列

witht（workdept,phoneno_str,trn,ttol）as

（select"WORKDEPT",cast（"PHONENO"asvarchar（1000））,rn,tolfrom

（

select"WORKDEPT","PHONENO",row_number（）over（partitionbyworkdept）asrn,count（*）over（partitionbyworkdept）astol

from"DB2ADMIN"."EMPLOYEE"

）bt

wherern=1

unionall

selectt.workdept,phoneno_str||''||coalesce（phoneno,''）,trn+1,ttolfromt,

（

select"WORKDEPT","PHONENO",row_number（）over（partitionbyworkdept）asrn

from"DB2ADMIN"."EMPLOYEE"

）bt1

wheret.workdept=bt1.workdeptandbt1.rn=trn+1andtrn+1<=ttol

）

select*fromtwheretrn=ttol

17.将上述结果返回来变，变回原来的样子：

selectstrip（tstr）,substr（tstr,pos+1,locate（'',tstr,pos+1）-pos-1）from

（

select''||PHONENO_STR||''aststr,caseposwhen1then1elsepos+1endaspos,cast（translate（substr（PHONENO_STR,pos,1）,'#',''）asvarchar

（1））aschfrom"DB2ADMIN"."TT",

（selectrow_number（）over（）asposfrom"DB2ADMIN"."TT","DB2ADMIN"."TT","DB2ADMIN"."TT","DB2ADMIN"."TT","DB2ADMIN"."TT"）p

wherepos

）t

wherech='#'orpos=1

orderby1

18.将每条记录中的字符串中的字符进行排序

技巧点：

a.在max函数中使用case表达式。

通过这种方式实现了逐个位置安置字符。

b.通过使用对全表排序来的得到一个1、2、3、、、。

。

的列表，而不用递归。

问题是可能不够，就是列的最大数还不够将一条记录拆为单个字符。

c.拆单个字符的方法

d.拆单个字符后，再排列，加行号。

e.问题，case那块，是有限制的，超过6个的就不能用了。

19.计算平均值问题：

Null不参与运算，导致结果出错。

20.生成累乘积

技巧：

A．计算累计和

B．用对数、指数互换实现累计乘

21.计算累计差

技巧：

a.仍然利用累计和，但是要识别第一行

b.通过对行加行标识实现标识行

22.计算模式：

技巧：

a.这样做的好处是能找出所有最大的，而不是一个。

23.查找中间值：

技巧：

a．通过ceil及。

。

得到整除顶及底的结果。

b．多增加的几个列，把要用到的中间结果都记录下来。

c．Where中通过or包含两种情况

24.求总和的百分比。

首先求部门和，然后再计算这个和在总和中的百分比。

技巧：

使用partitionby

25.确定两个日期之间的工作日数

技巧：

a．在sum中使用case表达式，且case表达式中使用in谓词进行判断。

b．使用dataname函数

c．Max函数中使用case表达式，相当于groupby

d．通过t500列出每一天

26.确定两个日期直接的月数和年数，既要算年，也要算月。

用年算月。

27.确定两个日期之间的秒分时数。

用天算的，不是很精确

28.计算一年中周内各个日期的次数

技巧：

a.获得一年中第一天的方法

b.用下一年的第一天减去一天作为本年年尾

29.计算相邻时间的相差天数，关键是查找到最近的那天的方法

30.通过2月的最后一天判断该年是否是闰年：

witht（p）as（selecthiredatefromemp）

selectp,caseday（（（p-（dayofyear（p）-1）day）+2month）-1day）when28then'ƽÄê'else'ÈòÄê'endfromt

31.确定月的第一天和最后一天：

使用day函数：

32.像日历一样打印一个月的每一天

witht7（num）as（（values1）unionallselectnum+1fromt7wherenum<7）,

tt（p）as（valuescurrentdate）,

trlt（p1,p2,p3,p4,p5,p6,p7,flag）as（

selectmax（caseposwhen1thendayend）,

max（caseposwhen2thendayend）,

max（caseposwhen3thendayend）,

max（caseposwhen4thendayend）,

max（caseposwhen5thendayend）,

max（caseposwhen6thendayend）,

max（caseposwhen7thendayend）,

1

from

（select（p-day（p）day+1day）-dayofweek（p-day（p）day+1day）day+numdayasday,row_number（）over（）asposfromtt,t7）trow

unionall

selectp1+7day,p2+7day,p3+7day,p4+7day,p5+7day,p6+7day,p7+7day,flag+1fromtrltwhereflag<5

）

selectcasemonth（p1）when（selectmonth（p）fromtt）thenchar（day（p1））else''end,

casemonth（p2）when（selectmonth（p）fromtt）thenchar（day（p2））else''end,

casemonth（p3）when（selectmonth（p）fromtt）thenchar（day（p3））else''end,

casemonth（p4）when（selectmonth（p）fromtt）thenchar（day（p4））else''end,

casemonth（p5）when（selectmonth（p）fromtt）thenchar（day（p5））else''end,

casemonth（p6）when（selectmonth（p）fromtt）thenchar（day（p6））else''end,

casemonth（p7）when（selectmonth（p）fromtt）thenchar（day（p7））else''end

fromtrlt

33.在同一个表中各个行之间进行比较，求符合条件的记录，注意比较次数，通过限制实现只比较一次

34.查找同一组或分区中行之间的差。

在这个例子中，充分发挥了标量子查询的优势，查找具有某种特定的某个值，想不出其他不用标量子查询的方法。

复杂于，如果有人在同一天被雇佣，如果不使用标量子查询，想不出还有其他办法。

35.补充范围内丢失的值。