【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基礎理論