找到贝利的蓝手帕

Question

想象一下，舞蹈队(不同规模)可以选择一套或多套服装，而每套服装可以有一件或多件物品。一个团队的所有成员都必须有相同的装备和每套装备的所有项目，也就是说，不完整的装备是坏的--我们想要找到那些糟糕的装备。

下表定义了两个团队:苹果和香蕉。苹果团队有3名成员，香蕉队有2名成员。苹果选择了一套只有一件物品的单件衣服，一套黄色的连衣裙，自然是一群人最喜欢的。香蕉队有两套装备:红色和蓝色；但是，贝利错过了蓝色装备的手帕。很尴尬。

让贝利摆脱麻烦吧。创建一个查询，以查找团队选择的装备中缺少的项。

感谢@Brits提供了下面的SQL：

团队和团队成员：

CREATE TABLE team (
    id   int unique,
    name text
);
INSERT INTO team (id, name)
VALUES (1, 'APPLE'),
       (2, 'BANANA');

CREATE TABLE team_member (
    id      int unique,
    name    text,
    team_id int references team (id)
);
INSERT INTO team_member (id, name, team_id)
VALUES (1, 'ADAM', 2),
       (2, 'BAILEY', 2),
       (3, 'CATE', 1),
       (4, 'DAVE', 1),
       (5, 'ERIN', 1);

服装和装备项目：

CREATE TABLE outfit (
    id   int unique,
    name text
);
INSERT INTO outfit (id, name)
VALUES (1, 'RED'),
       (2, 'YELLOW'),
       (3, 'BLUE');

CREATE TABLE outfit_item (
    id        int unique,
    name      text,
    outfit_id int references outfit (id)
);
INSERT INTO outfit_item (id, name, outfit_id)
VALUES (1, 'SHORTS', 1),
       (2, 'SHIRT', 1),
       (3, 'PANTSUIT', 2),
       (4, 'BANDANNA', 3),
       (5, 'HAT', 3);

团队成员装备项目：

CREATE TABLE member_item (
    member_id int references team_member (id),
    item_id   int references outfit_item (id)
);

INSERT INTO member_item (member_id, item_id)
VALUES (1, 1),
       (1, 2),
       (1, 4),
       (1, 5),
       (2, 1),
       (2, 2),
       (2, 5),
       (3, 3),
       (4, 3),
       (5, 3);

苹果团队的成员都有黄色的裤装，所以苹果已经准备好了。

香蕉队选择了红蓝相间的服装；遗憾的是，我没有给香蕉队穿黄色裤装--他们会杀了它，但无论如何。香蕉队的亚当和贝利有红色装备的物品，但贝利没有蓝色装备的手帕；我们不要让手帕妨碍香蕉队，所以我们需要查询才能返回：

BANANA BAILEY BLUE BANDANNAS

要查找一套服装中的物品数量：

SELECT
  o.name
, count(*) AS "number of items"
FROM
  outfit_item i
, outfit o
WHERE
  i.outfit_id = o.id
GROUP BY
  i.outfit_id
, o.name

  name  | number of items 
--------+-----------------
 RED    |               2
 BLUE   |               2
 YELLOW |               1

同样，一个团队的成员数：

SELECT
  t.name
, count(*) AS "number of members"
FROM
  team_member m
, team t
WHERE
  m.team_id = t.id
GROUP BY
  m.team_id
, t.name

  name  | number of members 
--------+-------------------
 BANANA |                 2
 APPLE  |                 3

这一切都很好，但是我们如何把这些信息结合起来，这样我们才能把贝利从这个香蕉错误中解救出来呢？

Answer 1

考虑到“如果任何成员都有装备项目，那么所有团队成员都必须拥有该装备中的所有项目”，我相信下面的内容将符合您的要求(我正在使用CTE来计算与每个团队相关的服装)。

with teamoutfit as (
  -- If any mmber of a team has any part of an outfit then that team is linked to that outfit 
 select distinct te.id as team_id, oft.id as outfit_id from
  team te 
  inner join team_member tm on tm.team_id = te.id
  inner join member_item mi on mi.member_id = tm.id
  inner join outfit_item oi on oi.id = mi.item_id
  inner join outfit oft on oft.id = oi.outfit_id
) 
select te.name as team, tm.name as member, of.name as outfit, oi.name as item from 
team te 
inner join team_member tm on tm.team_id = te.id
inner join teamoutfit tof on tof.team_id = tm.team_id
inner join outfit of on of.id = tof.outfit_id
inner join outfit_item oi on tof.outfit_id = oi.outfit_id
left join member_item mi on oi.id = mi.item_id and tm.id = mi.member_id
where 
mi.member_id is null

输出：

|   team | member | outfit |     item |
|--------|--------|--------|----------|
| BANANA | BAILEY |   BLUE | BANDANNA |

我设置了一个SQL Fiddle，您可以用它来玩这个游戏(如果我误解了您想要做的事情，也许可以澄清您的问题)。

Answer 2

确认在postgres上工作，虽然不是很漂亮，而且85%的人确信这可以简化。另外，我对服装和outfit_item表之间的关系有些困惑。在你布置桌子的方式中，当你加入outfit_item (outfit.id = outfit_item.outfit_id)时，你的桌子、手帕和帽子总是蓝色的，裤装总是黄色的，短裤/衬衫总是红色的。测试数据和查询就是在这样的理解下编写的。

    select distinct 
    tm.name as member_name,
    t.name as team_name,
    o.name as outfit_color, 
    oi.name as outfit_item
    from team_member tm
    join team t 
        on t.id = tm.team_id
    join member_items mi 
        on mi.member_id = tm.id
    join(
        with outfit_item_count as
            (SELECT outfit_id, count(*) AS items 
            FROM outfit_items 
            GROUP BY 1)
        select mi.member_id, oi.outfit_id, oic.items as items_needed, count(mi.item_id) as member_item_count 
        from member_items mi
        join outfit_items oi on oi.id = mi.item_id
        join outfit_item_count oic on oic.outfit_id = oi.outfit_id
        group by 1,2,3)z 
        on z.member_id = tm.id
    join outfit_items oi 
        on oi.outfit_id = z.outfit_id
    join outfit o 
        on o.id = oi.outfit_id
    where z.items_needed > z.member_item_count
    and oi.id not in (select item_id from member_items)