SQL:SELECT MAX(Date) 涉及几个表 [英] SQL: SELECT MAX(Date) involving a couple of tables

问题描述

我有一个包含四个表的小型 MS Access 数据库，并且正在努力处理涉及所有表的 MAX(Date) 查询.任何帮助将不胜感激.

I have a small MS Access database containing four tables and are struggeling with a MAX(Date) 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 | Date |....
Participation  Played_Games_ID | Person_ID |...

列出所有游戏以及我使用的任何人上次玩这些游戏的日期:

To list out all games and what date each of them were last played by any person I use:

SELECT DISTINCT Games.Name,
(SELECT MAX(Date) FROM Played_Games WHERE Played_Games.Game_ID = Games.Game_ID) AS Date_Last_Played
FROM Games
ORDER BY Games.Name

这很好用，但我还想列出特定人群的成员上次播放它们中的每一个的日期.对此的任何帮助将不胜感激.

This works great, but I would also like to list what date each of them were last played by members of a certain group of persons. Any help with that will be greatly appreciated.

我问了一个有点类似的问题 几天前，但后来我的目标是列出所有游戏，每场比赛总共玩了多少次，以及每场比赛由特定人群的成员玩了多少次(第 1 和第 2 个人是只是一个例子).GarethD 通过下面的代码帮助我解决了这个问题.我试图改变这段代码来解决我的新任务，因为我猜这个逻辑/结构的大部分都可以重用，但到目前为止我自己还没有成功:-(

I asked a somewhat similar question some days ago, but then my goal was to list out all games, how many times each game is played in total and how many times each games is played by members of a certain group of persons (Persons 1 and 2 are just an example). GarethD helped me out with that one just perfect by the code below. I've tried to alter this code to solve my new task, cause I guess much of this logic/structure can be reused, but so far I've not been successful on my own :-(

SELECT  Games.Name,
        IIF(ISNULL(TimesPlayed),0,TimesPlayed) AS Times_Played,
        IIF(ISNULL(TimesPlayedByGroupMembers),0,TimesPlayedByGroupMembers) AS Times_Played_By_Group_Members
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

任何帮助将不胜感激.感谢您的时间！

Any help will be greatly appreciated. Thanks for your time!

推荐答案

我刚刚在我发布的最后一个答案中添加了几行，以显示上次播放的日期，以及小组成员上次播放的日期，next到播放次数.希望这就是您所追求的.

I've just added in a couple of lines to the last answer I posted to show the date last played, and the date last played by group members, next to the number of times played. Hope this is what you are after.

SELECT  Games.Name,
        IIF(ISNULL(TimesPlayed),0,TimesPlayed) AS Times_Played,
        IIF(ISNULL(TimesPlayedByGroupMembers),0,TimesPlayedByGroupMembers) AS Times_Played_By_Group_Members,
        LastPlayed,
        LastPlayedByGroupMembers
FROM    Games
        LEFT JOIN
        (   SELECT  Game_ID,
                    COUNT(*) AS TimesPlayed,
                    SUM(IIF(ISNULL(Participation.Played_Games_ID),0,1)) AS TimesPlayedByGroupMembers,
                    MAX(Played_Games.[Date]) AS LastPlayed,
                    MAX(IIF(ISNULL(Participation.Played_Games_ID),NULL,Played_Games.[Date])) AS LastPlayedByGroupMembers
            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 MAX(Date) 涉及几个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！