MySQL按2列分组，但按特定顺序返回

Question

我看过类似问题的答案，但找不到解决此问题的方法。

我需要从三个表中选择相册、单张照片和用户详细信息。我只想返回每个用户的最新相册。在尝试分组和 SELECT DISTINCT 查询后，我找不到这样做的方法。这是我当前的查询，除了从同一用户返回多个相册外，它工作正常:

SELECT 
gallery_albums.title, gallery_albums.siteID, gallery_albums.albumID, gallery_albums.albumDate, gallery_photos.photoID, gallery_albums.userID
FROM gallery_albums, users, gallery_photos
WHERE users.userID = gallery_albums.userID 
AND gallery_photos.albumID = gallery_albums.albumID
GROUP BY gallery_albums.albumID
ORDER BY gallery_albums.albumDate DESC 
LIMIT 0 , 30

工作代码

这是对 aarthi_ram 建议的解决方案的轻微修改

SELECT * 
  FROM (
       SELECT gallery_albums.title, gallery_albums.siteID, gallery_albums.albumID, gallery_albums.albumDate, gallery_photos.photoID, gallery_albums.userID, users.fName, users.lName
    FROM gallery_albums, users, gallery_photos
    WHERE users.userID = gallery_albums.userID
    AND gallery_photos.albumID = gallery_albums.albumID
    GROUP BY gallery_albums.albumID
    ORDER BY gallery_albums.albumDate DESC  
  ) AS newTable
  GROUP BY userID
  ORDER BY albumDate DESC
  LIMIT 10

Answer 1

很难想象您的表的架构，但我猜每个表中都有auto_increment 列。一个用户可以有多个gallery_albums，也可以包含多个gallery_photos。

SELECT  a.*, b.*, c.*
FROM    users a
        INNER JOIN gallery_albums b
            ON a.userID = b.userID 
        INNER JOIN gallery_photos c
            ON b.albumID = c.albumID 
        INNER JOIN
        (
            SELECT  userID, MAX(ID) max_ID
            FROM    gallery_albums
            GROUP   BY userID
        ) d ON  b.userID = d.userID AND
                b.ID = d.max_ID
        INNER JOIN
        (
            SELECT  albumID, MAX(ID) max_ID
            FROM    gallery_photos
            GROUP   BY albumID
        ) e ON  c.albumID = e.albumID AND
                c.ID = e.max_ID
ORDER   BY b.albumDate DESC
LIMIT   0, 30

如您所见，在两个子查询中使用了名为 ID 的列:d 和 e。这些列是每个表上的auto_incremented 列。如果您有日期列来定义它们的状态，无论它们是最近的还是最近的，您也可以使用它。