如何在我的SQL语句中联接第三个表，该表返回一个COUNT而不丢失0个计数项? [英] How do I JOIN a third table in my SQL statement which returns a COUNT without losing the 0 count items?

问题描述

此问题的继续我们从下面开始执行了一条SQL语句...

We left off with a SQL statement as follows...

   SELECT t.teamid,
          t.teamname,
          COALESCE(COUNT(p.playerid), 0) AS playercount,
          t.rosterspots
     FROM TEAMS t
LEFT JOIN PLAYERS p ON p.teamid = t.teamid
 GROUP BY t.teamid, t.teamname, t.rosterspots

我还有一个要添加的约束.如果玩家要算入玩家人数"，需要先通过一次体检，该怎么办?

I have one more constraint to add. WHAT IF, players need to pass a Medical Exam before they count towards the "playercount"?

我将介绍桌子.

MedicalTests
PlayerId  PassedMedical
1         1
2         0
3         1
4         1

其中"PassedMedical"有点(1 = true).

Where "PassedMedical" is a bit (1 = true).

还要向团队"表中再添加1 ROW数据.

And also add 1 more ROW of data to the Teams table.

TeamId    Team Name
3         Toronto Rapters

这样，我有一个拥有0名球员的球队.

This way I have a team with 0 players.

预期的输出更改为:

Team Name         PlayerCount
Miami Heat        2
New York Knicks   1
Toronto Rapters   0

因为迈阿密热火的一名球员尚未通过医疗检查.

since one of the Miami Heat players has not yet passed the medical.

如果我添加

LEFT JOIN MEDICALTESTS m ON p.PlayerId = m.PlayerId
WHERE m.PassedMedical = 1

对于上面的语句，我丢失了所有的"0"行?

To the above statement I lose all the "0" rows?

谢谢，
贾斯汀

Thanks,
Justin

推荐答案

如果我误解了这个问题，请原谅我，但我认为没有必要将左联接加入到玩家表中.我们只需要球员人数.为什么不这样:

Forgive me if I've misunderstood the question, but I don't see the need for the left join onto the players table. We only need the count of players. Why not something like:

SELECT t.teamid,
          t.teamname, 
          (SELECT COUNT(*) FROM players inner join medicaltests on players.playerid = medicaltests.playerid where players.teamid = t.teamid and medicaltests.passedmedical = 1) AS playercount,
          t.rosterspots
     FROM TEAMS t
 GROUP BY t.teamid, t.teamname, t.rosterspots

这篇关于如何在我的SQL语句中联接第三个表，该表返回一个COUNT而不丢失0个计数项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！