要做什么看标题就懂了。。。
背景:1. 现有二表:test_main, test_sub
2. test_sub表的m_id是关联test_main的m_id字段的外键,main对sub为一对多。
求做成以下结果:
步骤
1.
test_main建表SQL:
create table test_main(
m_id number not null,
m_name varchar(10),
constraint test_main_pk primary key(m_id)
)
test_sub建表SQL:
create table test_sub(
s_id number,
m_id number,
s_name varchar(10),
constraint test_sub_pk primary key(s_id),
constraint test_sub_fk foreign key(m_id) references test_main(m_id)
)
2.插入数据:
test_main
test_sub
3.最终的查询SQL
select m_name,
ltrim(max(sys_connect_by_path(s_name, ',')), ',') as sub_names
from (
select m_name, s_name, m_id, rn1,
lead(rn1) over(partition by m_id order by rn1) rn2
from (
select core.*, row_number() over(order by core.m_id desc) rn1
from (
select m.m_name, s.m_id, s.s_name
from test_main m
join test_sub s on m.m_id = s.m_id
) core
)tmp
)tmp2
start with rn2 is null
connect by rn2 = prior rn1
group by m_id, m_name
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
思路:
1. 看core 子表,是业务核心SQL,目的是查出想要的所有数据。
select m.m_name, s.m_id, s.s_name
from test_main m
join test_sub s on m.m_id = s.m_id
结果:
2. row_number() over( [partition by col_g] order by col1 [, col2, ...] )函数, 这一函数表示若有partition字句,先根据col_g进行分组,在分组内根据col1 [, col2, ....]排序, 然后返回每组内部排序后的顺序编号(组内连续的唯一的),若无partition字句,表示不分组。row_number()与rownum字段的区别在于:使用rownum进行排序的时候是先对结果集加入伪行号,然后再进行排序,而row_number()是根据排序从句(order by col1...)先排序再计算行号码。
row_number()和rownum差不多,功能更强一点,根据partition by col_g分组后,可以在各个分组内从1开始排序。
如下SQL,根据m_id排个序然后给个rownumber rn1
select core.*, row_number() over(order by core.m_id desc) rn1
from (
select m.m_name, s.s_name, s.m_id
from test_main m
join test_sub s on m.m_id = s.m_id
) core
结果:
3.lead(col [, offset] [, default] ) over( [partition by col_g] order by col1 [, col2, ...] ) 函数,lead为返回相对当前这行记录的下offset行记录相应字段col的值,default表示当col的值为null时返回的值,默认为null, 不指定offset表示默认为1,则意思是返回当前行记录的下一行记录col的值,若为负数,则表示相对当前行记录的后offset行(要看oracle版本,有些貌似不支持负数)。
以test_sub表为例,先看以下SQL:
select s_id, lead(s_id) over(order by s_id asc) as sid
from test_sub
意思是查出s_id和此s_id 对应的下一行s_id的值,结果:
再看SQL:
select s_id, m_id, lead(s_id) over(partition by m_id order by s_id asc) as sid
from test_sub
加入分组后,则lead操作的范围缩小到组里面了,先看结果:
对比上面没有partition的SQL结果可以发现,s_id为3的记录在此处sid为空,那是因为partition by m_id的原因,现在是m_id组内的lead操作。在m_id为1的组里,s_id为3的记录是最后的记录了,所以它往后的不存在的行记录的s_id 的值必然为null。而在没有partition的情况下s_id为3的记录往后的行记录是s_id 为4的那个,所以lead出来是4。
回到tmp2子查询语句上面,SQL:
select m_name, s_name, m_id, rn1,
lead(rn1) over(partition by m_id order by rn1) rn2
from (
select core.*, row_number() over(order by core.m_id desc) rn1
from (
select m.m_name, s.s_name, s.m_id
from test_main m
join test_sub s on m.m_id = s.m_id
) core
)tmp
tmp子查询语句先根据m_id分组后表上行号RN1,然后根据RN1进行m_id组内lead操作。
结果:
4.start with...connect by 字句。。。
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
在此另举以案例说明之:
1)创建示例表
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
2)插入数据如下:
从上面的记录中id与pid的关系可以得出此时所有记录可以抽象出一棵树,其中PID字段表示父节点的ID值:
3)测试SQL:
select * from TBL_TEST
start with id=1
connect by prior id = pid
意思是从id为1的节点开始遍历其所有子节点,结果:
从结果看,这是个先序遍历(有关二叉树且看在下所作笔记:http://gwoham-163-com.iteye.com/blog/1896260),或自行google。至于oracle底层如何执行,恕在下愚钝。。。
再看start with 是指定从那个节点开始遍历;注意此处connect by prior id = pid,prior为优先之意,此处为id优先,则整个SQL表示遍历id为1节点的所有子节点。若改为connect by prior pid = id则变成遍历其所有父节点了。
再看SQL:
select * from TBL_TEST
start with id=5
connect by prior pid = id
意思是遍历id为5的节点的所有父节点(不包括最root节点,应为id为0的记录没有),结果:
了解了start with...connect by子句是以遍历树的方式查询出记录,分析tmp2查询子句结果集:
tmp2查询子句返回具有树形结构的数据,RN2为child,RN1为parent。
此查询语句表示查询子节点RN2为空的所有父节点,目的是揪出根据m_Id分组的三棵树的数据:
select m_name, rn1, rn2
from (
select m_name, s_name, m_id, rn1,
lead(rn1) over(partition by m_id order by rn1) rn2
from (
select core.*, row_number() over(order by core.m_id desc) rn1
from (
select m.m_name, s.s_name, s.m_id
from test_main m
join test_sub s on m.m_id = s.m_id
) core
)tmp
)tmp2
start with rn2 is null
connect by rn2 = prior rn1
给出tmp2的数据的树状图:
三棵树分别是Oham,Lulu,Cancan ;红色部分为root节点,实际不存在的记录。
三棵树分别遍历,结果为:
但问题是为什么要遍历这样的树呢?目的是使用sys_connect_by_path(node_col, conn_sbl)函数,其意思是让你把节点字段node_col按照当前遍历路径用conn_sbl符号连接起来,建议自行google深入了解。
执行SQL:
select m_name, rn1, rn2, s_name,
sys_connect_by_path(s_name, ',') as sub_names
from (
select m_name, s_name, m_id, rn1,
lead(rn1) over(partition by m_id order by rn1) rn2
from (
select core.*, row_number() over(order by core.m_id desc) rn1
from (
select m.m_name, s.m_id, s.s_name
from test_main m
join test_sub s on m.m_id = s.m_id
) core
)tmp
)tmp2
start with rn2 is null
connect by rn2 = prior rn1
因为当前prior指定是RN1优先(遍历父节点),而sys_connect_by_path函数就把当前节点其下的子节点以及自己(从最下至上)用逗号拼接在一起(说实话还不太明白按当前路径之说法什么意思,若把优先改成RN2,遍历子节点,那sys_connect_by_path就把其上的父节点以及自己从最上至下拼接),结果:
接下来就简单了,就上面的结果简单整理一下就行了,按m_name分组,取sub_names值最长的记录,把左边的逗号trim掉,然后大功告成。
执行SQL:
select m_name,
ltrim(max (sys_connect_by_path(s_name, ',')), ',') as sub_names
from (
select m_name, s_name, m_id, rn1,
lead(rn1) over(partition by m_id order by rn1) rn2
from (
select core.*, row_number() over(order by core.m_id desc) rn1
from (
select m.m_name, s.m_id, s.s_name
from test_main m
join test_sub s on m.m_id = s.m_id
) core
)tmp
)tmp2
start with rn2 is null
connect by rn2 = prior rn1
group by m_name
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
整理思路一下:
1.core是查出所有想要的数据。
2.tmp是对core的数据加入行序号。
3.tmp2是利用tmp中的行序号作分组lead操作,目的是构造出具有父子关系(树状)的数据形式 ——RN1为父,RN2为子,对应回test_main与test_sub的一对多主从关系。
4.有了tmp2具有树状结构的数据记录,结合start with...connect by语句,运用sys_connect_by_path函数查询出拼接出逗号的字段值。
5.最后对结果进行分组修饰处理。