mysql - SQL查询计数图片并获取封面图片

Question

我有带有属性 pictureId、albumName、isCover picture 的表格图片。

+--------+-----------+----------+
|   id   | albumName | coverPic |
+--------+-----------+----------+
| String | String    | Boolean  |
+--------+-----------+----------+

我需要编写查询以获取每张专辑中的图片数量并获取每张专辑封面图片的 ID。我写了这个查询并获得了图片数量，但我不知道如何获取每张专辑的封面图片 ID。

SELECT ALBUM as album, count(ALBUM) as  picsInAlbum, PICTURE_ID as pictureId 
FROM PICTURES 
group by ALBUM, PICTURE_ID

例如:

+--------+-----------+----------+
|   id   | albumName |isCover   |
+--------+-----------+----------+
| 1      | test      | true     |
+--------+-----------+----------+
+--------+-----------+----------+
| 2      | test      | false    |
+--------+-----------+----------+
+--------+-----------+----------+
| 3      | test1      | true    |
+--------+-----------+----------+
+--------+-----------+----------+
| 4      | test1      | false   |
+--------+-----------+----------+
+--------+-----------+----------+
| 5      | test2      | true    |
+--------+-----------+----------+
+--------+-----------+----------+
| 6      | test2      | false   |
+--------+-----------+----------+

作为查询结果，我需要这个。

+--------+-----------+----------+
| ALBUM  |PICSINALBUM|coverPicId
+--------+-----------+----------+
+--------+-----------+----------+
| test   | 2         | 1        |
+--------+-----------+----------+
+--------+-----------+----------+
| test1  | 2         | 3        |
+--------+-----------+----------+
+--------+-----------+----------+
| test2  | 2         | 5        |
+--------+-----------+----------+

Answer 1

条件聚合。对每一行进行测试以确定该行是否是相册的“封面图片”。如果是，返回picture_id，否则返回NULL。然后使用聚合函数挑选出一个非 NULL 值。

像这样:

 SELECT p.album                                AS album
      , COUNT(p.album)                         AS picsInAlbum
      , MAX(IF(p.isCover=1,p.picture_id,NULL)) AS pictureId 
   FROM `PICTURES` p 
  GROUP
     BY p.album

---

注意:MySQL IF() 函数可以替换为更便携的 ANSI 标准等效函数

      , MAX(CASE WHEN p.isCover = 1 THEN p.picture_id ELSE NULL END) AS pictureId 

注意:建议使用小写字母作为表名。

... it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html