集团通过查询SQL

Question

我想指望C和M 我开始与此查询的警报数和用户类型类型=处理警报的数量它给了我主要和关键警报数量正确的，但我“M卡住处理

SELECT 
    TYPE, 
    user_name, 
    COUNT(type_alertes) AS nb, 
    id_user, 
    SUM(CASE WHEN TYPE ='C' THEN 1 ELSE 0 END) AS critique, 
    SUM(CASE WHEN TYPE ='M' THEN 1 ELSE 0 END) AS majeur 
FROM stat_alert 
LEFT OUTER JOIN user_qdf ON user_qdf.`id` = id_user 
WHERE 
    user_qdf.`id`=id_user 
    AND TYPE IN ('M', 'C') 
GROUP BY id_user 

警报的人数谁可以帮我

Answer 1

如果你想所有警报的数量，从数M的的C和编号，然后

SELECT 
    user_name, 
    COUNT(type_alertes) AS nb, 
    id_user, 
    SUM(CASE WHEN TYPE ='C' THEN 1 ELSE 0 END) AS critique, 
    SUM(CASE WHEN TYPE ='M' THEN 1 ELSE 0 END) AS majeur 
FROM stat_alert 
JOIN user_qdf ON user_qdf.`id` = id_user 
GROUP BY id_user, user_name 

应该这样做。 我假设user_name来自user_qdf表，其他来自stat_alert并且id_user不为null。

编辑：对于alertes

的总和

SELECT 
    user_name, 
    COUNT(type_alertes) AS nb, 
    id_user, 
    SUM(CASE WHEN TYPE ='C' THEN 1 ELSE 0 END) AS critique, 
    SUM(CASE WHEN TYPE ='M' THEN 1 ELSE 0 END) AS majeur 
    SUM(CASE WHEN TYPE ='M' OR TYPE = 'C' THEN 1 ELSE 0 END) AS both_c_and_m 
FROM stat_alert 
JOIN user_qdf ON user_qdf.`id` = id_user 
GROUP BY id_user, user_name 

EDIT2：

SELECT 
    user_name, 
    COUNT(type_alertes) AS nb, 
    SUM(CASE WHEN TYPE ='C' THEN type_alertes ELSE 0 END) AS critique_alerts, 
    SUM(CASE WHEN TYPE ='M' THEN type_alertes ELSE 0 END) AS majeur_alertes 
    id_user, 
    SUM(CASE WHEN TYPE ='C' THEN 1 ELSE 0 END) AS critique, 
    SUM(CASE WHEN TYPE ='M' THEN 1 ELSE 0 END) AS majeur 
FROM stat_alert 
JOIN user_qdf ON user_qdf.`id` = id_user 
WHERE TYPE in ('C','M') 
GROUP BY id_user, user_name 

是你想要的吗？