带有 WHERE IN 子句的 SQL MAX 日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8947980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL MAX date with WHERE IN Clause
提问by dropper
I will give 2 examples, 1st one that works, and the 2nd with what I want to do:
我将给出 2 个例子，第一个有效，第二个是我想要做的：
SELECT P.ProjName, D.update_date, SUM(E.pass), SUM(E.fail)
FROM execution AS E INNER JOIN (
daily AS D INNER JOIN project AS P ON D.project_ID = P.ID )
ON E.daily_ID = D.ID
WHERE D.project_id = 25 AND D.update_date = (
SELECT MAX(update_date) FROM daily WHERE project_id = 25; )
GROUP BY P.ProjName, D.update_date;
This will work for individual projects. It gives me the total of Pass and Fails for the latest day of a given project (here, 25). I would like to grab a group of projects using an WHERE IN clause. For example (but does not work):
这将适用于单个项目。它为我提供了给定项目最近一天的通过和失败总数（此处为 25）。我想使用 WHERE IN 子句获取一组项目。例如（但不起作用）：
SELECT P.ProjName, D.update_date, SUM(E.pass), SUM(E.fail)
FROM execution AS E INNER JOIN (
daily AS D INNER JOIN project AS P ON D.project_ID = P.ID )
ON E.daily_ID = D.ID
WHERE D.project_id IN (25,26,28,29,30,31)
GROUP BY P.ProjName, D.update_date
HAVING MAX(D.update_date);
This will result in every date getting pulled for each of the projects. There are 3 tables involved, 'project' which is linked by 'daily', which is linked by 'execution'. So, for every project, there is multiple daily data, and each day has multiple execution data. I realize the HAVING MAX probably needs a comparison, but to what? Perhaps a modified JOIN will help.
这将导致每个项目的每个日期都被拉取。涉及3个表，'project'由'daily'链接，由'execution'链接。所以，对于每个项目，每天都有多个数据，每天都有多个执行数据。我意识到 HAVING MAX 可能需要比较，但要比较什么呢？也许修改后的 JOIN 会有所帮助。
Thanks, Keith
谢谢，基思
回答by Rick Kuipers
It's a bit tricky trying to do this without knowing the database but try this:
在不知道数据库的情况下尝试这样做有点棘手，但试试这个：
SELECT P.ProjName, D.update_date, SUM(E.pass), SUM(E.fail)
FROM execution AS E INNER JOIN (
daily AS D INNER JOIN project AS P ON D.project_ID = P.ID )
ON E.daily_ID = D.ID
WHERE D.project_id IN (25,26,28,29,30,31) AND D.update_date = (
SELECT MAX(update_date) FROM daily WHERE project_id = D.project_id)
GROUP BY P.ProjName, D.update_date;
I really have no idea if this is close to anything you'd want.
我真的不知道这是否接近您想要的任何东西。
回答by Michael Goldshteyn
You didn't specify a particular DBMS, so how about this (untested) SQL code which should for the most part be DBMS agnostic:
您没有指定特定的 DBMS，那么这个（未经测试的）SQL 代码如何，它在很大程度上应该是 DBMS 不可知的：
SELECT P.ProjName,
DesiredProjectUpdateDate.MaxUpdateDate,
SELECT SUM(pass) AS PassSum
FROM execution
WHERE daily_ID=D.ID,
SELECT SUM(fail) AS FailSum
FROM execution
WHERE daily_ID=D.ID
FROM
( -- This derived table helps you get the correct records from the daily table
SELECT D.project_ID, MAX(D.Update_date) AS MaxUpdateDate
FROM daily AS D
GROUP BY D.project_ID
WHERE D.project_ID IN (25,26,28,29,30,31)
) As DesiredProjectUpdateDate
JOIN daily AS D
ON D.project_ID=DesiredProjectUpdateDate.project_ID AND
D.Update_date=DesiredProjectUpdateDate.MaxUpdateDate
JOIN project AS P
ON P.ID = DesiredProjectUpdateDate.ProjectId
Give that a shot and let me know if you have any further issues.
试一试，如果您有任何其他问题，请告诉我。
回答by Tom Chantler
The issue is that you need a select which returns the max value of update_date for each project.
问题是您需要一个选择来为每个项目返回 update_date 的最大值。
Try this:
尝试这个：
SELECT P.ProjName, D.update_date, SUM(E.pass), SUM(E.fail)
FROM execution AS E INNER JOIN (
daily AS D INNER JOIN project AS P ON D.project_ID = P.ID )
ON E.daily_ID = D.ID
WHERE D.project_id IN (25,26,28,29,30,31)
AND D.update_date > ALL
(SELECT update_date FROM
daily AS D2
WHERE D.ID=D2.ID -- I am assuming that daily.ID is the primary key
AND ISNULL(D.update_date, '9999 DEC 31') <> D2.update_date)
GROUP BY P.ProjName, D.update_date;
I quite often write queries like this and they are faster than using the MAXaggregate function and work nicely.
我经常写这样的查询，它们比使用MAX聚合函数更快并且工作得很好。
EDIT:Sorry, I forgot to remove your now-redundant HAVING MAX...line. My bad!
编辑：对不起，我忘了删除你现在的冗余HAVING MAX...线路。我的错！
