如何编写动态SQL查询筛选仅指定用户组拥有的物品

嘿，这个需求其实很常见，要实现动态传入用户列表且精准筛选仅被这些用户共同拥有的物品（排除有其他额外拥有者的物品），我们可以用分组统计+条件校验的思路来搞定，下面给你两种可行的SQL方案，适配不同的数据库场景：

方案一：使用表变量/临时表+JOIN+NOT EXISTS #

这种方式逻辑清晰，兼容性强，适合大多数关系型数据库：

SQL示例（以SQL Server为例，表变量传递用户） #

-- 定义存储目标用户的表变量，支持动态添加用户
DECLARE @TargetUsers TABLE (user_id INT);
INSERT INTO @TargetUsers VALUES (1), (2); -- 这里可以动态传入任意数量的用户ID

SELECT o.item_id
FROM Owner o
-- 只保留目标用户拥有的物品记录
JOIN @TargetUsers tu ON o.owner_id = tu.user_id
GROUP BY o.item_id
HAVING 
    -- 条件1：该物品被所有目标用户拥有（拥有者数量等于目标用户总数）
    COUNT(DISTINCT o.owner_id) = (SELECT COUNT(*) FROM @TargetUsers)
    -- 条件2：该物品没有被非目标用户拥有
    AND NOT EXISTS (
        SELECT 1
        FROM Owner o2
        WHERE o2.item_id = o.item_id
        AND o2.owner_id NOT IN (SELECT user_id FROM @TargetUsers)
    );

适配MySQL的版本（用临时表） #

-- 创建临时表存储目标用户
CREATE TEMPORARY TABLE TargetUsers (user_id INT);
INSERT INTO TargetUsers VALUES (1), (2);

SELECT o.item_id
FROM Owner o
JOIN TargetUsers tu ON o.owner_id = tu.user_id
GROUP BY o.item_id
HAVING 
    COUNT(DISTINCT o.owner_id) = (SELECT COUNT(*) FROM TargetUsers)
    AND NOT EXISTS (
        SELECT 1
        FROM Owner o2
        WHERE o2.item_id = o.item_id
        AND o2.owner_id NOT IN (SELECT user_id FROM TargetUsers)
    );

方案二：使用GROUP BY+CASE统计校验 #

这种方式更简洁，用统计函数直接判断是否存在非目标用户：

DECLARE @TargetUsers TABLE (user_id INT);
INSERT INTO @TargetUsers VALUES (1), (2);

SELECT item_id
FROM Owner
GROUP BY item_id
HAVING 
    -- 条件1：拥有者数量等于目标用户总数
    COUNT(DISTINCT owner_id) = (SELECT COUNT(*) FROM @TargetUsers)
    -- 条件2：没有非目标用户的记录（统计非目标用户数量为0）
    AND SUM(CASE WHEN owner_id NOT IN (SELECT user_id FROM @TargetUsers) THEN 1 ELSE 0 END) = 0;

核心逻辑说明 #

不管哪种方案，都需要同时满足两个核心条件：

• 物品的拥有者数量恰好等于目标用户的总数（确保所有目标用户都拥有该物品）
• 物品没有被任何非目标用户拥有（排除像物品B这种有额外拥有者的情况）

通过表变量/临时表传递用户列表，就能实现完全动态的参数传入，不用硬编码用户ID啦～

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