mysql - 在这种特定的数据库布局中，如何从Songs表中选择songnames.id？

如何通过在songnames.id表中指定链接到的songs和多个songnames.name从artists.name表中选择songnames.id？

CREATE TABLE songnames (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    alias VARCHAR(255)
) ENGINE = 'InnoDB' DEFAULT CHARSET = 'UTF8';

CREATE TABLE artists (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL
) ENGINE = 'InnoDB' DEFAULT CHARSET= 'UTF8';

CREATE TABLE songs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    artist_id INT UNSIGNED NOT NULL REFERENCES artists(id) ON UPDATE CASCADE ON DELETE CASCADE,
    songname_id INT UNSIGNED NOT NULL REFERENCES songnames(id) ON UPDATE     CASCADE ON DELETE CASCADE,
    UNIQUE(artist_id, songname_id)
) ENGINE = 'InnoDB' DEFAULT CHARSET = 'UTF8';


songs table:                       artists table:

+----+-----------+-------------+   +----+---------------+
| id | artist_id | songname_id |   | id |     name      |
+----+-----------+-------------+   +----+---------------+
|  1 |     1     |      1      |   | 1  | Matt White    |
+----+-----------+-------------+   +----+---------------+
|  2 |     2     |      2      |   | 2  | Keyshia Cole  |
+----+-----------+-------------+   +----+---------------+
|  3 |     3     |      3      |   | 3  | Nitty Kutchie |
+----+-----------+-------------+   +----+---------------+
|  4 |     4     |      3      |   | 4  | Lukie D       |
+----+-----------+-------------+   +----+---------------+
|  5 |     5     |      4      |   | 5  | Sia           |
+----+-----------+-------------+   +----+---------------+

songnames table:

+----+--------------+--------------+
| id |     name     |     alias    |
+----+--------------+--------------+
| 1  | Love         |         NULL |
+----+--------------+--------------+
| 2  | Love         |         NULL |
+----+--------------+--------------+
| 3  | Love         | Must Be Love |
+----+--------------+--------------+
| 4  | The Greatest |         NULL |
+----+--------------+--------------+

到目前为止，我一直在玩This SQL Fiddle，但是我找不到通过给定songs和songname_id选择artists.name的方法。

这似乎正在工作：

SELECT DISTINCT
  songnames.id
FROM songs
JOIN songnames ON songs.songname_id = songnames.id
JOIN artists ON songs.artist_id = artists.id
WHERE songnames.name = 'Love' AND artists.name = 'Matt White'

但是我需要类似的东西（这显然无法执行）：

SELECT DISTINCT
  songnames.id
FROM songs
JOIN songnames ON songs.songname_id = songnames.id
JOIN artists ON songs.artist_id = artists.id
WHERE songnames.name = 'Love' AND artists.name = 'Lukie D' AND artists.name = 'Nitty Kutchie'

如何通过指定songnames.name（“爱”）和（多个）songnames.id（“ Lukie D”，“妮蒂·库奇”）从songs表中选择songnames.name（id 3）？

最佳答案

获取与歌曲名称和艺术家匹配的歌曲。然后按歌曲名称分组，看看是否有适合每个歌手的歌曲。

以下查询查找所有名为'Love'的歌曲，查找'Lukie D'和'Nitty Kutchie'，然后仅保留由两位（即count(*) = 2）歌手演奏的一首'Love'歌曲。

select songname_id
from songs
where songname_id in (select id from songnames where name = 'Love')
and artist_id in (select id from artists where name in ('Lukie D', 'Nitty Kutchie'))
group by songname_id
having count(*) = 2;

SQL提琴：http://sqlfiddle.com/#!9/fde558/10