SQL中GROUP BY的基本用法(Basic usage of GROUP BY in SQL)

我正在努力理解在这个查询中使用GROUP BY并且正在寻找澄清：

Flights(flno: integer, from: string, to: string, distance: integer, departs: time, arrives: time, price: real) Aircraft(aid: integer, aname: string, cruisingrange: integer) Certified(eid: integer, aid: integer) Employees(eid: integer, ename: string, salary: integer)

问题是：对于巡航范围超过1000英里的所有飞机，找到该飞机的名称和所有飞机认证的平均工资。

SELECT Temp.name, Temp.AvgSalary FROM ( SELECT A.aid, A.aname AS name, AVG (E.salary) AS AvgSalary FROM Aircraft A, Certified C, Employees E WHERE A.aid = C.aid AND C.eid = E.eid AND A.cruisingrange > 1000 GROUP BY A.aid, A.aname ) AS Temp

为什么这里需要GROUP BY ？以下查询不会返回飞机和相应的工资，还是会返回所有不属于每架飞机的员工的平均工资？

SELECT A.aname, AVG(E.salary) FROM Aircraft A, Certified C, Employees E WHERE A.aid = C.aid AND C.eid = E.eid AND A.cruisingrange > 1000

使用GROUP BY是否更改了表的格式，以便使用GROUP BY A.aid指定我们只对飞机表进行分组并保持认证和员工表不变？

I'm struggling to understand the use of GROUP BY in this query and am looking for clarification:

The question is: For all aircraft with cruising range over 1000 miles, find the name of the aircraft and the average salary of all pilots certified for this aircraft.

Why is the GROUP BY necessary here? Wouldn't the following query return the aircraft and the corresponding salary, or would it return the average salary of all employees not specific to each aircraft?

SELECT A.aname, AVG(E.salary) FROM Aircraft A, Certified C, Employees E WHERE A.aid = C.aid AND C.eid = E.eid AND A.cruisingrange > 1000

Does using GROUP BY change the format of the table so that using GROUP BY A.aid would specify that we are only grouping the aircraft table and leaving the certified and employee tables untouched?

最满意答案

GROUP BY需要正确地进行聚合（在这种情况下，取平均值）。

如果您不进行任何分组，MySQL将在整个表格上执行此聚合。换句话说，如果您使用了上次查询，它将返回巡航范围超过1000的所有飞机的平均工资，而不区分哪种飞机。试试吧，你会看到这种行为。

但是，如果您在此处使用GROUP BY子句，您将看到巡航范围超过1000的每架飞机的平均值，这是您想要的。没有它，你将获得所有飞机的平均值。

在一些示例数据上尝试这些查询，行为的差异将变得更加清晰。

编辑

关于你的最后几条陈述：是的，我们没有对认证或员工表做任何事情。退一步说，每架飞机都有问题。很多时候，如果给出一个问题陈述，说明您需要哪些项目结果，那么将它作为group by子句放置是一个良好的开端。

The GROUP BY is required to preform aggregation (in this case, taking the average) properly.

If you don't group by anything, MySQL will preform this aggregation over your entire table. In other words, if you used your last query it will return the average salary for all aircraft with a cruising range over 1000, with no distinction of which aircraft is which. Try it, and you will see this behavior.

However, if you use the GROUP BY clause here, you will see the average for each individual aircraft with a cruising range over 1000, which is what you want. Without it, you're taking the average of all aircraft.

Try these queries on some sample data, and the difference in behavior will become much more clear.

EDIT

Regarding your last few statements: yes, we are not doing anything with the certified or employee table. To step back, the problem states for each aircraft. Many times, if you are given a problem statement that spells out for which group of items you need results, it is a good start to place that as your group by clause.

更多推荐