需求：
课程表：tb_Course
|
CID |
CN(Course Name) |
|
1 |
C1 |
|
2 |
C2 |
区域表：tb_Area
|
AID |
AN |
|
1 |
A1 |
|
2 |
A2 |
|
3 |
A3 |
|
4 |
A4 |
|
5 |
A5 |
区域国家表: tb_Area_Country
|
AID |
AN(Area Name) |
CoID (Country ID) |
|
1 |
A1 |
1 |
|
1 |
A1 |
2 |
|
1 |
A1 |
3 |
|
2 |
A2 |
4 |
|
2 |
A2 |
5 |
|
2 |
A2 |
6 |
|
3 |
A3 |
7 |
|
4 |
A4 |
8 |
|
5 |
A5 |
9 |
课程到国家的同步状态 tb_record （没有记录的就是未同步）
|
CID(Course ID) |
CoID(Country ID) |
Status |
|
|
1 |
1 |
1 |
1:同步中,2:已同步 |
|
1 |
2 |
2 |
|
|
1 |
3 |
1 |
|
|
1 |
4 |
1 |
|
|
1 |
5 |
2 |
|
|
1 |
6 |
2 |
|
|
1 |
7 |
2 |
|
|
1 |
8 |
1 |
展示样例：
|
CID |
DistributeArea (include tb_record.status is 2) |
No_DistributeArea (include tb_record.status is 0, 1) |
|
1 |
A3(已同步) |
A1(同步中)、A2(同步中)、A4(同步中)、A5(未同步) |
|
2 |
A1(未同步)、A2(未同步)、A2(未同步)、A4(未同步)、A5(未同步) |
实现：
第一步：先关联 tb_Area 和 tb_record 得到区域的同步状态AS以及区域分发状态AD：
Select tbR.CID, tbA.AN, decode(min(tbR.status),‘1’, ‘同步中’, ‘已同步’) AS, decode(min(tbR.status), ‘2’, ‘1’, ‘0’) AD
From tb_Area_Country tbA join tb_record tbR on tbA.CoID = tbR.CoID
Group by tbR.CID, tbA.AN 结果表：T1
|
CID |
AN |
AS |
AD |
|
1 |
A1 |
同步中’ |
0 |
|
1 |
A2 |
同步中’ |
0 |
|
1 |
A3 |
已同步 |
1 |
|
1 |
A4 |
同步中’ |
0 |
第二步：用课程表和区域表做迪卡积，除去同步中及已同步的记录，再与同步中，已同步的记录做union，得到所有记录
Select c.cid, a.AN, '未同步' AS, '0' AD
from tb_Course c cross join tb_area a
where not exists(select 1 from
(Select tbR.CID, tbA.AN, decode(min(tbR.status),’1’,’同步中’,’已同步’) AS,
decode(min(tbR.status), ‘2’, ‘1’, ‘0’) AD
From tb_Area_Country tbA join tb_record tbR on tbA.CoID = tbR.CoID
Group by tbR.CID, tbA.AN
) t
Where t.cid = c.cid and t.AN = a.AN
)
union
Select tbR.CID, tbA.AN, decode(min(tbR.status),’1’,’同步中’,’已同步’) AS,
decode(min(tbR.status), ‘2’, ‘1’, ‘0’) AD
From tb_Area_Country tbA join tb_record tbR on tbA.CoID = tbR.CoID
Group by tbR.CID, tbA.AN;
|
CID |
AN |
AS |
AD |
|
1 |
A1 |
同步中 |
0 |
|
1 |
A2 |
同步中 |
0 |
|
1 |
A3 |
已同步 |
1 |
|
1 |
A4 |
同步中 |
0 |
|
1 |
A5 |
未同步 |
0 |
|
2 |
A1 |
未同步 |
0 |
|
2 |
A2 |
未同步 |
0 |
|
2 |
A3 |
未同步 |
0 |
|
2 |
A4 |
未同步 |
0 |
|
2 |
A5 |
未同步 |
0 |
一二步可以合起来做，如果没有过滤条件可以用定义成视图，如果有查询条件就可以创建一个临时表，过滤后插入临时表。我们暂且定义为视图：T1.
第三步：思路是先构建一颗树，然后再将树转成列：
方法1：构建树，先设法构建父子关系表 T2
1、先给表排序获得行号作为父节点ID
Select T1.CID, T1.AN, T1.AS, T1.AD, row_number() over(order by T1.CID,T1.AD) rnFirst
From T1 得到T2
2、使用lead函数和分区特性得到子节点id
Select T2.CID, T2.AN, T2.AS, T2.AD, T2.rnFirst, lead(T2.rnFirst) over(partition by T2.CID,T2.AD order by T2.rnFirst) rnNext From T2 得到T3
3、构造树，利用sys_connect_by_path函数链接字符串
Select T3.CID, T3.AD, sys_connect_by_path(T3.AN ||’(‘ || T3.AS || ‘)’, ‘、’) Area
From T3
Start with T3.rnNext is Null
Connect by rnNext = prior rnFirst 得到T4
合成一个语句：
Select T3.CID, T3.AD, sys_connect_by_path(T3.AN ||’(‘ || T3.AS || ‘)’, ‘、’) Area
From (Select T2.CID, T2.AN, T2.AS, T2.AD, T2.rnFirst, lead(T2.rnFirst) over(partition by T2.CID,T2.AD order by T2.rnFirst) rnNext
From (Select T1.CID, T1.AN, T1.AS, T1.AD, row_number() over(order by T1.CID,T1.AD) rnFirst
From T1
) T2
) T3
Start with T3.rnNext is Null
Connect by rnNext = prior rnFirst
方法2：10G 中的wmsys.wm_concat 方法
Select T1.CID, T1.Ad, wmsys.wm_concat(T3.AN ||'('||t1.AS||')') over(partition by T1.CID, T1.AD order by rownum) Area
From T1
构建树的结果也可以保存到一个临时表里。
第四步、分组取最大值：
Select T4.CID, T4.AD, ltrim(max(t4.Arear), ‘、’) Area From T4 得到T5
|
CID |
AD |
Area |
|
1 |
1 |
A3(已同步) |
|
1 |
0 |
A1(同步中)、A2(同步中)、A4(同步中)、A5(未同步) |
|
2 |
0 |
A1(未同步)、A2(未同步)、A2(未同步)、A4(未同步)、A5(未同步) |
第四步：行转列
Select T5.CID, max(decode(T5.AD, ‘1’, T5.Area, ‘’)) v1, max(decode(T5.AD, ‘0’, T5.Area, ‘’)) v2
From T5
Group by T5.CID
|
CID |
V1 |
V2 |
|
1 |
A3(已同步) |
A1(同步中)、A2(同步中)、A4(同步中)、A5(未同步) |
|
2 |
A1(未同步)、A2(未同步)、A2(未同步)、A4(未同步)、A5(未同步) |
