SQL：SELECT COUNT涉及几个表 [英] SQL: SELECT COUNT involving a couple of tables

问题描述

我有一个小型的MS Access数据库包含四个表，并努力与一个SELECT COUNT查询，涉及所有表。非常感谢任何帮助。

I have a small MS Access database containing four tables and are struggeling with a SELECT COUNT query which involves all tables. Any help will be greatly appreciated.

数据库概述：

TABLE       COLUMNS

Person         Person_ID |....
Games          Game_ID | Name |....
Played_Games   Played_Games_ID | Game_ID |....
Participation  Played_Games_ID | Person_ID |...

列出所有游戏以及每个游戏总共玩多少次使用：

To list out all games and how many times each game is played in total I use:

SELECT DISTINCT
Games.Name,
(SELECT COUNT(*) FROM Played_Games WHERE Played_Games.Game_ID = Games.Game_ID) AS TimesPlayed
FROM Games
ORDER BY Games.Name

这很棒，但我也想列出每个游戏是由某一群体的成员玩多少次

This works great, but I would also like to list how many times each game is played by members of a certain group of persons

我已经取得了一些进展下面的代码（第4行），但我仍然困扰一个小问题：

I have made some progress with the code below (line 4), but I'm still stuck with one small problem:

在最内侧括号的后面的Games.Game_ID的引用没有链接到当前Game_ID，因为它在上面的代码。它现在似乎是未定义的。当我在MS Access查询设计器中运行此测试时，它要求输入一个值Games.Game_ID。如果我然后键入一些随机Game_ID代码运行完美，但正如你可能已经知道，TimesPlayedByGroupMembers的值将是相同的记录集中的每一行，只有真正的一个特定的Game_ID，我键入

The reference to Games.Game_ID at the back of the innermost brackets is not linked to the current Game_ID anymore, as it is in the code above. It now seems to be undefined. When I run this within MS Access Query Designer to test, it asks me to type in a value for Games.Game_ID. If I then type in some random Game_ID the code runs "perfectly", but as you probably already figured out, the value of TimesPlayedByGroupMembers will then be the same for every row in the recordset and only true for that one particular Game_ID that I typed in

SELECT DISTINCT
Games.Name,
(SELECT COUNT(*) FROM Played_Games WHERE Played_Games.Game_ID = Games.Game_ID) AS TimesPlayed,
(SELECT COUNT(*) FROM (SELECT DISTINCT Played_Games_ID FROM Participation WHERE Played_Games_ID IN (SELECT Played_Games_ID FROM Played_Games WHERE Game_ID = Games.Game_ID) AND Person_ID IN (26, 27, 28))) AS TimesPlayedByGroupMembers
FROM Games
ORDER BY Games.Name

尝试解释我的代码做什么和/或应该从内到外做

I will try to explain what my code does and/or is supposed to do from the inside out

最内侧的括号列出了涉及当前游戏的Played_Games_ID，但这不是因为Games.Game_ID没有链接到当前的Game_ID

The innermost brackets lists out Played_Games_ID's that involves the current game, but this doesn't work because Games.Game_ID is not linked to the current Game_ID

中间括号（包括上面的一个）列出了Played_Games_ID，涉及当前游戏和一个或多个选择的人。这里列出的Person_ID只是一个例子，代码的这一部分是工作的

The middle brackets (including the one above) lists out Played_Games_ID's that involves the current game and one or more of the selected persons. The Person_ID's listed here are just an example, and this part of the code is working

最外面的括号（包括所有上面的）计算每个游戏由一个或更多的选择的人

The outermost brackets (including all above) counts how many times each game is played by one or more of the selected persons

我真的坚持这一点，所以任何帮助将非常感谢

I'm really stuck with this, so any help will be greatly appreciated

感谢您的时间

推荐答案

我的访问语法有些生锈，我不是100％认为以下是工作。

My Access syntax is somewhat rusty, and I am not 100% sure of your table structure but I think the below is will work.

SELECT  Games.Name, 
        TimesPlayed,
        TimesPlayedByGroupMembers
FROM    Games
        INNER JOIN 
        (   SELECT  Game_ID, 
                    COUNT(*) AS TimesPlayed,
                    SUM(IIF(ISNULL(Participation.Played_Games_ID),0,1)) AS TimesPlayedByGroupMembers
            FROM    Played_Games 
                    LEFT JOIN 
                    (   SELECT  Played_games_ID 
                        FROM    Participation 
                        WHERE   Person_ID IN (1, 2) 
                        GROUP BY Played_games_ID 
                    ) AS Participation 
                        ON Participation.Played_Games_ID = Played_Games.Played_Games_ID
            GROUP BY Game_ID
        ) AS Played_Games
            ON Played_Games.Game_ID = Games.Game_ID
ORDER BY Games.Name

ADDENDUM：

被显示为0使用以下：

SELECT  Games.Name, 
        IIF(ISNULL(TimesPlayed),0,TimesPlayed) AS TimesPlayed,
        IIF(ISNULL(TimesPlayedByGroupMembers),0,TimesPlayedByGroupMembers) AS TimesPlayedByGroupMembers
FROM    Games
        LEFT JOIN 
        (   SELECT  Game_ID, 
                    COUNT(*) AS TimesPlayed,
                    SUM(IIF(ISNULL(Participation.Played_Games_ID),0,1)) AS TimesPlayedByGroupMembers
            FROM    Played_Games 
                    LEFT JOIN 
                    (   SELECT  Played_games_ID 
                        FROM    Participation 
                        WHERE   Person_ID IN (1, 2) 
                        GROUP BY Played_games_ID 
                    ) AS Participation 
                        ON Participation.Played_Games_ID = Played_Games.Played_Games_ID
            GROUP BY Game_ID
        ) AS Played_Games
            ON Played_Games.Game_ID = Games.Game_ID
ORDER BY Games.Name

这篇关于SQL：SELECT COUNT涉及几个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！