时间戳

首页 > 解决方案 > 对多个内部联接进行分组

mysql - 对多个内部联接进行分组

问题描述

我有一个可以表示为棒球卡交易应用程序的数据集。有两个表可以跟踪个人拥有的以及个人想要的卡。如果一个人拥有多于一张特定球员卡的副本，则owned_cards表格中将出现多行（此处不包括球队）。如果有个人想要添加到他们的收藏中的卡片，则会在wanted_cards表中插入一行。

owned_cards：

| owner | player     |
|-------|------------|
| Fred  | Babe Ruth  |
| Fred  | Babe Ruth  |
| Mary  | Lou Gehrig |
| Mary  | Yogi Berra |
| Mary  | Yogi Berra |

wanted_cards：

| owner | player     |
|-------|------------|
| Fred  | Lou Gehrig |
| Fred  | Yogi Berra |
| Mary  | Babe Ruth  |

我正在尝试写一份声明，将“自动匹配”寻求交易的个人。如果一个人拥有一张以上的卡片副本，则假定他们会交易其中一张。同样，如果他们只提供一张玩家卡片的副本，他们将不会提供该卡片进行交易。假定所有卡片的价值相等。

SELECT fred_wants.player AS 'Fred Wants', mary_has.player AS 'Mary Has'
FROM wanted_cards fred_wants
INNER JOIN owned_cards mary_has ON fred_wants.owner = mary_has.owner
INNER JOIN wanted_cards mary_wants ON mary_has.owner = mary_wants.owner
INNER JOIN owned_cards fred_has ON fred_has.owner = mary_wants.owner
WHERE fred_wants.owner = 'Fred'
GROUP BY mary_has.player
HAVING COUNT(mary_has.player) > 1

产生：

| Fred Wants | Mary Has   |
|------------|------------|
| Lou Gehrig | Babe Ruth  |
| Babe Ruth  | Lou Gehrig |
| Babe Ruth  | Yogi Berra |

期望的输出：

| Fred Wants | Mary Has   |
|------------|------------|
| Babe Ruth  | Yogi Berra |

Fred 想要一张 Lou Gehrig 卡，但 Mary 只有一张，所以不能交易。取消 GROUP BY 我明白为什么要返回该行，但我对如何生成这么多行感到困惑；我认为 HAVING 会减少它。

标签： mysql

解决方案

这是一个示例查询，它返回谁拥有 Fred 想要的卡片的附加功能：

select w.player as 'Fred Wants', t.owner as 'Who has extras'
from
wanted_cards as w
inner join
(
  select owner, player
  from owned_cards as o
  where o.owner <> 'Fred'
  group by owner, player
  having count(*) > 1
) as t
on (w.player = t.player)
where w.owner = 'Fred'

主要想法是选择 Fred 想要的所有卡片，然后为所有其他玩家找到所有额外内容

推荐阅读