mysql - 连接两个表文件夹和图像

Question

       album
------------------
  aid  |  aname  |  
------------------
  1   | album1   | 
  2   | album2   | 
  3   | album3   | 
  4   | album4   | 
------------------

       image
---------------------------------------
  iid  |  title  |  albumid |  imgurl
---------------------------------------
  1   | img1     |   3      |  image3.jpg
  2   | img2     |   1      |  image1.jpg
  3   | img3     |   2      |  image4.jpg
  4   | img4     |   0      |  image5.jpg
---------------------------------------

我有两个表专辑和图像。在图像表中albumid=0表示图像不属于任何文件夹。我需要带有所有文件夹名称和文件夹内图像数量的 imageurl，如 mysql 中的下表。

示例:

----------------------------------------------------------------
  id=aid+iid  |  name= aname +title | imgurl          | countimg
----------------------------------------------------------------
  1           | album1              |       null      |  1
  2           | album2              |       null      |  1
  3           | album3              |       null      |  1
  4           | album4              |       null      |  0
  4           | img4                |    image5.jpg   |  0
-----------------------------------------------------------------

请问有什么可以帮忙的吗？提前致谢。

Answer 1

试试这个:

SELECT a.aid AS `id`, a.aname AS `name`, NULL AS imgurl, COUNT(i.iid) countimg 
FROM album a 
LEFT OUTER JOIN image i ON a.aid = i.albumid  
GROUP BY a.aid 
UNION 
SELECT a.iid AS `id`, i.title AS `name`, imgurl AS imgurl, 0 countimg 
FROM image i 
WHERE i.albumid = 0;