数值为1的地方

Question

我得到了这个选择。我想数(或数？)黄牌和红牌(只在YellowCard = 1处计算黄牌，在RedCard =1时才算红牌)。此选择不能正确工作。它计数卡，即使有0或空..。我怎样才能改正呢？

SELECT Firstname, Lastname, COUNT(YellowCard) AS Yellow, COUNT(RedCard) AS Red, Team.Name 
FROM PlayerMatch 
Inner join Player On PlayerMatch.PlayerId = Player.PlayerId 
INNER JOIN Team ON Player.TeamId = Team.TeamId 
INNER JOIN Match ON PlayerMatch.MatchId = Match.MatchId 
WHERE(YellowCard = 1 OR RedCard = 1) 
GROUP BY Lastname, Firstname, Name 
ORDER BY Yellow DESC

Answer 1

使用SUM()而不是COUNT()

SELECT Firstname, Lastname,
       SUM(YellowCard) AS Yellow, SUM(RedCard) AS Red, t.Name 
FROM PlayerMatch pm Inner join
     Player p
     On pm.PlayerId = p.PlayerId INNER JOIN
     Team t
     ON p.TeamId = t.TeamId INNER JOIN
     Match m
     ON pm.MatchId = m.MatchId 
WHERE (YellowCard = 1 OR RedCard = 1) 
GROUP BY Lastname, Firstname, Name
ORDER BY Yellow DESC;

这假设YellowCard和RedCard的值总是0或1(或NULL)。这似乎是一个合理的假设。

如果情况并非如此，则使用CASE

SELECT Firstname, Lastname,
       SUM(CASE WHEN YellowCard = 1 THEN 1 ELSE 0 END) AS Yellow,
       SUM(CASE WHEN RedCard = 1 THEN 1 ELSE 0 END) AS Red,
       t.Name 

Answer 2

不，NULL不算在内。如果是YellowCard，RedCard只使用0,1,NULL值：

SELECT Firstname, Lastname, COUNT(nullif(YellowCard,0)) AS Yellow, COUNT(nullif(RedCard,0)) AS Red, Team.Name 
FROM PlayerMatch 
Inner join Player On PlayerMatch.PlayerId = Player.PlayerId 
INNER JOIN Team ON Player.TeamId = Team.TeamId 
INNER JOIN Match ON PlayerMatch.MatchId = Match.MatchId 
WHERE(YellowCard = 1 OR RedCard = 1) 
GROUP BY Lastname, Firstname, Name 
ORDER BY Yellow DESC

Answer 3

您不能这样做，因为在SQL中，它将从group & where子句开始，因此您的别名还不存在。

您必须使用SQL子查询作为本教程：http://www.dofactory.com/sql/subquery

如果您尝试:从PlayerMatch内部联接选择*.其中YellowCard in (从yourTable选择* YellowCard = 1)和RedCard in (从yourTable选择* RedCard =1)