mysql - 尽管数据库中有多行，但计数仅显示一行

Question

这是我的 SQL 查询:

SELECT 
    COUNT(CASE WHEN  `urgency`='1' THEN 1 END) AS verylow,
    COUNT(CASE WHEN  `urgency`='2' THEN 1 END) AS low,
    COUNT(CASE WHEN  `urgency`='3' THEN 1 END) AS standard,
    COUNT(CASE WHEN  `urgency`='4' THEN 1 END) AS high,
    COUNT(CASE WHEN  `urgency`='5' THEN 1 END) AS critical,
    tbl_users.userName
FROM 
    notes, tbl_users 
WHERE 
    notes.responsible = tbl_users.userID
    AND project_id = '4413' 
    AND (notes.status = 'Ongoing' OR notes.status = 'Not started')

输出为:

verylow   low   standard   high   critical    userName
5          1       2         1        1        Nick

但是这是错误的，因为我在数据库中有多个用户分配了任务。它在我的数据库中看起来像这样:

urgency userName
3       Nick
5       Nick
4       Nick
3       James
1       James
1       Nick
2       Nick
1       James
1       Nick
1       Nick

知道为什么它不计算其他用户的紧急程度以及他有多少种不同的紧急程度吗？

Answer 1

你所做的并不完全正确。如果你打开 MySQL 模式 ONLY_FULL_GROUP_BY ，您会收到警告，因为您选择的列不在 GROUP BY 子句中，而没有应用聚合函数。所以你需要一个 GROUP BY条款。

整个查询应该如下所示:

SELECT 
    COUNT(CASE WHEN  `urgency`='1' THEN 1 END) AS verylow,
    COUNT(CASE WHEN  `urgency`='2' THEN 1 END) AS low,
    COUNT(CASE WHEN  `urgency`='3' THEN 1 END) AS standard,
    COUNT(CASE WHEN  `urgency`='4' THEN 1 END) AS high,
    COUNT(CASE WHEN  `urgency`='5' THEN 1 END) AS critical,
    tbl_users.userName
FROM 
    notes, tbl_users 
WHERE 
    notes.responsible = tbl_users.userID
    AND project_id = '4413' 
    AND (notes.status = 'Ongoing' OR notes.status = 'Not started')
GROUP BY tbl_users.userName;