promotion image of download ymail app
Promoted

SQL SELECT IF 問題

請問各位SQL高手,若有以下問題,請問SQL語法是否有可能達成TAB-1轉成TAB-2的結果? TAB-2的表格結構事先create好。謝謝!

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

    • Commenter avatar登入以對解答發表意見
  • 用4次left join就可以了

    • Commenter avatar登入以對解答發表意見
還有問題?馬上發問,尋求解答。