mysql - 如果我的计数行为 0，查询不会返回结果

Question

我有一个用户数据库。我想返回信息，包括他们所在组的数量。我执行以下 SQL 查询

SELECT dbo.Users.UserId AS 'userId', 
       dbo.Users.UserName AS 'firstName', 
       dbo.Users.Surname AS 'surname', 
       dbo.Users.Photograph AS 'photograph', 
       dbo.Users.JobTitle AS 'jobTitle', 
       dbo.Users.EmailAddress AS 'emailAddress', 
       dbo.Users.Inactive AS 'inactive', 
       COUNT(dbo.UserGroups.userId) AS 'teams'
 FROM dbo.Users, 
       dbo.UserGroups
 WHERE dbo.Users.UserId = 27 
       AND dbo.UserGroups.UserId = dbo.Users.UserId
 GROUP BY dbo.Users.UserId, 
       dbo.Users.UserName, 
       dbo.Users.Surname, 
       dbo.Users.Photograph, 
       dbo.Users.JobTitle, 
       dbo.Users.EmailAddress, 
       dbo.Users.Inactive

这不会返回任何结果，因为用户 id 27 不在任何组中，而它应该显示其他信息，并且组/团队行应为 0。

这对于一个或多个组中的用户来说效果很好

Answer 1

也许因为您使用的是隐式 INNER JOIN...请使用显式 LEFT JOIN。

试试这个:

SELECT dbo.Users.UserId AS 'userId', 
       dbo.Users.UserName AS 'firstName', 
       dbo.Users.Surname AS 'surname', 
       dbo.Users.Photograph AS 'photograph', 
       dbo.Users.JobTitle AS 'jobTitle', 
       dbo.Users.EmailAddress AS 'emailAddress', 
       dbo.Users.Inactive AS 'inactive', 
       COALESCE(COUNT(dbo.UserGroups.userId), 0) AS 'teams'
 FROM dbo.Users left join
       dbo.UserGroups on dbo.UserGroups.UserId = dbo.Users.UserId
 WHERE dbo.Users.UserId = 27  
 GROUP BY dbo.Users.UserId, 
       dbo.Users.UserName, 
       dbo.Users.Surname, 
       dbo.Users.Photograph, 
       dbo.Users.JobTitle, 
       dbo.Users.EmailAddress, 
       dbo.Users.Inactive