mysql - 在 SQL 中选择随机图像封面

Question

我想随机选择一张图片作为相册的封面。如果相册包含标有“封面”的图像，则应仅从这些图像中选择。如果子相册中有带有“cover”标签的图片，则不应选择未带有“cover”标签的图片。

这是一个视觉结构。

Album 1
├───Image 1
├───Album 2
│   ├───Image 2
│   ├───Image 3 (cover)
│   └───Image 4 (cover)
└───Album 3
    ├───Image 5
    └───Image 6

• 相册 1 应从图 1、图 3、图 4、图 5、图 6 中选择
• 相册 2 应从图 3、图 4 中选择
• 相册 3 应从图 5、图 6 中选择

这是我的表格

表格标签

| id | name  |
|----|-------|
| 1  | cover |

表image_tag

| id | image_id | tag_id |
|----|----------|--------|
| 1  | 3        | 1      |
| 2  | 4        | 1      |

表 album_image

| id | album_id | image_id |
|----|----------|----------|
| 1  | 1        | 1        |
| 2  | 2        | 2        |
| 3  | 2        | 3        |
| 4  | 2        | 4        |
| 5  | 3        | 5        |
| 6  | 3        | 6        |

表相册

| id | name    | parent_id |
|----|---------|-----------|
| 1  | album 1 | null      |
| 2  | album 2 | 1,        |
| 3  | album 3 | 1,        |

这是我想出的SQL。

SELECT ai.image_id AS image_id FROM albums AS a
INNER JOIN album_image AS ai ON ai.album_id = a.id
LEFT JOIN image_tag AS it ON it.image_id = ai.image_id AND it.tag_id = 1[1]
WHERE a.id = 1[2] OR parent_id LIKE '1,%'[3]
ORDER BY CASE
    WHEN it.tag_id IS NOT NULL AND a.id = 1[4] THEN RAND()
    ELSE RAND() + 1
END  ASC LIMIT 1;

[1]:这是当前在 PHP 中由另一个 SQL 获取的。如果能合并就好了。是否可以 LEFT JOIN INNER JOIN 表？如果标签不存在怎么办？

[2], [4]: 这是来自PHP的相册id。

[3]:这是来自 PHP 以确保包含子专辑。

该解决方案将生成以下结果。

• 相册 1 应从图片 1、图片 2、图片 3、图片 4、图片 5、图片 6 中选择
• 相册 2 应从图 3、图 4 中选择
• 相册 3 应从图 5、图 6 中选择

有没有办法修改 SQL 以便生成预期的结果？

附言我正在运行 MySQL。

编辑测试架构

CREATE TABLE tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name TEXT NOT NULL
);
INSERT INTO tags (name) VALUES ('cover');

CREATE TABLE image_tag (
  id INT AUTO_INCREMENT PRIMARY KEY,
  image_id INT NOT NULL,
  tag_id INT NOT NULL
);
INSERT INTO image_tag (image_id, tag_id) VALUES (3, 1);
INSERT INTO image_tag (image_id, tag_id) VALUES (4, 1);

CREATE TABLE album_image (
  id INT AUTO_INCREMENT PRIMARY KEY,
  album_id INT NOT NULL,
  image_id INT NOT NULL
);
INSERT INTO album_image (album_id, image_id) VALUES (1, 1);
INSERT INTO album_image (album_id, image_id) VALUES (2, 2);
INSERT INTO album_image (album_id, image_id) VALUES (2, 3);
INSERT INTO album_image (album_id, image_id) VALUES (2, 4);
INSERT INTO album_image (album_id, image_id) VALUES (3, 5);
INSERT INTO album_image (album_id, image_id) VALUES (3, 6);

CREATE TABLE albums (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id TEXT
);
INSERT INTO albums (name, parent_id) VALUES ('album 1', NULL);
INSERT INTO albums (name, parent_id) VALUES ('album 2', '1,');
INSERT INTO albums (name, parent_id) VALUES ('album 3', '1,');

Answer 1

这是我的解决方案。

SELECT ai.image_id AS image_id, (
    SELECT COUNT(*) FROM albums AS a2
        INNER JOIN album_image AS ai2 ON ai2.album_id=a2.id 
        LEFT JOIN image_tag AS it2 
            INNER JOIN tags AS t2 ON t2.id = it2.tag_id 
        ON it2.image_id = ai2.image_id AND t2.name = 'cover'
        WHERE a2.id = a.id AND t2.name IS NOT NULL AND it.tag_id IS NULL
    ) AS count FROM albums AS a 
INNER JOIN album_image AS ai ON ai.album_id=a.id 
LEFT JOIN image_tag AS it 
    INNER JOIN tags AS t ON t.id = it.tag_id 
ON it.image_id = ai.image_id AND t.name = 'cover'
WHERE a.id = 1 OR parent_id LIKE '1,%' 
ORDER BY CASE 
    WHEN it.tag_id IS NOT NULL AND a.id = 1 THEN RAND() 
    WHEN count > 0 THEN RAND() + 2
    ELSE RAND() + 1 
END ASC LIMIT 1;