HOWTO · MySQL

MySQL 中的 COUNT IF 方法

本教程演示如何在 MySQL 数据库中使用 COUNT IF。

本页内容

若要在 MySQL 中统计符合条件的行,可将 COUNT() 与 IF() 组合使用:匹配的行返回非 NULL 值,其他行都返回 NULL。COUNT() 只统计非 NULL 值,因此这种写法会统计匹配行。“COUNT IF”是这种查询模式的常见称呼,并非单独的 MySQL 函数或方法。

同样的思路也可用于统计匹配行中的不同值;SUM(CASE) 则是计算条件总数的另一种方式。下面的示例说明如何选择并调整这些写法。

MySQL 中条件计数的工作原理

通用表达式为 COUNT(IF(condition, 1, NULL))。IF() 会逐行判断条件:条件为真时返回 1,否则返回 NULL。聚合函数 COUNT() 会忽略 NULL 结果,只统计返回的 1,从而得到符合条件的行数。

例如,COUNT(IF(grade > 80, 1, NULL)) 会统计 grade 大于 80 的行。条件可以是列比较,也可以用 AND 或 OR 组合多个谓词。如果条件的结果为 NULL,IF() 会采用假值分支,因此该行不会计入结果。

这与统计所有行的 COUNT(*) 不同,也与统计某列非 NULL 行数的 COUNT(column) 不同。使用条件计数时,COUNT() 中的表达式决定哪些行会计入结果。可在 AS 后使用描述性别名,让结果列更容易辨认。

统计符合条件的行

假设 students 表中每位学生占一行,并有一个 grade 列。若要统计成绩高于 80 的学生,可使用 SELECT COUNT(IF(grade > 80, 1, NULL)) AS high_scorers FROM students;。若要增加一项要求,可用 AND 组合条件,例如 COUNT(IF(grade > 80 AND grade_level = 10, 1, NULL))。一行要计入结果,两个谓词都必须为真。

下面是一个小型示例数据集:

学生 成绩 年级
Alex 85 10
Blair 75 10
Casey 92 11
Drew 88 10

第一个查询会统计出三行成绩高于 80。组合条件会统计出两名成绩高于 80 且年级为 10 的学生。以上结果是根据表中显示的数据手动计算的;这里没有执行 SQL,因为此示例没有 MySQL 服务器测试环境。

运行下面的 Python 示例,可以了解相同的行计数逻辑。它使用上面的示例值,但不会执行 MySQL 查询。

本节中的代码以及显示的标签或输出仍为英文。

students = [
    {"grade": 85, "grade_level": 10},
    {"grade": 75, "grade_level": 10},
    {"grade": 92, "grade_level": 11},
    {"grade": 88, "grade_level": 10},
]
high_scorers = sum(student["grade"] > 80 for student in students)
high_scorers_in_grade_10 = sum(
    student["grade"] > 80 and student["grade_level"] == 10
    for student in students
)
print(f"high_scorers: {high_scorers}")
print(f"high_scorers_in_grade_10: {high_scorers_in_grade_10}")
high_scorers: 3
high_scorers_in_grade_10: 2

统计符合条件的不同值

若要统计符合条件的行中的唯一 ID,可将要去重的值放在真值分支中:COUNT(DISTINCT IF(condition, expression, NULL))。对于匹配的行,IF() 会返回 expression;其他行则返回 NULL。COUNT(DISTINCT ...) 会忽略 NULL,并且每个不同的非 NULL 结果只统计一次。因此,重复的 ID 只计一次,而匹配行的表达式若为 NULL,也不会计入结果。

例如,假设有一个包含 stu_id 和 stu_firstName 列的 student_details 表,SELECT COUNT(DISTINCT IF(stu_firstName LIKE '%reet', stu_id, NULL)) AS count_student_ids FROM student_details; 会统计名字以 reet 结尾的不同学生 ID。LIKE 中的 % 可匹配 reet 前面的任意字符序列。别名 count_student_ids 用于命名唯一的结果列。由于聚合函数已经返回一行结果,外层的 SELECT DISTINCT 没有必要。

下面是该查询的一组示例数据:

stu_id stu_firstName
101 Preet
102 Harpreet
101 Simreet
103 Maya
104 Suneet

查询结果为 2:ID 101 和 102 的名字符合条件。ID 101 出现两次,但只计一次;ID 104(Suneet)的名字不以 reet 结尾。以下是可选择复制的结果:

count_student_ids
2

如果要统计符合条件的唯一值,例如符合条件的订单中有多少个不同客户 ID,就使用这种写法。如果每个符合条件的行都应单独计数,即使其他值重复,也应使用普通的 COUNT(IF(...))。

使用 SUM(CASE) 进行条件计数

如果希望为每一行返回明确的数值贡献,SUM(CASE) 可以作为替代方案。对于学生示例,SUM(CASE WHEN grade > 80 THEN 1 ELSE 0 END) 会为每个高分加 1,为其他行加 0。根据显示的四行数据,合计为 3。若要同时检查两个条件,可将它们都写在 WHEN 谓词中,例如 SUM(CASE WHEN grade > 80 AND grade_level = 10 THEN 1 ELSE 0 END);示例结果为 2。

当基于 NULL 的规则简洁明了时,可选择 COUNT(IF(...))。如果将每行贡献设为 0 或 1 后更容易扩展计算,或更便于与其他条件总数一起阅读,则可选择 SUM(CASE)。两种写法都表示条件聚合;“COUNT IF”本身并非单独的函数。应明确写出条件、计数或求和的值以及输出别名,让读者能够核对每个结果的含义。