mysql - MYSQL内部联接第3表排序最新

我有3个表格，类别/帖子/画廊。

需要返回按照最新排序的第一张gsort照片按csort顺序排序的类别列表。如果没有照片或帖子，则显示null。

每个帖子的第一张照片通常将为gsort = 1，但是，如果删除此照片，则需要显示最低的gsort值，因为我随后将显示一个占位符图像。

categories
----------
cid cname   curl    csort
1   ccc     ccc/    3
2   bbb     bbb/    2
3   aaa     aaa/    1
4   ddd     ddd/    4


posts
-----
pid pname   purl            cid padded              poffon
1   apples  apples.html     2   2019-01-02 10:11:12 1
2   orange  orange.html     1   2019-01-02 10:12:00 1
3   grape   red-grapes.html 1   2019-01-06 10:15:12 1
4   banana  bannas.html     2   2019-01-08 10:19:54 0   //Note: this post is hidden
5   kiwi    kiwi-fruit.html 3   2019-01-10 10:26:20 1


gallery_photos
--------------
gid pid gsrc        gsort
1   1   img01a.jpg  6
2   1   img01b.jpg  2
3   1   img01c.jpg  4
4   3   img03a.jpg  2
5   3   img03b.jpg  1
6   3   img03c.jpg  4
7   3   img03d.jpg  3
8   2   img02a.jpg  1
9   1   img01d.jpg  5
10  1   img01e.jpg  3

预期结果>>>

cid cname   curl    gsrc
3   aaa     aaa/    null        //Note: no photos exist for this post
2   bbb     bbb/    img02a.jpg  //Note: 1 post exists for this category, show gsrc for lowest gsort
1   ccc     ccc/    img03b.jpg  //Note: 2 posts exist for this category, show gsrc for lowest gsort for the latest post date
4   ddd     ddd/    null        //Note: no posts exist for this category... a holding page with other content will be shown

这是到目前为止查询返回的结果太多的结果：

SELECT C.id
     , C.name
     , GA.medium_path AS gsrc
     , GA.gsort
  FROM CATEGORIES C
  LEFT
  JOIN POSTS P
    ON C.cid = P.cid
  JOIN
     ( SELECT cid
            , MAX(padded) max_date
         FROM POSTS
        GROUP
           BY cid
        ORDER
           BY padded DESC
     ) t
    ON t.max_date = P.padded
   AND C.cid = P.cid
  LEFT
  JOIN GALLERY GA
    ON GA.pid = P.pid

提前谢谢了

最佳答案

该查询应为您提供所需的结果。它使用两个子查询；一个获取每个类别的最新帖子，第二个获取每个帖子的gsort值最低的照片。然后将这两个子查询的结果LEFT JOIN放入categories表，以给出每个类别的gsort值最低的最新帖子：

SELECT c.*, pm.pid, pm.pname, pm.purl, pm.padded, pm.poffon, gm.gsrc, gm.gsort
FROM categories c
LEFT JOIN (SELECT p.*
           FROM posts p
           JOIN (SELECT cid, MAX(padded) AS latest
                 FROM posts
                 WHERE poffon = 1
                 GROUP BY cid) pl ON pl.cid = p.cid AND pl.latest = p.padded
          ) pm ON pm.cid = c.cid
LEFT JOIN (SELECT g.*
           FROM gallery_photos g
           JOIN (SELECT pid, MIN(gsort) AS earliest
                 FROM gallery_photos
                 GROUP BY pid) ge ON ge.pid = g.pid AND ge.earliest = g.gsort
          ) gm ON gm.pid = pm.pid
ORDER BY cname

输出：

cid     cname   curl    csort   pid     pname   purl                padded                  poffon  gsrc        gsort
3       aaa     aaa/    1       5       kiwi    kiwi-fruit.html     2019-01-10 10:26:20     1
2       bbb     bbb/    2       1       apples  apples.html         2019-01-02 10:11:12     1       img01b.jpg  2
1       ccc     ccc/    3       3       grape   red-grapes.html     2019-01-06 10:15:12     1       img03b.jpg  1
4       ddd     ddd/    4

Demo on dbfiddle

注意，我认为您对类别bbb的预期结果是错误的，对于cid = 2，最新的帖子是帖子1，该帖子的最低gsort值是2，对应于img01b。 img02a属于帖子2，位于cid 1中，而不是2中。