时间戳

首页 > 解决方案 > 将两个选择查询与一个通用列组合，但结果不同

sql - 将两个选择查询与一个通用列组合，但结果不同

问题描述

我正在努力结合这两个 sql 查询以获得只有 3 列的表

SELECT M.name, COUNT(A.MusicianID) AS AlbumAppearance
        FROM Album A, Musician M
  WHERE A.MusicianID=M.MusicianID
     GROUP BY M.name
  

  SELECT M.Name,COUNT (Pr.MusicianID)  AS SongAppearance  
        FROM  Performs Pr, Musician M
  WHERE Pr.MusicianID=M.MusicianID
  GROUP BY M.name

标签： sql

解决方案

假设音乐家的名字是唯一的（这似乎是一个非常合理的假设），相关的子查询可能是做你想做的最简单和最好的表现方式：

SELECT m.*,
       (SELECT COUNT(*)
        FROM Album A
        WHERE A.MusicianID = M.MusicianID
       ) as AlbumAppearance,
       (SELECT COUNT(*)
        FROM Performs Pr
        WHERE Pr.MusicianID = M.MusicianID
       ) as SongAppearance
FROM Musician m;

推荐阅读