查询同一Plate_Id分组下所有Prod_Id共有的Location

我来帮你搞定这个需求！你要找的是每个plate_id下，所有prod_id分组都包含的location，之前用最小prod_id分组做内连接的思路确实有缺陷——它只能验证某个location在部分分组存在，没法确保覆盖所有分组。

核心思路 #

要实现这个需求，我们可以分两步走：

• 先统计每个plate_id下一共有多少个不同的prod_id分组（记为total_prod_groups）
• 再统计每个plate_id + location组合，对应出现过的不同prod_id数量（记为prod_count）
• 当prod_count等于total_prod_groups时，说明这个location在当前plate_id的所有prod_id分组里都存在，就是我们要的结果

具体SQL实现 #

首先用你提供的测试数据创建表：

create table #AllTab ( 
    plate_id int, 
    prod_id int, 
    location int 
) 
insert into #AllTab values 
(100,10, 1), (100,10, 2), (100,10, 3), (100,10, 4), 
(100,20, 1), (100,20, 2), (100,20, 3), (100,20, 4), 
(100,20, 5), (100,20, 6), (100,20, 9), 
(100,30, 1), (100,30, 2), (100,30, 9), 
(100,40, 1), (100,40, 2), (100,40, 12), (100,40, 14), 
(100,40, 1), (100,40, 2), (100,40, 25), (100,40, 30), 
----------------- 
(200,10, 1), (200,10, 2), (200,10, 3), (200,10, 4), 
(200,20, 1), (200,20, 2), (200,20, 3), (200,20, 4), 
(200,20, 5), (200,20, 6), (200,20, 7), 
(200,30, 3), (200,30, 4), (200,30, 9), 
----------------- 
(300,10, 1), (300,10, 2), (300,10, 3), (300,10, 5), 
(300,20, 1), (300,20, 2), (300,20, 3), (300,20, 4), 
(300,20, 5), (300,20, 6), (300,20, 7), (300,20, 9), 
(300,30, 1), (300,30, 2), (300,30, 5)

然后执行查询语句：

WITH PlateProdCounts AS (
    -- 统计每个plate_id的不同prod_id总数
    SELECT plate_id, COUNT(DISTINCT prod_id) AS total_prod_groups
    FROM #AllTab
    GROUP BY plate_id
),
LocationProdCounts AS (
    -- 统计每个plate_id+location的不同prod_id数量
    SELECT plate_id, location, COUNT(DISTINCT prod_id) AS prod_count
    FROM #AllTab
    GROUP BY plate_id, location
)
-- 筛选出prod_count等于total_prod_groups的记录
SELECT lpc.plate_id, lpc.location
FROM LocationProdCounts lpc
JOIN PlateProdCounts ppc ON lpc.plate_id = ppc.plate_id
WHERE lpc.prod_count = ppc.total_prod_groups
ORDER BY lpc.plate_id, lpc.location;

结果验证 #

执行后会得到你期望的结果：

plate_id location 
100      1 
100      2 
200      3 
200      4 
300      1 
300      2 
300      5 

为什么之前的方法不行？ #

你之前用最小prod_id的子表做内连接，只能验证这个location在部分其他分组存在，但没法确保覆盖所有分组。比如plate_id=100的最小prod_id=30里的location=9，它在prod_id=10、20、30里存在，但在prod_id=40里没有，所以不该出现在结果里，但之前的查询会错误地保留它——而我们的新方法通过计数对比，完美规避了这个问题。

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