MySql 统计某个值至少出现两次的行总数

Question

我想选择不同class_id的总数，其中至少有两个生日相同的学生。

class_id      student_id     birthday 
1                30          1994-10-01
1                23          1994-01-01
1                19          1994-02-01
1                11          1994-03-01
2                9           1994-02-01
2                43          1994-03-01
3                41          1994-06-01
3                21          1994-05-01
4                9           1992-05-22
4                20          1992-09-05

Answer 1

编写一个子查询来查找同一类中所有重复的生日。然后用 SELECT COUNT(DISTINCT class_id) 计算不同类别的数量来自该子查询。

SELECT COUNT(DISTINCT class_id) FROM (
    SELECT class_id, birthday
    FROM YourTable
    GROUP BY class_id, birthday
    HAVING COUNT(*) > 1) AS x