需求：
课程表：tb_Course
|
CID |
CN(Course Name) |
|
1 |
C1 |
|
2 |
C2 |
区域国家表: tb_Area
|
AID |
AN(Area Name) |
CoID (Country ID) |
|
1 |
A1 |
1 |
|
1 |
A1 |
… |
|
1 |
A1 |
n |
|
2 |
A2 |
n+1 |
|
2 |
A2 |
… |
|
2 |
A2 |
2n |
|
3 |
A3 |
2n+1 |
|
3 |
A3 |
… |
|
3 |
A3 |
3n |
课程到国家的同步状态 tb_record
|
CID(Course ID) |
CoID(Country ID) |
status |
|
|
|
|
0，1，2 |
|
展示样例：
|
CID |
DistributeArea (include tb_record.status is 2) |
No_DistributeArea (include tb_record.status is 0, 1) |
|
1 |
A1(2);A2(2) |
A3(1) |
|
2 |
A3(2) |
A1(0);A2(1) |
实现：
第一步：先关联 tb_Area 和 tb_record 得到区域的同步状态AS以及区域分发状态AD：
Select tbR.CID, tbA.AN decode(min(tbR.status), ‘0’, ‘0’, ‘1’, ‘1’, ‘2’) AS, decode(min(tbR.status), ‘2’, ‘1’, ‘0’) AD
From tb_Area tbA left join tb_record tbR on tbA.CoID = tbR.CoID
Group by tbR.CID, tbA.AN 结果表：T1
|
CID |
AN |
AS |
AD |
|
1 |
A1 |
2 |
1 |
|
1 |
A2 |
2 |
1 |
|
1 |
A3 |
1 |
0 |
|
2 |
A1 |
0 |
0 |
|
2 |
A2 |
1 |
0 |
|
2 |
A3 |
2 |
1 |
第二步：思路是先构建一颗树，然后再将树转成列：
方法1：构建树，先设法构建父子关系表 T2
1、先给表排序获得行号作为父节点ID
Select T1.CID, T1.AN, T1.AS, T1.AD, rownum over(order by T1.CID,T1.AD) rnFirst
From T1 得到T2
|
CID |
AN |
AS |
AD |
rnFirst |
|
1 |
A1 |
2 |
1 |
1 |
|
1 |
A2 |
2 |
1 |
2 |
|
1 |
A3 |
1 |
0 |
3 |
|
2 |
A1 |
0 |
0 |
4 |
|
2 |
A2 |
1 |
0 |
5 |
|
2 |
A3 |
2 |
1 |
6 |
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
|
CID |
AS |
AD |
rnFirst |
rnNext |
|
1 |
2 |
1 |
1 |
2 |
|
1 |
2 |
1 |
2 |
Null |
|
1 |
1 |
0 |
3 |
Null |
|
2 |
0 |
0 |
4 |
5 |
|
2 |
1 |
0 |
5 |
Null |
|
2 |
2 |
1 |
6 |
Null |
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
|
CID |
AD |
Area |
|
1 |
1 |
,A1(2) |
|
1 |
1 |
,A1(2),A2(2) |
|
1 |
0 |
,A3(1) |
|
2 |
0 |
,A1(0) |
|
2 |
0 |
,A1(0),A2(1) |
|
2 |
1 |
,A3(2) |
合成一个语句：
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, rownum over(order by T1.CID,T1.AD) rnFirst
From T1
) T2
) T3
Start with T3.rnNext is Null
Connect by rnNext = prior rnFirst
方法二：10G 中的wmsys.wm_concat 方法
Select T1.CID, T1.Ad, wmsys.wm_concat(T3.AN ||’(‘ || T3.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 |
Aarer |
|
1 |
1 |
A1(2),A2(2) |
|
1 |
0 |
A3(1) |
|
2 |
0 |
A1(0),A2(1) |
|
2 |
1 |
A3(2) |
第四步：行转列
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 |
A1(2),A2(2) |
A3(1) |
|
2 |
A3(2) |
A1(0),A2(1) |
