在SQL查询中使用avg()函数只返回一行？

Question

我有这个SQL查询：

SELECT DISTINCT aname, avg(salary) 
FROM aircraft, certified, employees 
WHERE cruisingrange > 1000 AND ((certified.eid = employees.eid) = (aircraft.aid = certified.aid));

在一列中，我正在尝试显示巡航范围超过1000英里（cruisingrange> 1000）的所有飞机的飞机名称（aname）。在另一列中，我正在尝试显示已获得认证的飞行员（certified.eid = employees.eid）的平均工资（avg（salary）），他们被认证为特定飞机的飞行员（AND后的整个条件）。然而，它将所有工资合并为一个值并返回，因此我只得到了一个具有两列和一行的表格，而不是许多行（它仅显示一个飞机名称）。

这些SQL查询分别可以正常工作：

SELECT aname 
FROM aircraft 
WHERE cruisingrange > 1000;

SELECT avg(salary)
FROM employees, certified
WHERE employees.eid = certified.eid;

SELECT DISTINCT aname
FROM aircraft, certified
WHERE certified.aid = aircraft.aid;

我该如何编写一个查询来执行我想要的操作？今天我刚开始自学SQL，如果答案很明显，对不起。欢迎提出任何建议。

- Lefty.dev

---

如果你正在自学SQL，你可以选择一个比MySQL更标准的数据库。每个数据库都有它们的差异和怪癖，但MySQL在奇怪的行为方面是一个突出的例子，特别是在分组方面。我建议从正确的角度开始，选择Postgres或其中一个免费的SQL Server版本。 - LoztInSpace

@LoztInSpace 我明白了，我会调查一下。谢谢 :) - Lefty.dev

Answer 1

您正在使用MySQL，因此请正确使用GROUP BY：

SELECT aname, avg(salary) 
FROM aircraft a JOIN
     certified c
     ON a.aid = c.aid JOIN
     employees e
     ON c.eid = e.eid
WHERE cruisingrange > 1000 
GROUP BY aname;

根据您的查询，您似乎不太了解SQL。让我们从这里开始。以下是一些建议：

• 永远不要在FROM子句中使用逗号。始终使用适当的明确JOIN语法。
• 使用有意义的表别名（缩写）。
• 限定所有列名（我无法在上面的查询中这样做，因为我不知道列来自哪里）。
• 在理解SQL之前不要使用DISTINCT。它对性能产生巨大影响，并且有时会导致奇怪的意外结果。

因此，您的查询出现了什么问题？基本上，可以考虑以下相关查询：

SELECT aname, avg(salary) 
FROM aircraft a JOIN
     certified c
     ON a.aid = c.aid JOIN
     employees e
     ON c.eid = e.eid
WHERE cruisingrange > 1000 ;

它会返回多少行呢？在大多数数据库中，它将返回0行和错误。这是一个聚合查询，但SQL引擎不知道如何处理aname。

MySQL允许此语法，返回一行（因为它是没有GROUP BY的聚合查询）。aname的值取自一个不确定的行。

因此，当您向SELECT添加DISTINCT时，它没有任何效果。查询已经仅返回一行，因此根据定义，是唯一的。

- Gordon Linoff

---

这对我帮助很大，谢谢！你说得对，我在SQL方面非常新手和糟糕。所以我会学习关于连接和分组的知识。 - Lefty.dev