postgresql - SQL-在同一表中查找一个属性与另一个属性的多次出现

考虑下表

Inventory(storeid, itemid, qty)
Items(itemid, description, size, color)

这是我的任务：检索满足以下条件的存储的id：对于其库存中保存的每个项目描述，存储以该描述的所有可能大小保存相应的项目id。
这就是响应的外观：

3667
3706
3742
3842

我在哪里：

with s as (
    select *
    from inventory
    inner join items using (itemID)
),
m as (
    select count(distinct size), description
    from items
    group by description
),
sizes as (
    select distinct size
    from items
)
select distinct s1.storeID
from s s1
    inner join m m1
    on s1.description = m1.description
    group by s1.storeID;

这只会返回storeid，其中包含与任何描述匹配的项……即每个storeid。找不到方法获取描述并确保它具有所有三种大小（小、中、大）。
http://sqlfiddle.com/#!2/2a743e

最佳答案

不是说三种尺寸，而是所有可能的尺寸，我喜欢数组，所以：

WITH sizes AS (
    SELECT description, array_agg(DISTINCT size) AS sizes
    FROM items
    GROUP BY description
)
,store_items AS(
    SELECT s.storeID, it.description, array_agg(DISTINCT it.size) AS sizes
    FROM stores AS s
      JOIN inventory AS i
        ON s.storeID = i.storeID
      JOIN items AS it
        ON i.itemID = it.itemID
     GROUP BY s.storeID, it.description
)
SELECT s.storeID
FROM stores AS s
WHERE s.storeID NOT IN(
  SELECT storeID
  FROM store_items AS si
  JOIN sizes z
    ON z.description = si.description
    AND si.sizes<>z.sizes)

fiddle