【Oracle SQL】Oracle SQL 之七 SQL面试题比武招亲

海7318

　　收集網上看到碰到見過的形形色色的關於SQL的面試題或是實際工資中比較難一點的SQL應用
　　 持續更新中.....
　　SQL
　　1、問----Table結構如下：
　　

文章ID	欄目ID	機構ID
WEN_ID	LAN_ID	ORG_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

　　解----
--首先按題目測試數據生成表數據with temp_table as(select '1' wen_id, 'a' lan_id, '01' org_idfrom dualunion allselect '2' wen_id, 'a' lan_id, '01_1' org_idfrom dualunion allselect '3' wen_id, 'b' lan_id, '01_1_2' org_idfrom dualunion allselect '4' wen_id, 'c' lan_id, '01' org_idfrom dualunion allselect '5' wen_id, 'a' lan_id, '09' org_idfrom dualunion allselect '6' wen_id, 'a' lan_id, '09_1' org_idfrom dualunion allselect '7' wen_id, 'b' lan_id, '010_1' org_idfrom dualunion allselect '8' wen_id, 'c' lan_id, '010' org_idfrom dualunion allselect '9' wen_id, 'c' lan_id, '09_1_1' org_idfrom dualunion allselect '10' wen_id, 'a' lan_id, '010_1_1' org_idfrom dualunion allselect '11' wen_id, 'a' lan_id, '010_1' org_idfrom dualunion allselect '12' wen_id, 'a' lan_id, '01' org_idfrom dualunion allselect '13' wen_id, 'b' lan_id, '01_2' org_id from dual )--解法一:caseselect lan_id as "欄目",sum(case when substr(org_id,1,2)='01' and  substr(org_id,1,3)!='010' then 1 else 0 end) as "機構01",sum(case when substr(org_id,1,2)='09' then 1 else 0 end) as "機構09",sum(case when substr(org_id,1,3)='010' then 1 else 0 end) as "機構010"from temp_table group by lan_id--解法二：利用oracle 11g的新特性PIVOTSELECT *FROM (SELECT lan_id as "欄目", replace(substr(org_id, 1, 3), '_', '') as p_orgid FROM temp_table) PIVOT(count(1) FOR p_orgid IN('01' as "機構01",'09' as "機構09",'010' as "機構010"))　　



　　
　　
　　PL/SQL & T-SQL
　　
　　
　　
　　SQL基礎理論