如何获取一个计数为零的群组？

Question

我将尝试从我的SQL服务器数据库中的数据制作一个图表。我将列出所有街道，并统计居住在该街道的用户数量，即使数量为零。 为此，我尝试了以下查询：

Create table Streets(
  ID int IDENTITY  primary key,
  Name varchar(100)
);

create table users(
  ID int IDENTITY  primary key,
  Username varchar(100),
  StreetID int references Streets(id)
);

insert into streets values ('1st street'), ('2nd street'), ('3rd street'), 
                           ('4th street'), ('5th street');
insert into users values ('Pol', 1), ('Doortje', 1), ('Marc', 2), ('Bieke', 2), 
                         ('Paulien', 2), ('Fernand', 2), ('Pascal', 2), ('Boma', 3), 
                         ('Goedele', 3), ('Xavier', 4);

select s.name as street, count(s.name) as count 
from users u inner join streets s on u.streetid = s.id
group by s.name

而且它给我这个输出：

ID	街道	计数
1	第一街	2
2	第二街	5
3	第三街	2
4	第四街	1

问题是第五街没有用户居住，所以在结果中没有显示。我能用SQL服务器做到这一点吗？这里有一个fiddle供您参考

更新：如果我使用right join，我得到了以下结果：

ID	街道	数量
1	第一街	2
2	第二街	5
3	第三街	2
4	第四街	1
5	第五街	1

查看这个fiddle。

- H. Pauwelyn

---

由于没有人解释为什么您的查询没有返回预期结果：由于聚合函数忽略NULL值，您必须从内部表中计算一个列（而不是从外部表中计算），该列被定义为NOT NULL（以便能够区分数据中的NULL和外连接创建的NULL）。最简单的方法是计算连接列：COUNT(u.streetid)。 - dnoeth

因为 right join 和 right outer join 是同一回事。我按照 @jpmc26 的建议在我的答案中添加了解释。 - SqlWorldWide

Answer 1

你的查询没有按预期工作的原因是： 内连接（inner join）给出了两个表的交集。在你的情况下，你的用户表中没有“第五大街”的记录，这就是为什么连接没有产生任何关于它的记录的原因。 外连接（右连接或左连接）将给出内连接的结果，并且还会添加左侧或右侧表中所有不符合条件的记录，具体取决于外连接的类型（左连接或右连接）。 在这种情况下，我将“街道”放在连接的左侧，并使用左外连接，因为你希望在结果集中包含所有的街道（即使计数为零）。 将你的查询改为以下内容。

SELECT S.Name AS Street,
       Count(U.Username) AS COUNT
FROM Streets S
LEFT OUTER JOIN Users U ON U.Streetid = S.Id
GROUP BY S.Name

结果

- SqlWorldWide

---

对我来说，从count(*)改为count(customer.id) - 类似于上面所示的方式 - 产生了关键的差异。谢谢 :) - Zeek

只是注意一下，我在处理一个查询时遇到了类似的问题，就像上面的评论所说，将我的COUNT(*)更改为（在我的情况下）COUNT(LATEST_TEST_DATES.TEST_DATE)起作用，但实际上我并不需要LEFT OUTER JOIN，只需要一个LEFT JOIN（针对所有日期的列表）。 - KS1

Answer 2

这是一种可能的方式。

select s.name as streets,
       (select count(*)
        from   users
        where  StreetID = s.id) cnt
from   streets s;

- McNets

Answer 3

清理代码以适应区分大小写的实例...

CREATE TABLE Streets
(
    ID INT IDENTITY PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE users
(
    ID INT IDENTITY PRIMARY KEY,
    Username VARCHAR(100),
    StreetID INT
        REFERENCES Streets ( ID )
);

INSERT INTO Streets
VALUES ( '1st street' ),
    ( '2nd street' ),
    ( '3rd street' ),
    ( '4th street' ),
    ( '5th street' );
INSERT INTO users
VALUES ( 'Pol', 1 ),
    ( 'Doortje', 1 ),
    ( 'Marc', 2 ),
    ( 'Bieke', 2 ),
    ( 'Paulien', 2 ),
    ( 'Fernand', 2 ),
    ( 'Pascal', 2 ),
    ( 'Boma', 3 ),
    ( 'Goedele', 3 ),
    ( 'Xavier', 4 );

当你使用列名和 COUNT 一起时，它会计算 NOT NULL 值。 我在这里使用 RIGHT JOIN 来让 Joe Obbish 满意。

SELECT   s.Name AS street, COUNT(u.Username) AS count
FROM     users AS u
RIGHT JOIN Streets AS s
ON u.StreetID = s.ID
GROUP BY s.Name

结果：

街道	数量
第一街	2
第二街	5
第三街	2
第四街	1
第五街	0

- Erik Darling

Answer 4

1. 通过街道ID获取计数
2. 将街道ID与街道表中的ID进行连接
3. 使用Coalesce函数作为空值的替代

以下是简短的查询：

select Name, coalesce( u.ct,0)ct FROM streets s left join (
select StreetID,count(*)ct from users group by StreetID)u on s.ID=u.StreetID

- rakesh

---

这是使用左连接 - rakesh