SQL 如果指定了 SELECT DISTINCT，则 ORDER BY 项必须出现在选择列表中

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

提问by Xaisoft

I added the columns in the select list to the order by list, but it is still giving me the error:

我将选择列表中的列添加到 order by 列表中，但它仍然给我错误：

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

如果指定了 SELECT DISTINCT，则 ORDER BY 项必须出现在选择列表中。

Here is the stored proc:

这是存储的过程：

CREATE PROCEDURE [dbo].[GetRadioServiceCodesINGroup] 
@RadioServiceGroup nvarchar(1000) = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT DISTINCT rsc.RadioServiceCodeId,
                rsc.RadioServiceCode + ' - ' + rsc.RadioService as RadioService
FROM sbi_l_radioservicecodes rsc
INNER JOIN sbi_l_radioservicecodegroups rscg 
ON rsc.radioservicecodeid = rscg.radioservicecodeid
WHERE rscg.radioservicegroupid IN 
(select val from dbo.fnParseArray(@RadioServiceGroup,','))
OR @RadioServiceGroup IS NULL  
ORDER BY rsc.RadioServiceCode,rsc.RadioServiceCodeId,rsc.RadioService

END

采纳答案by Chris Van Opstal

Try this:

尝试这个：

ORDER BY 1, 2

OR

或者

ORDER BY rsc.RadioServiceCodeId, rsc.RadioServiceCode + ' - ' + rsc.RadioService

回答by Joel Coehoorn

While they are not the same thing, in one sense DISTINCTimplies a GROUP BY, because every DISTINCTcould be re-written using GROUP BYinstead. With that in mind, it doesn't make sense to order by something that's not in the aggregate group.

虽然它们不是一回事，但在某种意义上DISTINCT意味着 a GROUP BY，因为每个DISTINCT都可以改写GROUP BY。考虑到这一点，按不在聚合组中的东西排序是没有意义的。

For example, if you have a table like this:

例如，如果您有一个这样的表：

col1  col2
----  ----
 1     1
 1     2
 2     1
 2     2
 2     3
 3     1

and then try to query it like this:

然后尝试像这样查询它：

SELECT DISTINCT col1 FROM [table] WHERE col2 > 2 ORDER BY col1, col2

That would make no sense, because there couldend up being multiple col2values per row. Which one should it use for the order? Of course, in this query you know the results wouldn't be that way, but the database server can't know that in advance.

那没有意义，因为最终每行可能有多个col2值。它应该用于订单中的哪一个？当然，在这个查询中你知道结果不会那样，但是数据库服务器不能提前知道。

Now, your case is a little different. You included all the columns from the order byclause in the selectclause, and therefore it would seem at first glance that they were all grouped. However, some of those columns were included in a calculated field. When you do that in combination with distinct, the distinctdirective can onlybe applied to the final resultsof the calculation: it doesn't know anything about the source of the calculation any more.

现在，你的情况有点不同。您将order by子句中的所有列都包含在select子句中，因此乍一看，它们似乎都已分组。但是，其中一些列包含在计算字段中。当您结合 distinct 执行此操作时，该distinct指令只能应用于计算的最终结果：它不再了解有关计算来源的任何信息。

This means the server doesn't really know it can count on those columns any more. It knows that they were used, but it doesn't know if the calculation operation might cause an effect similar to my first simple example above.

这意味着服务器并不真正知道它可以再依赖这些列。它知道它们被使用了，但它不知道计算操作是否会导致类似于我上面第一个简单示例的效果。

So now you need to do something else to tell the server that the columns are okay to use for ordering. There are several ways to do that, but this approach should work okay:

所以现在你需要做一些其他的事情来告诉服务器这些列可以用于排序。有几种方法可以做到这一点，但这种方法应该可以正常工作：

SELECT rsc.RadioServiceCodeId,
            rsc.RadioServiceCode + ' - ' + rsc.RadioService as RadioService
FROM sbi_l_radioservicecodes rsc
INNER JOIN sbi_l_radioservicecodegroups rscg 
    ON rsc.radioservicecodeid = rscg.radioservicecodeid
WHERE rscg.radioservicegroupid IN 
    (SELECT val FROM dbo.fnParseArray(@RadioServiceGroup,','))
    OR @RadioServiceGroup IS NULL  
