sql - 在给定附加条件的情况下从 JOIN 获取单行

Question

我正在做一个选择，其中我给出了一年（下面硬编码为1981），我希望每个合格的乐队都有一排。主要问题是为每个乐队找到最年长的在世成员：

SELECT b.id_band,
    COUNT(DISTINCT a.id_album),
    COUNT(DISTINCT s.id_song),
    COUNT(DISTINCT m.id_musician),
    (SELECT name FROM MUSICIAN WHERE year_death IS NULL ORDER BY(birth)LIMIT 1)
FROM BAND b
    LEFT JOIN ALBUM a ON(b.id_band  = a.id_band)
    LEFT JOIN SONG  s ON(a.id_album = s.id_album)
    JOIN MEMBER m ON(b.id_band= m.id_band)
    JOIN MUSICIAN mu ON(m.id_musician = mu.id_musician)

  /*LEFT JOIN(SELECT name FROM MUSICIAN WHERE year_death IS NULL
              ORDER BY(birth) LIMIT 1) AS alive FROM mu*/ -- ??

WHERE b.year_formed = 1981
GROUP BY b.id_band;

我想从mu每个乐队中获得最年长的在世成员。但我只是从关系中得到了最年长的音乐家MUSICIAN。

这是显示我当前查询的输出的屏幕截图：

标签： sqlpostgresqlleft-joindistinctgreatest-n-per-group

Answer 1

您可以引用不在此嵌套选择中的表，如下所示

SELECT b.id_band,
COUNT(DISTINCT a.id_album),
COUNT(DISTINCT s.id_song),
COUNT(DISTINCT m.id_musician),
(SELECT name FROM MUSICIAN WHERE year_death IS NULL ORDER BY(birth) AND 
MUSICIAN.id_BAND = b.id_band LIMIT 1)
FROM BAND b
LEFT JOIN ALBUM a ON(b.id_band  = a.id_band)
LEFT JOIN SONG  s ON(a.id_album = s.id_album)
JOIN MEMBER m ON(b.id_band= m.id_band)
JOIN MUSICIAN mu ON(m.id_musician = mu.id_musician)

/*LEFT JOIN(SELECT name FROM MUSICIAN WHERE year_death IS NULL ORDER 
BY(birth)LIMIT 1) AS alive FROM mu*/
WHERE b.year_formed= 1981       
GROUP BY b.id_band