SQL多重计数

Question

让我们来看这张表格：

[name] [type]
"Ken Anderson" 1
"John Smith" 2
"Bill Anderson" 1
"George Anderson" 1
"Taylor Smith" 1
"Andrew Anderson" 2
"Dominic Smith" 2

并且那个查询：

SELECT mates.type, COUNT(*) AS SmithsCount
FROM mates
WHERE mates.name LIKE "* Smith"
GROUP BY mates.type

结果应该是这样的：

[type] [SmithsCount]
1 1
2 2

如果我想在每个组中获取Andersons计数，该怎么办？例如：

[type] [SmithsCount] [AndersonsCount]
1 1 3
2 2 1

当然，我希望这个尽可能简单 ;) 我在SQL方面相当新手，我阅读了W3 Schools和http://www.sql-tutorial.net/的教程，但它们只提供了基础的例子，没有“更”复杂的查询。有人有一些有用的链接吗？谢谢。

- user659160

---

你所面临的问题不是SQL的问题，而是一个糟糕的设计。永远不要将名字和姓氏存储在同一个字段中，然后尝试按姓氏查询。当你使用通配符作为第一个字符时，无法使用索引。在学习解决查询问题之前，正确地学习如何避免这种错误。 - HLGEM

是的，我知道。那只是一个例子，并不是真正的问题。 - user659160

Answer 1

select type,
       sum(case when name like '% Smith' then 1 else 0 end) as SmithCount,
       sum(case when name like '% Anderson' then 1 else 0 end) as AndersonCount
    from mates
    group by type

- Joe Stefanelli

---

我正在使用MS Access（不是我的想法...），但这指向了“iif”语句，其工作方式相同。谢谢。 - user659160

Answer 2

你需要一个数据透视表。这是一些关系型数据库管理系统（如Oracle、SQLServer等）支持的功能。

数据透视表允许您将值用作聚合的列。请参阅我的帖子如何使用SQL将垂直数据转换为水平数据？。

数据透视表还将允许您获取列表中所有其他人的计数。

- Falcon

---

对于那些给我点踩的新手来说，他需要的是一个数据透视表，并且应该介绍这个概念给他。 - Falcon

Joe的解决方案对我来说很清晰，但那个数据透视表可能对其他事情有用。我会阅读相关资料，谢谢。 - user659160

Answer 3

你的查询接近正确，但是通配符字符应该使用 % 而不是 *。

select type,
    sum(case when name like '%Smith' then 1 else 0 end) as SmithCount,
    sum(case when name like '%Anderson' then 1 else 0 end) as AndersonCount
group by type

- bobs

Answer 4

在标准的SQL术语中，您提出的演示不支持此操作。

标准的SQL方法是先将数据规范化为mates.first_name、mates.last_name，然后执行以下操作：

SELECT mates.type, mates.last_name, COUNT(*) AS last_name_count
FROM mates
WHERE mates.last_name IN ('Smith', 'Anderson')
GROUP BY mates.type, mates.last_name

这将提供类似以下的输出

type last_name last_name_count
1    Anderson  3
1    Smith     1
2    Anderson  1
2    Smith     2

这是您正在寻找的相同信息，但格式/呈现方式不同。从历史上看，您应该在客户端应用程序中将这些数据进行透视/交叉表处理（作为演示层的一部分）。

当然，许多时候在SQL层面执行这种操作很有用或必要，因此对标准进行了扩展，例如pivot（MSSQL）或crosstab（postgres），等等...

- Unreason