返回一个包含yes或no列的表

Question

我有以下两个表：

歌手

identification    name
-------------------------
20          Bolton
21          Madonna
22          Nirvana
23          Hendrix

合并

first_singer_id     second_singer_id       genre
---------------------------------------------------
20                  21               pop
21                  23               pop
22                  21               rock
23                  20               rock
23                  21               metal
23                  21               hiphop
23                  22               pop

我想得到一个结果，歌手表上的每个名字都在第二栏中说明了他们是否唱金属歌。例如，亨德里克斯和麦当娜唱金属歌，所以桌子应该是这样的：

name     metal
--------------
Bolton   N
Madonna  Y
Nirvana  N
Hendrix  Y

我尝试了以下方法，但没有得到明显的结果，也没有考虑到Colaborate的对称关系。

select case when C.genre= 'metal' then "Y" else "N" end as genre, S1.name 
from   Colaborate C
JOIN Singers S1
ON S1.identification = C.first_singer_id
JOIN Singers S2
ON S2.identification = C.second_singer_id

如何使用CASE表达式解决此问题？

Answer 1

您可以通过多种方式确定这一点，其中一种方式是使用exists

Select name,
case when exists (select * from colaborate c where c.first_singer_id=s.identification and genre='metal')
or exists (select * from colaborate c where c.second_singer_id=s.identification and genre='metal')
then 'Y' else 'N' end as Metal
from Singers s

Answer 2

使用LEFT联接联接表并使用条件聚合：

SELECT s.name,
MAX(CASE WHEN c.genre = 'metal' THEN 'Y' ELSE 'N' END) metal
FROM Singers s LEFT JOIN Colaborate c
ON s.identification IN (c.first_singer_id, c.second_singer_id)
GROUP BY s.identification, s.name

Answer 3

firstronginger_id，second_singer_id。。。表示该模式可以做得更好。如果三个歌手合作会发生什么？还是三十？

SQL中的列表是通过联接表完成的。如果你像这样重组你的数据…

create table songs (
id bigint primary key generate always as identity,
name text not null,
genre text not null
);
create table singers (
id bigint primary key generate always as identity,
name text not null
);
create table song_singers (
song_id bigint not null references songs(id),
singer_id bigint not null references singers(id)
);

现在，每首歌可以有你喜欢的歌手。您仍然需要聚合得到的行，forpas的技术运行良好，但没有自联接。

select
singers.name,
max(case songs.genre when 'metal' then 'Y' else 'N' end) as genre
from singers
left join song_singers on singers.id = song_singers.singer_id
left join songs on songs.id = song_singers.song_id
group by singers.id

试试看。