如何在Mysql中使用count?

如果你的状态一直是静态的,那5个案例。你可以用 left join 和 group by 以及一些开关情况下的条件来解决查询。

SELECT Users.id_user ,
SUM(CASE WHEN Get_cal.status = 'ABAN' THEN 1 ELSE 0 END) AS ABAN ,
SUM(CASE WHEN Get_cal.status = 'DES' THEN 1 ELSE 0 END) AS DES ,
SUM(CASE WHEN Get_cal.status = 'LET' THEN 1 ELSE 0 END) AS LET ,
SUM(CASE WHEN Get_cal.status = 'NOANS' THEN 1 ELSE 0 END) AS NOANS ,
SUM(CASE WHEN Get_cal.status = 'OTH' THEN 1 ELSE 0 END) AS OTH 
FROM Users LEFT JOIN Get_cal ON (Users.id_user = Get_cal.id_user) GROUP BY Users.id_user ,Get_cal.id_user

您需要在此处进行条件聚合:

SELECT
    u.id_user,
    COUNT(CASE WHEN c.status = 'ABAN'  THEN 1 END) AS ABAN,
    COUNT(CASE WHEN c.status = 'DES'   THEN 1 END) AS DES,
    COUNT(CASE WHEN c.status = 'LET'   THEN 1 END) AS LET,
    COUNT(CASE WHEN c.status = 'NOANS' THEN 1 END) AS NOANS,
    COUNT(CASE WHEN c.status NOT IN ('ABAN', 'DES', 'LET', 'NOAN')
               THEN 1 END) AS OTH
FROM Users u
LEFT JOIN Get_cal c
    ON c.id_user = u.id_user
GROUP BY
    u.id_user
ORDER BY
    u.id_user;