sql - 如何计算不同表中ID的出现次数

Question

我正在为此使用 MS Access

我正在制作一个关于 UFC 比赛的 Access 数据库。我有一个战斗时间表，其中包含每场战斗的每个战士的 ID 和战斗的获胜者。我正在尝试进行查询，列出每一位参加过战斗的战士，并列出每位战士的胜利次数。

这是我的 fightSchedule 表的一个例子

    ID  Fighter1    Fighter2    Weight Class            Date    Winner
    A   205         215         Light Heavyweight   8/14/2013   205
    B   206         212         Welterweight        8/15/2013   212
    C   207         218         Middleweight        8/14/2013   207
    D   208         209         Heavyweight         8/14/2013   209

例如，我想要一个看起来像这样的查询:

    Fighter        # of Wins
    205             1
    206             0
    207             1
    208             0
    209             1
    212             1
    215             0
    218             0

我完全不知道这样做会涉及什么。我知道如何使用计数函数，但不知道如何像我在这个例子中想要的那样使用它。

我在 W3 Schools 上找到了这个，但我不知道这是否适合使用或如何使用。

SELECT column_name, COUNT(column_name)
    FROM table_name
    GROUP BY column_name

如有任何帮助，我们将不胜感激。谢谢!

Answer 1

其实并没有那么难。您需要逐步构建查询:

1) 选择至少参加过一场战斗的所有战士，但不要计算那些参加过多次战斗的战士:

select distinct fighter1 from
(
    select fighter1 from fightSchedule 
    union select fighter2 from fightSchedule 
)

2) 为实际获胜的选手选择获胜次数:
(这基本上是您在 W3School 上找到的查询)

select winner, count(winner) as Wins
from fightSchedule
group by winner

3) 组合前两个查询:

• 我们需要第一个查询的战士列表和第二个查询的获胜列表，因此我们需要JOIN这两个查询。
• 并非每个战士都赢得了一场比赛，因此并非第一个查询中的所有战士都出现在第二个查询中。
• 这就是我们需要执行 LEFT JOIN 的原因，这意味着我们从第一个查询中选择所有行，并且只从第二个查询中选择匹配的行
  (标准 INNER JOIN 会过滤掉第一个查询中第二个查询中丢失的所有行)

在 MS Access 中，有两种方法可以进行第二个查询:

如果将前两个查询保存为 Access 中的命名查询，会更容易。
然后你可以像第三个查询中的表一样查询它们:

select fighter1 as Fighter, nz(wincount,0) as Wins
from qryFighters
    left join qryWinners
    on qryFighters.fighter1 = qryWinners.winner

(在查询名称前加上诸如 qry 之类的前缀是一种品味问题。我只是在这个示例中这样做以强调它们是查询，而不是“常规”表)

如果您不想将查询保存为命名的 Access 查询，您也可以在一个大的 SQL 语句中完成它，尽管它更复杂并且如果您没有 SQL 经验可能会让您感到困惑:

select fighter1 as Fighter, nz(wincount,0) as Wins
from
(
    select distinct fighter1 from
    (
        select fighter1 from fightSchedule 
        union select fighter2 from fightSchedule 
    )
) as fighters
left join
(
    select winner, count(winner) as wincount
    from fightSchedule
    group by winner
) as winners
on fighters.fighter1 = winners.winner