Mysql with Group by + IF + Left Join + Group_Concat

Question

我认为它很微不足道，但我坚持这样做:

SELECT u.id, u.name,

IF(s.prod_id=0, 1,NULL) as amount,
IF(s.prod_id=92, 2,NULL) as amount2,
IF(s.prod_id=116, 3,NULL) as amount3,
IF(s.prod_id=134, 4,NULL) as amount4

FROM table_user u

LEFT JOIN table_user_status s ON s.user_id = u.id 
where s.prod_id = 0 OR s.prod_id = 92 OR s.prod_id = 117 OR s.prod_id = 134

-- GROUP BY u.id
order by u.id ASC 

我得到类似的东西:

22 | Matt | 1|NULL|NULL|NULL

22 | Matt | NULL|2|NULL|NULL

按 id 分组后，我得到 22 MATT 1 NULL NULL NULL，然后是下一条记录。

最后我必须使用 group_concat 来获取像这样的数据22 |马特 | 1,2 |

谢谢!

Answer 1

使用 CASE 表达式将所有这些情况作为单个列返回，然后只需 GROUP_CONCAT 即可:

SELECT
    u.id,
    u.name,
    GROUP_CONCAT(amount ORDER BY amount) amounts
FROM
(
    SELECT
        u.id,
        u.name,
        CASE s.prod_id
            WHEN 0   THEN 1
            WHEN 92  THEN 2
            WHEN 116 THEN 3
            WHEN 134 THEN 4
        END AS amount
    FROM table_user u
    LEFT JOIN table_user_status s
        ON s.user_id = u.id 
    WHERE s.prod_id IN (0, 92, 116, 134)
) u
GROUP BY
    u.id,
    u.name
ORDER BY u.id