渠道表Channel
——————————————————————————————————
parentid | id | channel
——————————————————————————————————
0 1 运营商
0 2 媒体
1 3 移动
1 4 联通
2 5 报纸
2 6 杂志
6 7 读者文摘
6 8 故事会
5 9 广州日报
5 10 深圳日报
........其实就是如下的一个树形结构根0--
运营商1--
--移动3
--联通4
媒体2 --
--报纸5
--广州日报9
--深圳日报10
--杂志6
--读者文摘7
--故事会8另外一张表
营业记录表A (其中channel_id跟 Channel 表中的 id关联)
——————————————————————————————————
id | channel_id | fee
——————————————————————————————————
1 3 100
2 4 50
3 9 30
4 7 20
5 5 10
6 3 120
7 8 15
8 6 60
9 10 45
10 4 40
11 5 15
12 7 20
13 1 140
14 2 110
........
这张表中,既有channel_id属于广州日报的记录,也有channel_id属于报纸的记录,
既有channel_id属于移动的记录,也有channel_id属于运营商的记录。
也就是说,树形结构中的每个节点(除了根节点0之外)在表A中都有可能有channel_id对应.现在要根据渠道(channel_id)来对营业记录表A进行分组统计费用(fee)
遵循以下规则,
如果我在界面上选择了根节点为起始节点,那么统计出来的结果应该是运营商 450 (属于运营商这个节点的,并包含其下所有子节点的)
媒体 325 (属于媒体这个节点的,并包含其下所有子节点的)如果我在界面上选择了运营商节点为起始节点,那么统计出来的结果应该是运营商 140 (单单属于运营商这个节点的)
移动 220 (属于移动这个节点的,并包含其下所有子节点的)
联通 90 (属于联通这个节点的,并包含其下所有子节点的)如果我在界面上选择了媒体节点为起始节点,那么统计出来的结果应该是媒体 110 (单单属于媒体这个节点的)
报纸 100 (属于报纸这个节点的,并包含其下所有子节点的)
杂志 115 (属于杂志这个节点的,并包含其下所有子节点的)也就是说选择某个节点为起始节点,然后统计这个节点下的所有数据,
然后按下一层节点进行分组,同时本节点跟下一层节点列在一起
请问用一句sql怎样写出来呢?
——————————————————————————————————
parentid | id | channel
——————————————————————————————————
0 1 运营商
0 2 媒体
1 3 移动
1 4 联通
2 5 报纸
2 6 杂志
6 7 读者文摘
6 8 故事会
5 9 广州日报
5 10 深圳日报
........其实就是如下的一个树形结构根0--
运营商1--
--移动3
--联通4
媒体2 --
--报纸5
--广州日报9
--深圳日报10
--杂志6
--读者文摘7
--故事会8另外一张表
营业记录表A (其中channel_id跟 Channel 表中的 id关联)
——————————————————————————————————
id | channel_id | fee
——————————————————————————————————
1 3 100
2 4 50
3 9 30
4 7 20
5 5 10
6 3 120
7 8 15
8 6 60
9 10 45
10 4 40
11 5 15
12 7 20
13 1 140
14 2 110
........
这张表中,既有channel_id属于广州日报的记录,也有channel_id属于报纸的记录,
既有channel_id属于移动的记录,也有channel_id属于运营商的记录。
也就是说,树形结构中的每个节点(除了根节点0之外)在表A中都有可能有channel_id对应.现在要根据渠道(channel_id)来对营业记录表A进行分组统计费用(fee)
遵循以下规则,
如果我在界面上选择了根节点为起始节点,那么统计出来的结果应该是运营商 450 (属于运营商这个节点的,并包含其下所有子节点的)
媒体 325 (属于媒体这个节点的,并包含其下所有子节点的)如果我在界面上选择了运营商节点为起始节点,那么统计出来的结果应该是运营商 140 (单单属于运营商这个节点的)
移动 220 (属于移动这个节点的,并包含其下所有子节点的)
联通 90 (属于联通这个节点的,并包含其下所有子节点的)如果我在界面上选择了媒体节点为起始节点,那么统计出来的结果应该是媒体 110 (单单属于媒体这个节点的)
报纸 100 (属于报纸这个节点的,并包含其下所有子节点的)
杂志 115 (属于杂志这个节点的,并包含其下所有子节点的)也就是说选择某个节点为起始节点,然后统计这个节点下的所有数据,
然后按下一层节点进行分组,同时本节点跟下一层节点列在一起
请问用一句sql怎样写出来呢?
然后用
select * from tbname start with id=0 connect by pid=id;
这种结构的语句就能检索整个树了。
2 SELECT t3.ID,
3 t3.parentid,
4 t3.channel,
5 SUM(fee) s_fee,
6 (SELECT SUM(s_fee)
7 FROM Channel t1, (SELECT channel_id, SUM(fee) s_fee FROM a t2 GROUP BY channel_id) t2
8 WHERE t1.ID = t2.channel_id
9 START WITH t1.ID = t3.ID
10 CONNECT BY PRIOR t1.ID = parentid) s_all
11 FROM Channel t3, a t4
12 WHERE t3.ID = t4.channel_id
13 GROUP BY t3.ID,t3.parentid, t3.channel)
14 WHERE ID = 2 OR parentid = 2; ID PARENTID CHANNEL FEE
---------- ---------- -------------------------------------------------- ----------
2 0 媒体 110
5 2 报纸 100
6 2 杂志 115SQL> SELECT ID,parentid,channel,decode(parentid,1,s_all,s_fee) fee FROM (
2 SELECT t3.ID,
3 t3.parentid,
4 t3.channel,
5 SUM(fee) s_fee,
6 (SELECT SUM(s_fee)
7 FROM Channel t1, (SELECT channel_id, SUM(fee) s_fee FROM a t2 GROUP BY channel_id) t2
8 WHERE t1.ID = t2.channel_id
9 START WITH t1.ID = t3.ID
10 CONNECT BY PRIOR t1.ID = parentid) s_all
11 FROM Channel t3, a t4
12 WHERE t3.ID = t4.channel_id
13 GROUP BY t3.ID,t3.parentid, t3.channel)
14 WHERE ID = 1 OR parentid = 1; ID PARENTID CHANNEL FEE
---------- ---------- -------------------------------------------------- ----------
1 0 运营商 140
3 1 移动 220
4 1 联通 90