MariaDB 5.5统计指定用户完成收藏集数量的SQL查询问题

搞定这个收藏集完成次数统计的问题不难，咱们结合你的MariaDB 5.5环境和业务需求来一步步解决！首先明确核心逻辑：用户必须集齐某个收藏集的所有奖品才算完成一次，要是重复集齐完整的奖品组合（比如同一套奖品拿了两次），次数也要跟着累加。

先看你的样本数据：用户1有45、46、47三个奖品，正好对应收藏集1的全部奖品，所以完成1次；用户2只有66，缺了收藏集2的65，所以不算完成。

因为MariaDB 5.5不支持WITH公共表表达式（CTE是10.2版本才新增的），所以咱们用子查询来写兼容的SQL：

SELECT 
    upc.consumer_id,
    upc.set_id AS completed_set,
    MIN(upc.prize_count) AS count
FROM (
    -- 统计指定用户每个收藏集下各奖品的获得数量
    SELECT 
        ap.consumer_id,
        csm.set_id,
        csm.prize_id,
        COUNT(ap.prize_id) AS prize_count
    FROM awarded_prizes ap
    JOIN collectable_set_members csm ON ap.prize_id = csm.prize_id
    WHERE ap.consumer_id = 1 -- 替换成你要查询的用户ID，去掉则查所有用户
    GROUP BY ap.consumer_id, csm.set_id, csm.prize_id
) upc
JOIN (
    -- 计算每个收藏集需要的不同奖品总数
    SELECT set_id, COUNT(DISTINCT prize_id) AS total_prizes
    FROM collectable_set_members
    GROUP BY set_id
) spc ON upc.set_id = spc.set_id
GROUP BY upc.consumer_id, upc.set_id
HAVING 
    -- 验证用户是否集齐了该收藏集的所有奖品
    COUNT(upc.prize_id) = spc.total_prizes
    -- 只保留实际完成过的记录
    AND MIN(upc.prize_count) > 0;

代码解释： #

1. upc子查询：先统计目标用户在每个收藏集下，每个具体奖品的获得次数。比如用户1的结果就是每个奖品各1次。
2. spc子查询：提前算出每个收藏集需要多少个不同的奖品，比如收藏集1需要3个，收藏集2需要2个。
3. 关联与分组：把两个子查询关联后，按用户和收藏集分组。HAVING条件确保用户拥有该集的所有奖品（对比奖品数量和收藏集总需求），然后取该集所有奖品获得次数的最小值作为完成次数——因为要集齐一套，每个奖品至少要有1个，要是每个奖品都有2个，就能完成2次。

用你的样本数据运行这段SQL，会得到你想要的结果：

+-------------+---------------+-------+
| consumer_id | completed_set | count |
+-------------+---------------+-------+
|           1 |             1 |     1 |
+-------------+---------------+-------+

如果用户1后来又拿到了45、46、47各一次，那最终的count会变成2，完美支持多次完成的场景。要是想查询所有用户的完成情况，只需要去掉WHERE ap.consumer_id = 1这一行就行。

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