Oracle里的交叉SQL語句寫法
2024-08-29 13:29:13
供稿：網友
實例說明
資料表結構(紅色為主鍵)==>
主表:test_part_cost_tab(料號資料表)
part_no varchar2(20)
part_name varchar2(50)
從表:test_part_cost_dt_tab(料號成本資料表)
part_no varchar2(10)
cost_id varchar2(5)
cost number
數據==>
主表資料:
part_no part_name
1 1000 name1000
2 1001 name1001
從表資料:
part_no cost_id cost
1 1000 100 1.1
2 1000 200 1.2
3 1000 300 1.3
4 1000 321 1.321
5 1001 100 2.1
交叉資料==>
sql語句產生的結果
part_no part_name cost_100 cost_200 cost_300 cost_321
1 1000 name1000 1.1 1.2 1.3 1.321
2 1001 name1001 2.1 0 0 0
具體的交叉sql語句寫法:
select a.part_no,a.part_name,
--sum(b.cost)
sum(case when b.cost_id = '100' then b.cost else 0 end) as cost_100,
sum(case when b.cost_id = '200' then b.cost else 0 end) as cost_200,
sum(case when b.cost_id = '300' then b.cost else 0 end) as cost_300,
sum(case when b.cost_id = '321' then b.cost else 0 end) as cost_321
from test_part_cost_tab a,test_part_cost_dt_tab b
where a.part_no = b.part_no
group by a.part_no,a.part_name
ps: 若主表有資料,從表沒有資料時,交叉后會沒有相應的資料
解決辦法是在where條件里用外連接
where a.part_no = b.part_no(+)
參考資料:
http://www.cnblogs.com/iouniuniu/archive/2004/04/05/5238.aspx ==>此文的語法與本文的語法不同
http://it.icxo.com/htmlnews/2004/11/11/454293.htm
http://www.itpub.net/176727.html
