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

收集網上看到碰到見過的形形色色的關於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_id from dual union all select '2' wen_id, 'a' lan_id, '01_1' org_id from dual union all select '3' wen_id, 'b' lan_id, '01_1_2' org_id from dual union all select '4' wen_id, 'c' lan_id, '01' org_id from dual union all select '5' wen_id, 'a' lan_id, '09' org_id from dual union all select '6' wen_id, 'a' lan_id, '09_1' org_id from dual union all select '7' wen_id, 'b' lan_id, '010_1' org_id from dual union all select '8' wen_id, 'c' lan_id, '010' org_id from dual union all select '9' wen_id, 'c' lan_id, '09_1_1' org_id from dual union all select '10' wen_id, 'a' lan_id, '010_1_1' org_id from dual union all select '11' wen_id, 'a' lan_id, '010_1' org_id from dual union all select '12' wen_id, 'a' lan_id, '01' org_id from dual union all select '13' wen_id, 'b' lan_id, '01_2' org_id from dual )--解法一:case select 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的新特性PIVOT SELECT * 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基礎理論