我想为专辑的封面选择一个随机图像。如果专辑包含标记为"封面"的图像,则应仅从这些图像中进行选择。如果子Album的图像标记为"封面",则不应选择未标记为"封面"的图像。
这是一个视觉结构。
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 |
表专辑_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的电流。如果可以合并,那会很好。可以左加入内部联接表吗?如果标签不存在怎么办?
[2],[4]:这是专辑ID来自php。
[3]:这来自PHP,以确保包括子弹。
解决方案将生成以下结果。
- 专辑1应该从图像1,图像2 ,图3,图像4,图像5,图像6
- 专辑2应该从图像3,图像4 中选择
- 专辑3应该从图像5,图像6 中选择
是否有一种修改SQL的方法,以便我可以生成我的预期结果?
P.S。我正在运行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,');
这是我的解决方案。
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;