表结构数据如下
文章id 栏目id 所属机构id
1 a 01
2 a 01_1
3 b 01_1_2
4 c 01
5 a 09
6 a 09_1
7 b 010_1
8 c 010
9 c 09_1_1
10 a 010_1_1
11 a 010_1
12 a 01
13 b 01_2
其中机构id 01_ 09_ 010_ 都为相应01、09、010机构子机构
请问如何得到以下统计结果
获得各个机构（包括子机构）在各个栏目下发布的文章总数
栏目 机构01 机构09 机构010
a 3 2 2
b 2 0 1
c 1 1 1
------解决方案--------------------
这不还是上次那个么？
- SQL code
with t as(select '1' wen_id,'a' lan_id, '01' zuo_id from dualunion allselect '2' wen_id,'a' lan_id, '01_1' zuo_id from dualunion allselect '3' wen_id,'b' lan_id, '01_1_2' zuo_id from dualunion allselect '4' wen_id,'c' lan_id, '01' zuo_id from dualunion allselect '5' wen_id,'a' lan_id, '09' zuo_id from dualunion allselect '6' wen_id,'a' lan_id, '09_1' zuo_id from dualunion allselect '7' wen_id,'b' lan_id, '010_1' zuo_id from dualunion allselect '8' wen_id,'c' lan_id, '010' zuo_id from dualunion allselect '9' wen_id,'c' lan_id, '09_1_1' zuo_id from dualunion allselect '10' wen_id,'a' lan_id, '010_1_1' zuo_id from dualunion allselect '11' wen_id,'a' lan_id, '010_1' zuo_id from dualunion allselect '12' wen_id,'a' lan_id, '01' zuo_id from dualunion allselect '13' wen_id,'b' lan_id, '01_2' zuo_id from dual)select tt.lan_id, MAX(nvl(decode(tt.org_id, '01', nm), 0)) 机构01, MAX(nvl(decode(tt.org_id, '09', nm), 0)) 机构09, MAX(nvl(decode(tt.org_id, '010', nm), 0)) 机构010 from ( select s.org_id, s.lan_id, count(s.wen_id) nm from (select t.wen_id, t.lan_id, substr(t.zuo_id, 1, decode(instr(t.zuo_id, '_', 1), 0, length(t.zuo_id) + 1, instr(t.zuo_id, '_') - 1)) org_id from t) s group by s.lan_id, s.org_id order by s.org_id, s.lan_id) tt group by tt.lan_id order by tt.lan_id
------解决方案--------------------
这和上次那个差不多啊
- SQL code
with t as(select '1' wen_id,'a' lan_id, '01' zuo_id from dualunion allselect '2' wen_id,'a' lan_id, '01_1' zuo_id from dualunion allselect '3' wen_id,'b' lan_id, '01_1_2' zuo_id from dualunion allselect '4' wen_id,'c' lan_id, '01' zuo_id from dualunion allselect '5' wen_id,'a' lan_id, '09' zuo_id from dualunion allselect '6' wen_id,'a' lan_id, '09_1' zuo_id from dualunion allselect '7' wen_id,'b' lan_id, '010_1' zuo_id from dualunion allselect '8' wen_id,'c' lan_id, '010' zuo_id from dualunion allselect '9' wen_id,'c' lan_id, '09_1_1' zuo_id from dualunion allselect '10' wen_id,'a' lan_id, '010_1_1' zuo_id from dualunion allselect '11' wen_id,'a' lan_id, '010_1' zuo_id from dualunion allselect '12' wen_id,'a' lan_id, '01' zuo_id from dualunion allselect '13' wen_id,'b' lan_id, '01_2' zuo_id from dual)select tt.lan_id, MAX(nvl(decode(tt.org_id, '01', nm), 0)) 机构01, MAX(nvl(decode(tt.org_id, '09', nm), 0)) 机构09, MAX(nvl(decode(tt.org_id, '010', nm), 0)) 机构010 from ( select s.org_id, s.lan_id, count(s.wen_id) nm from (select t.wen_id, t.lan_id, substr(t.zuo_id, 1, decode(instr(t.zuo_id, '_', 1), 0, length(t.zuo_id) + 1, instr(t.zuo_id, '_') - 1)) org_id from t) s group by s.lan_id, s.org_id order by s.org_id, s.lan_id) tt group by tt.lan_id order by tt.lan_id