mysql - 加入专辑并附上图片数量和封面？

我正在尝试使用MySQL从两个表中获得某些结果，以下是两个表的格式：

          Albums                                         Images
------------------------------                 ----------------------------
  id   |   name  |   cover                       id |   image     | album
------------------------------                 ----------------------------
  1    | album1  |    3                          1  | image1.jpg  |   2
  2    | album2  |    1                          2  | image2.jpg  |   3
  3    | album3  |    0                          3  | image3.jpg  |   1
  4    | album4  |    0                          4  | image4.jpg  |   1
------------------------------                   5  | image5.jpg  |   3
                                                 6  | image6.jpg  |   1
                                               ----------------------------

现在给出上面的表格，我希望结果看起来像这样：

---------------------------------------
  id  |  name  |  count |  cover
---------------------------------------
  1   | album1 |   3    |  image3.jpg
  2   | album2 |   1    |  image1.jpg
  3   | album3 |   2    |    null
  4   | album4 |   0    |    null
---------------------------------------

count是给定相册中的图像数量
cover是ID与专辑封面栏中的ID匹配的图像


我尝试了一些查询，但没有一个给我想要的结果。

SELECT a.id,
       a.name,
       count(p.id) AS imageCount,
       (SELECT image
          FROM albums,
               photos
         WHERE albums.cover = photos.id)
  FROM photos p
RIGHT OUTER JOIN albums a
              ON a.id=p.album
  GROUP BY a.id;

这样只会导致每一行列出相同的封面图片。

我也尝试过这个：

SELECT a.id,
       a.name,
       count(p.id) AS imageCount,
       coverTable.image
  FROM photos p
RIGHT OUTER JOIN albums a
              ON a.id=p.album
RIGHT OUTER JOIN (SELECT albums.id,
                         image
                     FROM albums,
                          photos
                    WHERE albums.cover = photos.id) AS coverTable
              ON coverTable.id = a.id
        GROUP BY a.id;

这样只会为一张有封面图像的相册产生一行。

最佳答案

SELECT a.id, a.name, count(p1.id) AS imageCount, p2.image as cover
FROM albums a
LEFT JOIN photos p1 ON p1.album = a.id
LEFT JOIN photos p2 ON p2.id = a.cover
GROUP BY a.id

DEMO

关于mysql - 加入专辑并附上图片数量和封面？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26559167/