巧妙运用null
[if !supportLists]1. [endif]Null的含义
例如，以下sql查不到第三条数据：
with t as(
select 1 cid from sysibm.dual union all
select 2 from sysibm.dual union all
select null from sysibm.dual
)
select * from t where cid!=1
[if !supportLists]2. [endif]Not In中存在null值会导致查询语句失效：
例如：
with t as(
select 1 cid from sysibm.dual union all
select 2 from sysibm.dual union all
select null from sysibm.dual
),
t1 as(
select 'a' name ,1 id from sysibm.dual union all
select 'b',2 from sysibm.dual union all
select 'c',6 from sysibm.dual
)
select * from t1 where id not in(select cid from t)
[if !supportLists]3. [endif]利用Count(null)不计数的特性
with t as(
select 1 cid from dual union all
select 2 from dual union all
select null from dual
)
--select count(1) from t where cid is not null --等价于
select count(cid) from t
但是在where条件上直接加过滤，有不适用的条件，例如我要同时计算总条数和CID非空的条数，在同时处理多指标加工时，需要扫描一次表计算出所有的指标，语句二显得非常有用，例如：
[if !supportLists]4. [endif]Null导致索引失效
根据实际情况分析执行计划，索引不存储NULL值。
巧妙运用with
with子句大部分情况下等同于子查询，优点是with结果集可以复用，另外牢记with的一个特点，很多时候with可以改变整个大sql的执行计划，所以有时候可以尝试用with改变执行计划来优化sql。
巧妙运用自连接
自连接有时候能帮助我们完成很多有趣的业务场景，比如说，查看济南公交车所有站点的22组合，查看济南市内公交转一次车能从一个站点达到另外一个站点。
有如下表：
站点表：
公交车途经站点表：
要求查询所有站点22组合：
转一次车可以实现的站点到达：
答案：
withstation(id,name) as(--站点表
select 1,'济南东' from sysibm.dual union all
select 2,'济南西' from sysibm.dual union all
select 3,'济南南' from sysibm.dual union all
select 4,'济南北' from sysibm.dual union all
select 5,'济南中' from sysibm.dual
),
bus_station_ref(bus_num,station_id) as(--公交车站点表
select 115,1 from sysibm.dual union all
select 115,2 from sysibm.dual union all
select 116,1 from sysibm.dual union all
select 116,4 from sysibm.dual union all
select 119,2 from sysibm.dual union all
select 119,3 from sysibm.dual
),
all_station_comps(id1,name1,id2,name2) as(--所有站点组合，自连接实现
select* fromstation t1,station t2 wheret1.id!=t2.id --and t1.id<t2.id
),
zc_lines(bus_num1 ,id1,name1, bus_num2,id2, name2) as(--转车线路组合 自连接实现
selectbef.bus_num ,bef.station_id,s1.name, r2.bus_num ,s2.id, s2.name
frombus_station_ref bef,bus_station_ref r1,bus_station_ref r2,bus_station_ref aft,station s1,station s2
where
r1.bus_num!=r2.bus_num ANDr1.station_id=r2.station_id--转车条件
ANDr1.station_id!=bef.station_id ANDr2.station_id!=aft.station_id--排除转车那一站两车衔接的组合
ANDbef.bus_num=r1.bus_num
ANDaft.bus_num=r2.bus_num
ANDbef.station_id=s1.id
ANDaft.station_id=s2.id
)
SELECT* FROMall_station_comps
--SELECT * FROM zc_lines
巧妙运用笛卡尔
以上公交车场景，也巧妙的运用了笛卡尔。
经典行转列
原理：通常使用聚合函数+分组，结合decode函数或者case表达式等等。
例如：有客户经理存款/贷款额度如下：
转换为如下结果：
答案：
witht as(
select '001'mgr,'ck'tp ,10000000bal from sysibm.dual
UNION ALL
select '001','dk',30000000 from sysibm.dual
UNION ALL
select '001','dk',30000000 from sysibm.dual
UNION ALL
select '002','ck',60000000 from sysibm.dual
UNION ALL
select '002','dk',60000000 from sysibm.dual
UNION ALL
select '002','dk',50000000 from sysibm.dual
)
SELECTmgr,
sum(DECODE(tp,'ck',bal,0)) ckye,
sum(DECODE(tp,'dk',bal,0)) dkye
FROMt
GROUP BYmgr
列转不定行
原理：递归，剥洋葱
有如下表格：
将C2列根据逗号拆分成多行，转成如下结果：
答案：
witht1 as
(
select 3c1,'eee,fff,ggg,yyy,jjj,lll'c2 from sysibm.dual union all
select 1c1,'aaa,bbb'c2 from sysibm.dual union all
select 2c1,'ppp'c2 from sysibm.dual
),
t2(c1,c2,f) as(
selectc1,c2,instr(c2,',') f fromt1
union all
selectc1,substr(c2,1,instr(c2,',')-1) c2,0f fromt2 wheref>0
union all
selectc1,substr(c2,instr(c2,',')+1) c2,instr(substr(c2,instr(c2,',')+1) ,',') f fromt2 wheref>0
)
select* fromt2 wheref=0 order byc1
行转不定列
因为列不定，因此sql的select表达式部分不确定，是sql定义部分，只能用plsql来写。
Sql递归
运算层级不定的，大多会用到递归。
举例：凭证项目一sql，需求如下：
场景描述：
凭证项目中有这么一个需求，查询机构层级关系，表里存着当前机构与父级机构，要实现机构与本机构、机构与父级机构、机构与爷爷级机构、……、机构与顶级机构，父级机构与父级机构、父级机构与爷爷级机构、……、父级机构与顶级机构，依次组合，直到顶级（注意，机构层级不确定）。
例如，数据如下（C1表示当前级机构，C2表示当前级的直接父级机构）：
从机构层级上看是这样：
a—b—c—d
a—b—e—f
想得到如下结果集：dd dc db da cc cb ca bb ba ff fe fb fa ee eb ea
思路：往外需要结合算法推导
答案：
WITHt as(
SELECT 'd'org,'c'parentorg FROM SYSIBM.dual --dc
UNION ALL
SELECT 'c'org,'b'parentorg FROM SYSIBM.dual --cb
UNION ALL
SELECT 'b'org,'a'parentorg FROM SYSIBM.dual --ba
UNION ALL
SELECT 'f'org,'e'parentorg FROM SYSIBM.dual --fe
UNION ALL
SELECT 'e'org,'b'parentorg FROM SYSIBM.dual --eb
),
t2(org,parentorg) as(
SELECTorg,parentorg FROMt
UNION ALL
SELECTa.org,aa.parentorg FROMt a,t2 aa WHEREa.parentorg=aa.org
)
SELECT* FROMt2
答案怎么来的？？？
第0层：
t表:
dc
cb
ba
fe
Eb
t2表无数据
第1层：
T表:
dc
cb
ba
fe
eb
t2表:
db
ca
ea
fb
第2层：
T表:
dc
cb
ba
fe
eb
第一层结果
db
ca
ea
fb
本次新增
da
fa
第3层：
t:
dc
cb
ba
fe
eb
第一层结果
db
ca
ea
fb
第二层结果
da
Fa
本次结果无数据，
循环退出。
Sql中使用算法
这个例子太多啦，主要考验灵活性，简单举几个吧！
举例1：
有如下表：
规则：按照acctno分组，按照seq排序，遇到diffdays>31，则重新排序
思路：重新规划分组字段
结果如下：
测试库截图：
举例2：
数据加工平台实际业务需求，很多个是否** 的标记，例如：
是否吃香菜是否加葱花是否加糖是否加盐是否加酱油是否放辣椒等等等等，而且这些条件可以任意组合，后面查询根据各种条件组合来查的情况非常多。
使用硬编码配置和数据组合，类似于linux权限 1 2 4分别表示可执行、可写、可读。
