MySQL sql中GROUP BY和ORDER BY有什么区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1277460/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
what is the difference between GROUP BY and ORDER BY in sql
提问by vehomzzz
When do you use which in general? Examples are highly encouraged!
你一般什么时候使用哪个？非常鼓励示例！
I am referring so MySql, but can't imagine the concept being different on another DBMS
我指的是 MySql，但无法想象这个概念在另一个 DBMS 上会有所不同
采纳答案by CMerat
ORDER BY alters the order in which items are returned.
ORDER BY 更改项目返回的顺序。
GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc).
GROUP BY 将按指定的列聚合记录，这允许您对非分组列（例如 SUM、COUNT、AVG 等）执行聚合功能。
回答by RiddlerDev
ORDER BY alters the order in which items are returned.
ORDER BY 更改项目返回的顺序。
GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc).
GROUP BY 将按指定的列聚合记录，这允许您对非分组列（例如 SUM、COUNT、AVG 等）执行聚合功能。
TABLE:
ID NAME
1 Peter
2 John
3 Greg
4 Peter
SELECT *
FROM TABLE
ORDER BY NAME
=
3 Greg
2 John
1 Peter
4 Peter
SELECT Count(ID), NAME
FROM TABLE
GROUP BY NAME
=
1 Greg
1 John
2 Peter
SELECT NAME
FROM TABLE
GROUP BY NAME
HAVING Count(ID) > 1
=
Peter
回答by JerryGoyal
ORDER BY:sort the data in ascending or descending order.
ORDER BY：按升序或降序对数据进行排序。
Consider the CUSTOMERStable:
考虑CUSTOMERS表：
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would sort the result in ascending order by NAME:
以下是一个示例，它将按 NAME 按升序对结果进行排序：
SQL> SELECT * FROM CUSTOMERS
ORDER BY NAME;
This would produce the following result:
这将产生以下结果：
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+
GROUP BY:arrange identical data into groups.
GROUP BY：将相同的数据分组。
Now, CUSTOMERStable has the following records with duplicate names:
现在，CUSTOMERS表具有以下重复名称的记录：
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Ramesh | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | kaushik | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
if you want to group identical names into single name, then GROUP BY query would be as follows:
如果要将相同的名称分组为单个名称，则 GROUP BY 查询将如下所示：
SQL> SELECT * FROM CUSTOMERS
GROUP BY NAME;
This would produce the following result: (for identical names it would pick the last one and finally sort the column in ascending order)
这将产生以下结果：（对于相同的名称，它将选择最后一个并最终按升序对列进行排序）
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 4 | kaushik | 25 | Mumbai | 6500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 2 | Ramesh | 25 | Delhi | 1500.00 |
+----+----------+-----+-----------+----------+
as you have inferred that it is of no use without SQL functions like sum,avg etc..
正如您所推断的那样，如果没有 sum、avg 等 SQL 函数，它就没有用处。
so go through this definition to understand the proper use of GROUP BY:
所以通过这个定义来理解 GROUP BY 的正确使用：
A GROUP BY clause works on the rows returned by a query by summarizing identical rows into a single/distinct group and returns a single row with the summary for each group, by using appropriate Aggregate function in the SELECT list, like COUNT(), SUM(), MIN(), MAX(), AVG(), etc.
GROUP BY 子句通过将相同的行汇总到单个/不同的组中来处理查询返回的行，并通过在 SELECT 列表中使用适当的聚合函数（如 COUNT()、SUM）返回带有每个组摘要的单行()、MIN()、MAX()、AVG() 等
Now, if you want to know the total amount of salary on each customer(name), then GROUP BY query would be as follows:
现在，如果您想知道每个客户（姓名）的工资总额，那么 GROUP BY 查询将如下所示：
SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
GROUP BY NAME;
This would produce the following result: (sum of the salaries of identical names and sort the NAME column after removing identical names)
这将产生以下结果：（相同姓名的工资之和，并在删除相同姓名后对 NAME 列进行排序）
+---------+-------------+
| NAME | SUM(SALARY) |
+---------+-------------+
| Hardik | 8500.00 |
| kaushik | 8500.00 |
| Komal | 4500.00 |
| Muffy | 10000.00 |
| Ramesh | 3500.00 |
+---------+-------------+
回答by Max Schmeling
The difference is exactly what the name implies: a group by performs a grouping operation, and an order by sorts.
区别正是顾名思义：group by 执行分组操作，而 order by 排序。
If you do SELECT * FROM Customers ORDER BY Namethen you get the result list sorted by the customers name.
如果你这样做，SELECT * FROM Customers ORDER BY Name你会得到按客户名称排序的结果列表。
If you do SELECT IsActive, COUNT(*) FROM Customers GROUP BY IsActiveyou get a count of active and inactive customers. The group by aggregated the results based on the field you specified.
如果你这样做，SELECT IsActive, COUNT(*) FROM Customers GROUP BY IsActive你会得到活跃和不活跃客户的数量。group by 根据您指定的字段聚合结果。
回答by PatrikAkerstrand
They have totally different meaning and aren't really related at all.
它们有完全不同的含义，根本没有真正的关系。
ORDER BYallows you to sort the result set according to different criteria, such as first sort by name from a-z, then sort by the price highest to lowest.
ORDER BY允许您根据不同的条件对结果集进行排序，例如先从 az 按名称排序，然后按价格从高到低排序。
(ORDER BY name, price DESC)
（按名称订购，价格 DESC）
GROUP BYallows you to take your result set, group it into logical groups and then run aggregate queries on those groups. You could for instance select all employees, group them by their workplace location and calculate the average salary of all employees of each workplace location.
GROUP BY允许您获取结果集，将其分组为逻辑组，然后对这些组运行聚合查询。例如，您可以选择所有员工，按工作地点对他们进行分组，然后计算每个工作地点所有员工的平均工资。
回答by Swapnil Chincholkar
Simple, ORDER BYorders the data and GROUP BYgroups, or combines the data.
简单，ORDER BY对数据进行排序和GROUP BY分组，或组合数据。
ORDER BYorders the result set as per the mentioned field, by default in ascending order.
ORDER BY根据提到的字段对结果集进行排序，默认情况下按升序排列。
Suppose you are firing a query as ORDER BY (student_roll_number), it will show you result in ascending order of student's roll numbers. Here, student_roll_numberentry might occur more than once.
假设您将查询作为 触发ORDER BY (student_roll_number)，它将以学生卷号的升序显示结果。在这里，student_roll_number进入可能发生不止一次。
In GROUP BYcase, we use this with aggregate functions, and it groups the data as per the aggregate function, and we get the result. Here, if our query has SUM (marks)along with GROUP BY (student_first_name)it will show the sum of marks of students belonging to each group (where all members of a group will have the same first name).
在GROUP BY情况下，我们使用这个具有聚合函数，它组数据按聚合函数，我们得到的结果。在这里，如果我们的查询SUM (marks)带有GROUP BY (student_first_name)它，它将显示属于每个组的学生的分数总和（其中一个组的所有成员将具有相同的名字）。
回答by kevchadders
Some good examples there. Just like to add my own from webcheatsheet which gives good clear examples, as well as letting you execute your own SQL.
那里有一些很好的例子。就像从 webcheatsheet 添加我自己的，它提供了很好的清晰示例，以及让您执行自己的 SQL。
回答by C?t?lin Piti?
GROUP BY is used to group rows in a select, usually when aggregating rows (e.g. calculating totals, averages, etc. for a set of rows with the same values for some fields).
GROUP BY 用于在选择中对行进行分组，通常在聚合行时（例如，为某些字段具有相同值的一组行计算总计、平均值等）。
ORDER BY is used to order the rows resulted from a select statement.
ORDER BY 用于对 select 语句产生的行进行排序。
回答by Im_khan
ORDER BYshows a field in ascending or descending order. While GROUP BYshows same fieldnames, id's etc in only one output.
ORDER BY按升序或降序显示字段。虽然GROUP BY仅在一个输出中显示相同的字段名、id 等。
回答by Khadija
- GROUP BY will aggregate records by the specified column which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc.). ORDER BY alters the order in which items are returned.
- If you do SELECT IsActive, COUNT(*) FROM Customers GROUP BY IsActive you get a count of active and inactive customers. The group by aggregated the results based on the field you specified. If you do SELECT * FROM Customers ORDER BY Name then you get the result list sorted by the customer's name.
- If you GROUP, the results are not necessarily sorted; although in many cases they may come out in an intuitive order, that's not guaranteed by the GROUP clause. If you want your groups sorted, always use an explicitly ORDER BY after the GROUP BY.
- Grouped data cannot be filtered by WHERE clause. Order data can be filtered by WHERE clause.
- GROUP BY 将按指定列聚合记录，这允许您对非分组列（如 SUM、COUNT、AVG 等）执行聚合功能。ORDER BY 更改项目返回的顺序。
- 如果您执行 SELECT IsActive, COUNT(*) FROM Customers GROUP BY IsActive，您将获得活跃和非活跃客户的计数。group by 根据您指定的字段聚合结果。如果您执行 SELECT * FROM Customers ORDER BY Name，那么您将获得按客户姓名排序的结果列表。
- 如果是GROUP，结果不一定是排序好的；尽管在许多情况下它们可能以直观的顺序出现，但 GROUP 子句不能保证这一点。如果您希望对组进行排序，请始终在 GROUP BY 之后使用明确的 ORDER BY。
- WHERE 子句不能过滤分组数据。订单数据可以通过 WHERE 子句过滤。
