NCHSIEH 發問時間： 電腦與網際網路程式設計 · 8 年前

# SQL SELECT IF 問題

TAB-1

ID_欄位1_ 欄位2

---------------------------

001 A 10

001 B 20

002 B 10

002 C 30

002 D 40

TAB-2

ID _A _ B _ C _ D

-----------------------------

001 10 20 -- --

002 -- 10 30 40

### 2 個解答

• 8 年前
最佳解答

select C.id, sum(C.A) A, sum(C.B) B, sum(C.C) C, sum(C.D) D

from

(

select id, sum(f2) A, 0 B, 0 C, 0 D

from test

where id = '001' and f1 = 'A'

group by id, f1

union

select id, 0 A, sum(f2) B, 0 C, 0 D

from test

where id = '001' and f1 = 'B'

group by id, f1

) C

group by C.id

union

select C.id, sum(C.A) A, sum(C.B) B, sum(C.C) C, sum(C.D) D

from

(

select id, 0 A, sum(f2) B, 0 C, 0 D

from test

where id = '002' and f1 = 'B'

group by id, f1

union

select id, 0 A, 0 B, sum(f2) C, 0 D

from test

where id = '002' and f1 = 'C'

group by id, f1

union

select id, 0 A, 0 B, 0 C, sum(f2) D

from test

where id = '002' and f1 = 'D'

group by id, f1

) C

group by C.id

2012-08-09 09:13:48 補充：

test -> TAB-1

• 用4次left join就可以了