GROUP BY rsc.RadioServiceCode,rsc.RadioServiceCodeId,rsc.RadioService
ORDER BY rsc.RadioServiceCode,rsc.RadioServiceCodeId,rsc.RadioService

回答by Tony Andrews

Try one of these:

尝试以下方法之一：

1. Use column alias:

   ORDER BY RadioServiceCodeId,RadioService

2. Use column position:

   ORDER BY 1,2

1. 使用列别名：

   ORDER BY RadioServiceCodeId,RadioService

2. 使用列位置：

   按 1,2 排序

You can only order by columns that actually appear in the result of the DISTINCT query - the underlying data isn't available for ordering on.

您只能按实际出现在 DISTINCT 查询结果中的列进行排序 - 基础数据不可用于排序。

回答by Charles Bretana

Distinct and Group By generally do the same kind of thing, for different purposes... They both create a 'working" table in memory based on the columns being Grouped on, (or selected in the Select Distinct clause) - and then populate that working table as the query reads data, adding a new "row" only when the values indicate the need to do so...

Distinct 和 Group By 通常做同样的事情，出于不同的目的......它们都基于被分组的列在内存中创建一个“工作”表，（或在 Select Distinct 子句中选择） - 然后填充它查询读取数据时的工作表，仅当值指示需要这样做时才添加新的“行”...

The only difference is that in the Group By there are additional "columns" in the working table for any calculated aggregate fields, like Sum(), Count(), Avg(), etc. that need to updated for each original row read. Distinct doesn't have to do this... In the special case where you Group By only to get distinct values, (And there are no aggregate columns in output), then it is probably exactly the same query plan.... It would be interesting to review the query execution plan for the two options and see what it did...

唯一的区别是，在 Group By 中，任何计算聚合字段（如 Sum()、Count()、Avg() 等）的工作表中都有额外的“列”，这些字段需要针对每个原始行读取进行更新。Distinct 不必这样做...在特殊情况下，您 Group By 只是为了获得不同的值，（并且输出中没有聚合列），那么它可能是完全相同的查询计划......它查看两个选项的查询执行计划并看看它做了什么会很有趣......

Certainly Distinct is the way to go for readability if that is what you are doing (When your purpose is to eliminate duplicate rows, and you are not calculating any aggregate columns)

如果您正在这样做，当然 Distinct 是提高可读性的方法（当您的目的是消除重复行，并且您没有计算任何聚合列时）

回答by rio

When you define concatenation you need to use an ALIAS for the new column if you want to order on it combined with DISTINCT Some Ex with sql 2008

当您定义连接时，如果您想在新列上使用 ALIAS 并结合 DISTINCT Some Ex 和 sql 2008对其进行排序

--this works 

    SELECT DISTINCT (c.FirstName + ' ' + c.LastName) as FullName 
    from SalesLT.Customer c 
    order by FullName

--this works too

    SELECT DISTINCT (c.FirstName + ' ' + c.LastName) 
    from SalesLT.Customer c 
    order by 1

-- this doesn't 

    SELECT DISTINCT (c.FirstName + ' ' + c.LastName) as FullName 
    from SalesLT.Customer c 
    order by c.FirstName, c.LastName

-- the problem the DISTINCT needs an order on the new concatenated column, here I order on the singular column
-- this works

    SELECT DISTINCT (c.FirstName + ' ' + c.LastName) 
        as FullName, CustomerID 
        from SalesLT.Customer c 

order by 1, CustomerID

-- this doesn't

    SELECT DISTINCT (c.FirstName + ' ' + c.LastName) as FullName 
     from SalesLT.Customer c 
      order by 1, CustomerID

回答by HenryS

You could try a subquery:

您可以尝试一个子查询：

SELECT DISTINCT TEST.* FROM (
    SELECT rsc.RadioServiceCodeId,
        rsc.RadioServiceCode + ' - ' + rsc.RadioService as RadioService
    FROM sbi_l_radioservicecodes rsc
       INNER JOIN sbi_l_radioservicecodegroups rscg ON  rsc.radioservicecodeid = rscg.radioservicecodeid
    WHERE rscg.radioservicegroupid IN 
       (select val from dbo.fnParseArray(@RadioServiceGroup,','))
        OR @RadioServiceGroup IS NULL  
    ORDER BY rsc.RadioServiceCode,rsc.RadioServiceCodeId,rsc.RadioService
) as TEST