mysql - 根据不同表中的条件进行计数

Question

我有 4 个表(歌曲、专辑和两个关系表)。

我有两个查询需要合并:

-查询 1)

SELECT l.name_language, count(s.id_song) 
FROM language as l
LEFT JOIN song_has_languages as s ON l.id_language = s.id_language
GROUP BY l.id_language
HAVING COUNT(s.id_song) > 0
ORDER BY name_language ASC

输出:

name_language | songs 
English       | 5     
Spanish       | 1          

-查询 2)

SELECT l.name_language, count(a.id_album) 
FROM language as l
LEFT JOIN album_has_languages as a ON l.id_language = a.id_language
GROUP BY l.id_language
HAVING COUNT(a.album)> 0
ORDER BY name_language ASC

输出:

name_language | albums
English       | 5
French        | 2

我的目标是这个输出:

name_language | total | 
English       | 10    | 
Spanish       | 1     | 
French        | 2     | 

我只想打印歌曲或专辑的语言。

Answer 1

您可以使用union all和聚合来完成您想要的操作:

select l.name_language, sum(num_songs + num_albums) as total
from language l left join
     ((select shl.id_language, count(*) as num_songs, 0 as num_albums
       from song_has_languages shl
       group by  shl.id_language
      ) union all
      (select ahl.id_language, 0 as num_songs, count(*) as num_albums
       from album_has_languages ahl
       group by ahl.id_language
      )
     ) sa
     on sa.id_language = l.id_language
group by l.id_language, l.name_language;

您还可以使用LEFT JOIN来表达:

select l.name_language,
       ( coalesce(num_songs, 0) + coalesce(num_albums, 0) ) as total
from language l left join
     (select shl.id_language, count(*) as num_songs
      from song_has_languages shl
      group by  shl.id_language
     ) shl 
     on shl.id_language = l.id_language left join
     (select ahl. id_language, count(*) as num_albums
      from album_has_languages ahl
      group by ahl.id_language
     ) ahl
     on ahl.id_language = l.id_language;