mysql - 如何在mysql中使用group by获取新的字符串列

Question

基本上我的问题是

  id  sid  string
    5    1    AAA
    6    1    BBB
    7    2    CCC
    8    3    ZZZ
    9    3    EEE

得到这样的输出

sid   str1   str2
1     AAA    BBB

分组依据 sid

Answer 1

尝试以下数据透视查询:

SELECT t.sid,
    CASE WHEN t.str1 > 0 THEN 'AAA' ELSE NULL END AS str1,
    CASE WHEN t.str2 > 0 THEN 'BBB' ELSE NULL END AS str2,
    CASE WHEN t.str3 > 0 THEN 'CCC' ELSE NULL END AS str3
FROM
(
    SELECT t.sid,
        SUM(CASE WHEN t.string = 'AAA' THEN 1 ELSE 0 END) AS str1,
        SUM(CASE WHEN t.string = 'BBB' THEN 1 ELSE 0 END) AS str2,
        SUM(CASE WHEN t.string = 'CCC' THEN 1 ELSE 0 END) AS str3
    FROM yourTable t
    GROUP BY t.sid
) t