如何使用T-SQL组By

I know I need to have (although I don't know why) a GROUP BY clause on the end of a SQL query that uses any aggregate functions like count, sum, avg, etc:

我知道我需要(尽管我不知道为什么)在SQL查询的末尾有一个GROUP BY子句，它使用任何聚合函数，如count、sum、avg等:

SELECT count(userID), userName
FROM users
GROUP BY userName

When else would GROUP BY be useful, and what are the performance ramifications?

其他什么时候分组是有用的，性能的影响是什么?

5 个解决方案

#1

To retrieve the number of widgets from each widget category that has more than 5 widgets, you could do this:

要从包含5个以上小部件的每个小部件类别中检索小部件的数量，您可以这样做:

SELECT WidgetCategory, count(*)
FROM Widgets
GROUP BY WidgetCategory
HAVING count(*) > 5

The "having" clause is something people often forget about, instead opting to retrieve all their data to the client and iterating through it there.

“拥有”子句是人们经常忘记的东西，而不是选择将所有数据检索到客户端并在那里进行迭代。

#2

GROUP BY is similar to DISTINCT in that it groups multiple records into one.

GROUP BY类似于DISTINCT，它将多个记录分组为一个记录。

This example, borrowed from http://www.devguru.com/technologies/t-sql/7080.asp, lists distinct products in the Products table.

这个例子来自http://www.devguru.com/technologies/t-sql/7080.asp，列出了产品表中不同的产品。

SELECT Product FROM Products GROUP BY Product

Product
-------------
Desktop
Laptop
Mouse
Network Card
Hard Drive
Software
Book
Accessory

The advantage of GROUP BY over DISTINCT, is that it can give you granular control when used with a HAVING clause.

GROUP BY over DISTINCT的优点是，它可以在使用HAVING子句时给您粒度控制。

SELECT Product, count(Product) as ProdCnt
FROM Products
GROUP BY Product
HAVING count(Product) > 2

Product      ProdCnt
--------------------
Desktop          10
Laptop            5
Mouse             3
Network Card      9
Software          6

#3

Group By forces the entire set to be populated before records are returned (since it is an implicit sort).

Group By强制在返回记录之前填充整个集合(因为它是隐式排序)。

For that reason (and many others), never use a Group By in a subquery.

出于这个原因(以及其他许多原因)，不要在子查询中使用组。

#4

Counting the number of times tags are used might be a google example:

计算使用标记的次数可能是谷歌示例:

SELECT TagName, Count(*)
AS TimesUsed
FROM Tags
GROUP BY TagName ORDER TimesUsed

If you simply want a distinct value of tags, I would prefer to use the DISTINCT statement.

如果您只想要一个不同的标记值，我宁愿使用不同的语句。

SELECT DISTINCT TagName
FROM Tags
ORDER BY TagName ASC

#5

GROUP BY also helps when you want to generate a report that will average or sum a bunch of data. You can GROUP By the Department ID and the SUM all the sales revenue or AVG the count of sales for each month.

当您想要生成一个平均或汇总一堆数据的报告时，GROUP BY也会有所帮助。您可以根据部门ID和所有销售收入的总和或AVG对每个月的销售额进行分组。

#1