分组方式和工作方式 [英] How does Group by and Having works

问题描述

我是SQL的新手，在编写了一些查询之后，我想了解SQL如何内部"处理查询.我从stackoverflow的另一则帖子中获取一个查询:

I am new to SQL and after writing some queries I wanted to understand how SQL "internally" processes the queries. I take one query from another post in stackoverflow:

select name from contacts
group by name
having count(*) > 1

我的问题是:按名称分组将具有相同名称的所有行合并为一行，然后如何计算知道有多少具有相同名称的行被合并.我正在尝试拆分查询处理的所有步骤，以了解它是如何工作的，但是在这种情况下，您似乎无法拆分它.预先感谢.

My question is: group by name merges all rows with the same name into one row, how does then count know how many rows with the same name were merged. I am trying to split all steps in the processing of the query in order to understand how it is exactly working, but in this case it seems like you cannot split it. Thanks in advance.

推荐答案

从显示在此处的sql查询中 执行顺序如下图所示

From your sql query that you show there the execution sequence will be like this show below

from contacts

知道要获取哪些表的数据，下一步将是您的WHERE子句，但是在这种情况下，您将没有子句，因此将继续进行下一步

knowing which tables's data you are getting, next will be your WHERE clause but in this case you don't have one so will follow to the next step which is

group by name

将所有相同的名称分组到一行记录中.

group all the same name to a row of record.

边注:现在，SELECT语句仍未运行，因此，当HAVING语句运行时，可以计算相同名称的行

side note: Now the SELECT statement still haven run yet, therefore when HAVING statement run can count the row that the same name has

下一个是你

having count(*) > 1

过滤掉所有计数超过1的记录，最后将成为SELECT

filter up all the record which count more than 1, and lastly will be the SELECT

select name

上面是显示示例的执行顺序.

above was the execute sequence for your example shown.

这些是完整的sql查询序列

And these is the full sequence of sql query

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

希望对您有帮助.

这篇关于分组方式和工作方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！