php - 一页显示多个 COUNT(*)

Question

这可能非常简单 - 但我正在撕毁我的头发。数据库正在使用 cPanel (MariaDB 10.1.28)

场景是这样的

• 1 个名为“警告”的表

• 1 列名为“WARNING_REASON”(VARCHAR)warning_reason 包含各种“原因”

• 1 列名为“WARNING_LEVEL”(VARCHAR)这包含“VERBAL”、“LEVEL 1”或“LEVEL 2”

我需要在 5 列表的 php 中输出此数据

• 警告原因
• 表中按 warning_reason 分组的总行数
• 按 warning_reason 分组的匹配“VERBAL”的行总数
• 按 warning_reason 分组的匹配“LEVEL 1”的行总数
• 按 warning_reason 分组的匹配“LEVEL 2”的行总数

Answer 1

您可以使用子查询来完成此操作:

SELECT t1.warning_reason
, (SELECT COUNT(*) FROM warnings s1 WHERE s1.warning_reason = t1.warning_reason) AS total_count
, (SELECT COUNT(*) FROM warnings s2 WHERE s2.warning_reason = t1.warning_reason AND s2.warning_level = \'VERBAL\') AS verbal_count
, (SELECT COUNT(*) FROM warnings s3 WHERE s3.warning_reason = t1.warning_reason AND s3.warning_level = \'LEVEL 1\') AS level1_count
, (SELECT COUNT(*) FROM warnings s4 WHERE s4.warning_reason = t1.warning_reason AND s4.warning_level = \'LEVEL 2\') AS level2_count
FROM warnings t1
GROUP BY t1.warning_reason