时间戳

首页 > 解决方案 > 计算不同表中的条件

mysql - 计算不同表中的条件

问题描述

我有 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     |

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

标签： mysqlsql

解决方案

你可以做你想做的事情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 JOINs 来表达：

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;

推荐阅读