MySQL 5.0：筛选certificate_id唯一值数量等于required_certificates的行

嘿，我来帮你搞定这个查询需求！首先理清楚你的核心诉求：要找出那些opportunity分组下，唯一的certificate_id数量刚好等于该组required_certificates值的所有行，而且因为main_user_id有重复，你担心GROUP BY用不了——其实没问题，我们换个分组维度就行。

在MySQL 5.0里，可以用下面的方法实现：

方法1：关联子查询保留所有符合条件的行 #

SELECT o.*
FROM opportunity o
INNER JOIN (
    -- 先计算每个opportunity的唯一证书数量，筛选符合要求的组
    SELECT 
        opportunity,
        required_certificates,
        COUNT(DISTINCT certificate_id) AS unique_cert_count
    FROM opportunity
    GROUP BY opportunity, required_certificates
    HAVING unique_cert_count = required_certificates
) AS valid_ops ON o.opportunity = valid_ops.opportunity;

简单解释下： #

1. 子查询部分：我们按opportunity和required_certificates分组（从你的示例数据看，同一个opportunity的required_certificates值是一致的，这样分组没问题），用COUNT(DISTINCT certificate_id)算出该组里不同证书的数量，然后只留下数量等于required_certificates的分组。
2. 主查询：把原表和子查询的结果关联起来，这样就能拿到所有符合条件的opportunity对应的每一行数据了。

如果只需要每个符合条件的opportunity的汇总信息（不需要所有明细行），直接用子查询的结果就行：

SELECT 
    opportunity,
    required_certificates,
    COUNT(DISTINCT certificate_id) AS unique_cert_count
FROM opportunity
GROUP BY opportunity, required_certificates
HAVING COUNT(DISTINCT certificate_id) = required_certificates;

关于你提到的GROUP BY问题 #

你说main_user_id重复所以无法用GROUP BY，其实这里的分组逻辑根本不需要碰main_user_id呀！我们要统计的是每个opportunity下的证书情况，所以分组维度是opportunity和required_certificates，和main_user_id重复不重复没关系～

用你的示例数据测试的话，这个查询会保留：

• opportunity 1的所有6行（唯一证书数是2，等于required_certificates的2）
• opportunity 2的所有5行（唯一证书数是2，等于required_certificates的2）
• opportunity 3的所有4行（唯一证书数是1，等于required_certificates的1）
  完全符合你的需求。

内容的提问来源于stack exchange，提问作者Varin