时间戳

首页 > 解决方案 > 如果我的计数行为 0，则查询不返回任何结果

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

问题描述

我有一个用户数据库。我想返回信息，包括他们所在的组数量。我执行以下 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 时。

这适用于一个组或多个组中的用户

标签： mysqlsql

解决方案

可能是因为您使用的是隐式 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

推荐阅读