SQL WHERE IN子句的MAX日期

Question

我将给出两个例子，第一个是有效的，第二个是我想要做的：

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;

这对于单个项目是可以工作的。它给我提供了一个给定项目（这里是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);

这将导致每个项目的每个日期都被拉取。涉及到3个表，'project'通过'daily'相连，'daily'通过'execution'相连。因此，对于每个项目，有多个每日数据，每天都有多个执行数据。我意识到HAVING MAX可能需要比较，但是与什么比较呢？也许修改JOIN会有所帮助。

谢谢， Keith

- dropper

---

这是一个使用Excel VBA中的ADODB连接器（Jet 4）的MS Access数据库。第一个答案解决了它。感谢大家的帮助。 - dropper

另外需要注意的是，我需要在SELECT和GROUP BY语句中添加P.ID以保持原始顺序。 - dropper

Answer 1

如果没有了解数据库，这个问题有点棘手，但可以尝试以下方法：

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;

我真的不知道这是否符合您的要求。

- Rick Kuipers

Answer 2

您没有指定特定的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

试一试，如果有任何进一步的问题，请告诉我。

- Michael Goldshteyn

Answer 3

问题在于您需要一个选择器，该选择器返回每个项目的update_date的最大值。

请尝试这个：

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;

我经常编写这样的查询，它们比使用 MAX 聚合函数更快且效果很好。

编辑：抱歉，我忘记删除您现在不需要的 HAVING MAX... 行了。我的错！

- Tom Chantler

---

是的，但它给了我一个错误：“在查询表达式中使用了错误数量的参数与函数'D.ID = D2.ID AND ISNULL(D.update_date,'9999 DEC 31') <> D2.update_date'。”也许Jet不喜欢ISNULL？我尝试过没有ISNULL，它给了我每个日期。 - dropper

嗯，我对Jet/Access不是很了解，但我给了你T-SQL代码...谢谢你让我知道它没有起作用。 - Tom Chantler

我找到了Jet的ISNULL答案，你需要使用IIf语法。AND IIf((SELECT MAX(update_date) FROM daily WHERE project_ID = D.project_ID) IS NULL, 0, (SELECT MAX(update_date) FROM daily WHERE project_ID = D.project_ID)) = IIf(D.update_date IS NULL, 0, D.update_date)。我还将连接改为RIGHT JOINS，这样我就可以获取所有项目，即使它们没有日期（未更新的项目）。 - dropper

很酷。感谢更新，如果其他人阅读这篇文章，这将非常方便。但我猜你最终使用了另一种解决方案。 :-) - Tom Chantler