使用计数(包括不匹配的行)连接2个表

这里有一个完整的、希望更有用的答案,它演示了示例数据及其产生的结果。请运行以下示例:

CREATE TABLE #band
    (
        BandId INT ,
        BandName NVARCHAR(10)
    );

CREATE TABLE #song
    (
        SongId INT ,
        BandId INT ,
        SongName NVARCHAR(10)
    );

INSERT INTO #band ( BandId ,
                    BandName )
VALUES ( 1, 'Rockers' ) ,
       ( 2, 'Rappers' ) ,
       ( 3, 'Ravers' ) ,
       ( 4, 'Poppers' );

INSERT INTO #song ( SongId ,
                    BandId ,
                    SongName )
VALUES ( 1, 1, 'rock 1' ) ,
       ( 2, 1, 'rock 2' ) ,
       ( 3, 1, 'rock 3' ) , -- rock = 3 songs - not output
       ( 4, 2, 'rap 1' ) , 
       ( 5, 2, 'rap 2' ) ,  -- rap = 2 songs - is output
       ( 6, 3, 'rave 1' );  -- rave = 1 song - is output
        -- pop = 0 songs - is output

SELECT   b.BandId ,
         b.BandName ,
         COUNT(s.SongId) Songs
FROM     #band AS b
         LEFT JOIN #song AS s ON s.BandId = b.BandId  -- left join includes 0's
GROUP BY b.BandId ,
         b.BandName
HAVING   COUNT(s.SongId) < 3;  -- filters to bands with less than 3 songs

DROP TABLE #band;
DROP TABLE #song;

生产:

BandId      BandName   Songs
----------- ---------- -----------
4           Poppers    0
2           Rappers    2
3           Ravers     1

您正在寻找 left join :

select b.name, count(s.bandid) as NUMBER_OF_SONGS
from bands b left join
     songs s
     on b.bandid = s.bandid  -- or whatever your join conditions are
group by b.name
having count(s.bandid) < 3;