编写SQL查询找出包含user2全部标记及对应数量的用户

现有ORDERS表，包含user、mark和mark_cnt列。需要编写SQL查询，找出满足以下条件的用户：

• 覆盖user2的所有标记类型
• 每个标记类型的数量不低于user2的对应数量

user2的标记情况为：1个m1、2个m2、1个m4。各用户标记明细：

• U1：m1、m2、m2、m3、m4、m4、m4
• U2：m1、m2、m2、m4
• U3：m1、m2、m2、m2、m3、m3、m3、m3、m3、m3、m3、m4、m4、m4、m5
• U4：m1、m2、m4

你尝试的代码及问题 #

你写的代码存在逻辑漏洞，原代码如下：

select user
from orders
where mark in  (
               select mark from orders where user='u2'
               )
group by user
having count(mark) = (select count(mark) from order where user='u2')

问题点：

1. 仅统计了符合mark范围的总数量，没有按单个标记类型校验数量，比如U4的总数量和user2相同，但m2数量不足，却会被错误筛选出来
2. 表名拼写错误：order应该是orders

正确SQL写法 #

思路 #

先统计user2每个标记的需求数量，再关联其他用户的标记数据，校验两个核心条件：

• 用户拥有user2的所有标记类型
• 每个标记的数量都不低于需求值

针对mark_cnt列存储单条记录数量的场景

WITH user2_requirement AS (
    SELECT mark, SUM(mark_cnt) AS required_count
    FROM orders
    WHERE user = 'u2'
    GROUP BY mark
)
SELECT o.user
FROM orders o
JOIN user2_requirement ur ON o.mark = ur.mark
GROUP BY o.user
HAVING 
    -- 确保每个标记的数量都达标
    SUM(CASE WHEN SUM(o.mark_cnt) >= ur.required_count THEN 1 ELSE 0 END) = (SELECT COUNT(*) FROM user2_requirement)
    -- 确保覆盖所有标记类型
    AND COUNT(DISTINCT o.mark) = (SELECT COUNT(*) FROM user2_requirement);

针对每条记录代表单个标记（即mark_cnt=1）的场景

如果你的表中每条记录对应一个标记（比如用户给出的示例明细），可以简化为：

WITH user2_requirement AS (
    SELECT mark, COUNT(*) AS required_count
    FROM orders
    WHERE user = 'u2'
    GROUP BY mark
)
SELECT o.user
FROM orders o
JOIN user2_requirement ur ON o.mark = ur.mark
GROUP BY o.user, ur.mark, ur.required_count
HAVING COUNT(o.mark) >= ur.required_count
-- 再次分组确保覆盖所有标记类型
GROUP BY o.user
HAVING COUNT(DISTINCT o.mark) = (SELECT COUNT(*) FROM user2_requirement);

结果说明 #

上述查询会返回U1和U3，符合预期：

• U1的m1、m2、m4数量均满足要求，且覆盖所有标记
• U3的各标记数量都超过user2的需求，符合条件
• U4的m2数量仅1个，不满足user2的2个需求，会被排除

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