时间戳

首页 > 解决方案 > MS Access SQL 运行总数

sql - MS Access SQL 运行总数

问题描述

我摸不着头脑，因为我不知道如何在 MS Access 中创建运行总和，我尝试了 IIF 语句、DSUM 语句和子查询（这是我的首选）

很简单，我有一个足球队的名单，他们的比赛日期和他在每场比赛中收集的积分，我想创建一个运行总分。

我的查询没有按应有的方式计算运行总和，它只是在计算当前游戏。

以下是我尝试的代码，有人可以帮忙吗？

SELECT Team, FORMAT(mDate, "yyyy-mm-dd") AS Match_Date, Pts, 
SUM(IIF(FORMAT(mDate, "yyyy-mm-dd") <= FORMAT(mDate, "yyyy-mm-dd"),Pts)) AS Total_Pts
FROM 
(SELECT * FROM
(SELECT home_team AS Team, match_date AS mDate, IIF(home_goals > away_goals,3,IIF(home_goals = away_goals,1,0)) AS Pts FROM A WHERE league = "Premiership" AND season = "2016 - 2017" AND home_team = "Arsenal"  
UNION  
SELECT away_team AS Team, match_date AS mDate, IIF(home_goals < away_goals,3,IIF(home_goals = away_goals,1,0)) AS Pts FROM A WHERE league = "Premiership" AND season = "2016 - 2017" AND away_team = "Arsenal")  AS Filter ORDER BY 2 DESC)  AS Test
GROUP BY Team, FORMAT(mDate, "yyyy-mm-dd"), Pts
ORDER BY 2 DESC;

标签： sqlms-access

解决方案

首先：不要将日期格式化为字符串并将这些字符串作为日期进行比较。

然后：为了能够将UNION查询连接到自身，必须编写两次查询，每次都有自己的别名。其中一个查询将用于当前游戏记录，另一个用于构建积分的运行总和。Team连接两边的 必须相等，“mDate运行”查询的 必须小于或等于mDate当前游戏的 。由于第一部分的UNION记录与第二部分的记录不同，您可以使用UNION ALL加快查询速度：

SELECT Games.Team, Games.mDate, Games.Pts, SUM(Runnings.Pts) AS Total_Pts
FROM
(
    SELECT home_team AS Team, match_date AS mDate, IIF(home_goals > away_goals,3,IIF(home_goals = away_goals,1,0)) AS Pts
    FROM A WHERE league = "Premiership" AND season = "2016 - 2017" AND home_team = "Arsenal"
    UNION ALL
    SELECT away_team AS Team, match_date AS mDate, IIF(home_goals < away_goals,3,IIF(home_goals = away_goals,1,0)) AS Pts
    FROM A WHERE league = "Premiership" AND season = "2016 - 2017" AND away_team = "Arsenal"
) AS Games
INNER JOIN
(
    SELECT home_team AS Team, match_date AS mDate, IIF(home_goals > away_goals,3,IIF(home_goals = away_goals,1,0)) AS Pts
    FROM A WHERE league = "Premiership" AND season = "2016 - 2017" AND home_team = "Arsenal"
    UNION ALL
    SELECT away_team AS Team, match_date AS mDate, IIF(home_goals < away_goals,3,IIF(home_goals = away_goals,1,0)) AS Pts
    FROM A WHERE league = "Premiership" AND season = "2016 - 2017" AND away_team = "Arsenal"
) AS Runnings ON Games.Team = Runnings.Team AND Games.mDate >= Runnings.mDate
GROUP BY Games.Team, Games.mDate, Games.Pts
ORDER BY Games.Team, Games.mDate;

推荐阅读