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

I'm using MS Access for this

我用的是MS Access

I'm making an access database on UFC fights. I have a fightschedule table that has the IDs of each fighter for each fight and the winner of the fights. I'm trying to make a query that lists every fighter that fought and lists how many wins each one had.

我正在建立一个UFC战斗的访问数据库。我有一个战斗时间表，每个战斗的每个战士的id和战斗的胜利者的id。我正在尝试做一个查询，列出每一个战斗过的战士，并列出每个战士赢得了多少胜利。

This is an example of my fightSchedule table

这是我的战斗时间表的一个例子

    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

So for for example I want a query that would look something like this:

例如，我想要一个类似这样的查询:

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

I don't know much about what would go into doing this at all. I know how to use the count function but no idea how to use it like I want in this example.

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

I found this on W3 Schools but I don't know if this is the right one to use or how to use it.

这是我在W3学校发现的，但我不知道这是正确的还是如何使用。

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

Any help would be appreciated. Thank you!

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

2 个解决方案

#1

It's actually not that difficult. You need to build up the query in baby steps:

其实没那么难。您需要以小步骤构建查询:

1) Select all fighters that ever fought at least one fight, but don't count those more than once that fought several fights:

1)选择所有参加过至少一场战斗的战斗人员，但不要把参加过几次战斗的人都算在内:

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

2) Select the number of wins for the fighters that actually won:
(this is basically the query that you found on W3Schools)

2)为真正获胜的战士选择获胜的次数:(这基本上是你在W3Schools上找到的查询)

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

3) Combine the first two queries:

3)合并前两个查询:

We need the list of fighters from the first query, and the list of wins from the second query, so we need to JOIN the two queries.
我们需要来自第一个查询的战士列表，以及来自第二个查询的胜利列表，因此我们需要加入这两个查询。
Not every fighter won one of his fights, so not all fighters from the first query are present in the second query.
不是每个战士都赢得了他的一场战斗，所以不是所有的战士从第一个查询出现在第二个查询中。
That's why we need to do a LEFT JOIN, meaning that we select all rows from the first query and only those that match from the second query
(the standard INNER JOIN would have filtered out all rows from the first query that are missing in the second query)
这就是我们需要执行左连接的原因，这意味着我们从第一个查询中选择所有的行，并且只选择与第二个查询匹配的行(标准的内部连接会过滤掉第二个查询中缺失的第一个查询中的所有行)

In MS Access, there are two ways how to do the second query:

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

It's easier if you save the first two queries as named queries in Access.
Then you can query them like tables in the third query:

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

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

(it's a matter of taste to prefix the query names with stuff like qry. I just did it in this example to emphasize that they're queries, not "regular" tables)

(用诸如qry之类的东西来对查询名称进行前缀是很重要的。我只是在这个例子中强调它们是查询，而不是“常规”表)

If you don't want to save the queries as named Access queries, you can do it as well all in one big SQL statement, although it's more complex and is likely to confuse you if you're not experienced in SQL:

如果您不想将查询保存为命名访问查询，那么您也可以在一个大型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

#2

Generally what you want to do is to unpivot columns into rows. There're several ways to do this in different RDBMS.
Since you haven't specified you RDBMS, I'll start with very general way - to unpivot columns with union all. I think this query will work in any RDBMS:

一般来说，您要做的是将列分解为行。在不同的RDBMS中，有几种方法可以做到这一点。由于您还没有指定RDBMS，因此我将从非常通用的方式开始——使用union all对列进行unpivot。我认为这个查询将适用于任何RDBMS:

select
   Fighter,
   sum(Wins) as Wins
from
(
  select
      Fighter1 as Fighter,
      case when Fighter1 = Winner then 1 else 0 end as Wins
  from fightSchedule
  union all
  select
      Fighter2 as Fighter,
      case when Fighter2 = Winner then 1 else 0 end as Wins
  from fightSchedule
) as a
group by Fighter

sql fiddle demo

sql小提琴演示

On the other hand, for SQL Server you can use outer apply:

另一方面，对于SQL Server，可以使用外部应用程序:

select
    C.Fighter,
    sum(C.Wins) as Wins
from fightSchedule
    outer apply (
        select Fighter1, case when Fighter1 = Winner then 1 else 0 end
        union all
        select Fighter2, case when Fighter2 = Winner then 1 else 0 end
    ) as C(Fighter, Wins)
group by C.Fighter

#1