在SQL查询中显示零值数据?

尝试从团队左侧选择加入成员

SELECT Team.Teamid, count(memberid)
FROM 
    TEAM
LEFT OUTER JOIN 
     Member on Member.teamid = Team.Teamid
GROUP by Team.Teamid

只是想给你一些关于这是怎么回事的背景知识。

上面说

给我团队中的所有团队ID,然后对每个团队ID计算成员表中的匹配项,即使没有匹配项。

如果你使用

SELECT Team.Teamid, count(memberid)
FROM 
    TEAM
INNER JOIN 
     Member on Member.teamid = Team.Teamid
GROUP by Team.Teamid

这意味着

SELECT teamID, count(memberID) AS [noOfMembers]
FROM team
LEFT JOIN member
ON team.teamID = member.teamID
GROUP by teamID;

我做了这个测试,为我工作

CREATE TABLE team (
 teamID CHAR(6) NOT NULL PRIMARY KEY); 

CREATE TABLE member (
 memberID CHAR(7) NOT NULL PRIMARY KEY,
 teamID CHAR(6) NOT NULL REFERENCES team(teamID) );

INSERT INTO team (teamID) VALUES ('T1')
INSERT INTO team (teamID) VALUES ('T2')
INSERT INTO team (teamID) VALUES ('T3')
INSERT INTO team (teamID) VALUES ('T4')

INSERT INTO member (memberID, teamID) VALUES ('M1', 'T1')
INSERT INTO member (memberID, teamID) VALUES ('M2', 'T1')
INSERT INTO member (memberID, teamID) VALUES ('M3', 'T1')
INSERT INTO member (memberID, teamID) VALUES ('M4', 'T3')

SELECT Team.teamID, count(member.memberID) AS [noOfMembers]
FROM Team LEFT JOIN member ON Member.teamID = Team.teamID
GROUP BY ALL Team.teamID;

正确连接到团队表应该可以解决此问题。