带有 WHERE IN 子句的 SQL 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'链接，由'execution'链接。所以，对于每个项目，每天都有多个数据，每天都有多个执行数据。我意识到 HAVING MAX 可能需要比较，但要比较什么呢？也许修改后的 JOIN 会有所帮助。

谢谢，
基思

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;

我真的不知道这是否接近您想要的任何东西。