mysql使用if和case语句生成

Question

我这里有一个表名table1

debit  credit    groups

100               group1
500     -100      group3
        -100      group3
200     -50       group2

我需要这样的输出

group1     group2       group3
100         200           500
            -50          -100
                         -100

这是我的问题

SELECT
IF((SELECT CASE WHEN groups = "group1"
THEN Debit ELSE Credit END),Debit,Credit) as group1,
IF((SELECT CASE WHEN groups = "group2"
THEN Debit ELSE Credit END),Debit,Credit) as group2,
IF((SELECT CASE WHEN groups = "group3"
THEN Debit ELSE Credit END),Debit,Credit) as group3
FROM table1

我得到的输出是

group1     group2       group3
    100         200           500

Answer 1

尝试将贷方和借方信息的查询分开，并使用union all 将这些列合并为一个按“组”值分组的列。

select
    case when groups = "group1" then credit end as group1,  
    case when groups = "group2" then credit end as group2,
    case when groups = "group3" then credit end as group3
from table1

union all

select
    case when groups = "group1" then debit end as group1,  
    case when groups = "group2" then debit end as group2,
    case when groups = "group3" then debit end as group3
from table1