mysql - 使用mysql组后获取具有最高ID的值

Question

我有一个网站，允许用户上传图像以进行审核。用户首先创建一个审计，该审计具有存储在审计表中的随机数auditID和dateStart(用户选择的日期，存储为DATE而不是DATETIME)。然后，他们选择要上传的图像，然后为该图像分配一个 ID (imageID) 及其在服务器上的文件路径 (imageLocation) 以及存储在图像表中的始终为“标准”的类型。 auditID是图像表中的外键。

然后使用脚本显示这些图像，该脚本按图像位置对图像进行分组，以便显示每个图像的一个实例以及使用 MAX(dateStart) 所使用的最后一次审核的名称。

除非用户使用相同的图像进行具有相同开始日期的多次审核，否则此方法效果很好，在这种情况下，该图像会显示多次，每次审核在同一日期显示一次。最简单的方法是将 dateStart 从日期更改为日期时间，但这不是一个可行的选择，因此下一个最好的方法是不仅找到 MAX(dateStart)，还找到 MAX(imageID)。

我尝试在 GROUP 之后添加 SORT BY i.imageID LIMIT 1 DESC 但它只返回一个图像，而不是每个图像的最新实例。

这就是我到目前为止所拥有的......

SELECT i.imageID AS newest_image, 
       i.userID AS image_userID, 
       i.auditID AS image_auditID, 
       i.type AS image_type, 
       i.imageLocation AS location,
       a.*
       FROM image i
       JOIN audit a
       ON i.auditID = a.auditID
       JOIN 
        (
         SELECT i.imageLocation, MAX(a.dateStart) MaxDate
         FROM image i
             JOIN audit a
             ON i.auditID = a.auditID
         WHERE i.userID = '$id' 
         AND i.type = 'standard' 
         GROUP BY i.imageLocation
        )
      g ON  g.MaxDate = a.dateStart
      AND g.imageLocation = i.imageLocation
      WHERE i.userID = '$id' 
      AND i.type = 'standard'
      AND i.imageLocation NOT LIKE '%standard_examples%'"

我希望它返回与现在相同的结果，但如果同一图像在同一日期多次使用，那么我想要具有最高 imageID 的图像实例。

我希望这是足够的信息，表格非常复杂，但如果需要的话我可以提供它们和示例数据，但这需要一段时间。

Answer 1

尝试重新表述问题:您正在存储具有关联的audit记录的image记录。有时会加载重复的图像，并且通过具有相同的 imageLocation 来识别这些图像。查找最新的图像记录及其关联的审核记录。

我认为您的查询看起来是正确的，但如果所有图像 ID 都是唯一的，则无需在内部查询中加入 audit 表。之后加入 audit 表。如果id已经被识别，也不需要再次加入最大日期。

这是对同一查询的尝试，为了清楚起见，我只是将子查询拉出到 CTE 中，但您可以将其放回原来的位置。

WITH images AS (
    SELECT imageLocation, MAX(imageID) AS newest_imageID
      FROM image
      WHERE i.userID = '$id' 
        AND i.type = 'standard'
        AND i.imageLocation NOT LIKE '%standard_examples%'
      GROUP BY imageLocation
)

SELECT i.imageID AS newest_image, 
       i.userID AS image_userID, 
       i.auditID AS image_auditID, 
       i.type AS image_type, 
       i.imageLocation AS location,
       a.*

  FROM image i
  JOIN images ni ON (i.imageID = ni.newest_imageID)
  JOIN audit a ON (i.auditID = a.auditID)

更新:

如果没有 CTE，您可以返回到原始格式(请注意，我尚未创建表格并加载数据进行测试，这只是基于查询中的问题):

SELECT i.imageID AS newest_image, 
       i.userID AS image_userID, 
       i.auditID AS image_auditID, 
       i.type AS image_type, 
       i.imageLocation AS location,
       a.*

  FROM image i

  JOIN ( SELECT imageLocation, MAX(imageID) AS newest_imageID
               FROM image i
               WHERE i.userID = '$id' 
                 AND i.type = 'standard'
                 AND i.imageLocation NOT LIKE '%standard_examples%'
               GROUP BY imageLocation
       ) ni ON (i.imageID = ni.newest_imageID)

  JOIN audit a ON (i.auditID = a.auditID)