原因 - 如果指定了 SELECT DISTINCT，则 ORDER BY 项必须出现在选择列表中 [英] Reason for - ORDER BY items must appear in the select list if SELECT DISTINCT is specified

问题描述

我知道下面的查询会导致错误 - 如果指定了 SELECT DISTINCT，则 ORDER BY 项目必须出现在选择列表中.

I know that the query below causes the error - ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

SELECT DISTINCT city
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'
ORDER BY birthdate;

这是什么原因?实际发生了什么?如果我不使用 DISTINCT 或仅在 SELECT 或 ORDER BY city 中添加生日，它会给我一些输出，但不会出错.是不是因为 SELECT DISTINCT city 只给出了一个包含城市的结果集而没有其他内容?

What is the reason for this ? What is actually happening ? If I don't use DISTINCT or just add birthdate in SELECT or ORDER BY city only, it gives me some output, but not an error. Is it because SELECT DISTINCT city only gives a result set with cities and nothing else ?

编辑 -(我认为这可能是我问题的答案)

SELECT city
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'

考虑上面的查询.不确定，但我认为它是这样工作的 - 在幕后，SQL Server 可能有一个包含所有列的结果集，但我们只显示了 city 列，我将其称为显示集".因此，按城市或任何其他列排序是有效的.

Consider the above query. Not sure, but I think this is how it works - Behind the scenes, SQL Server probably has a result set with all columns, but we are only shown the city column which I will call the "displayed set" . So, ordering by city or any other column is valid.

当我们使用 SELECT DISTINCT city 时，结果集会发生什么变化?SQL Server 的结果集中不只有带有 DISTINCT 城市的行.它具有整个结果集，就像由 SELECT * 查询生成的结果集一样.但是，它仅显示基于城市的不同行.现在可以根据生日对显示的集合进行排序吗?号

What happens to the result set when we use SELECT DISTINCT city instead ? SQL server does not have only the rows with DISTINCT cities in its result set. It has the entire result set, like the one generated by a SELECT * query. But, it shows only distinct rows based on city. Now can this displayed set be sorted based on birthdate ? No.

在一个有很多员工的城市，即很多生日，SQL Server 无法判断应该使用哪个生日来对显示的集合进行排序.这就是它显示错误消息的原因.

In a city with many employees, ie many birthdates, SQL server cannot tell which birthdate should be used for ordering the displayed set. That is why it displays an error message.

推荐答案

可以使用 GROUP BY 重写带有 SELECT DISTINCT 的查询.所以查询:

A query with SELECT DISTINCT can be rewritten using GROUP BY. So the query:

SELECT DISTINCT city
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA' ;

相当于:

SELECT city
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'
GROUP BY city ;

并且您也不能在此处使用 ORDER BYbirthdate.两个查询的原因相同.可能有许多(不止一个)行具有相同的 city 但不同的 birthdate.应该使用哪一个进行排序(如果允许?)

and you can't use ORDER BY birthdate here either. The reason is the same for both queries. There may be many (more than one) rows with same city but different birthdate. Which one should be used for the ordering (if it was allowed?)

然而，您可以在 GROUP BY 查询中使用聚合函数:

You can however use aggregate functions with a GROUP BY query:

SELECT city
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'
GROUP BY city 
ORDER BY MIN(birthdate) ;               -- or MAX(birthdate)

这篇关于原因 - 如果指定了 SELECT DISTINCT，则 ORDER BY 项必须出现在选择列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！