使用LEFT JOIN进行SELECT DISTINCT，在t-SQL中按ORDER BY [英] SELECT DISTINCT with LEFT JOIN, ORDERed BY in t-SQL

问题描述

我在SQL Server 2008中具有下表：

I have the following table in SQL Server 2008:

CREATE TABLE tbl (ID INT, dtIn DATETIME2, dtOut DATETIME2, Type INT)

INSERT tbl VALUES
(1, '05:00', '6:00', 1),
(2, '05:00', '7:00', 1),
(3, '05:01', '8:00', 1),
(4, '05:00', '8:00', 1),
(5, '05:00', '6:00', 2),
(6, '05:00', '7:00', 2)

，它选择具有相同dtIn日期的相同类型的所有记录的ID，并按stOut的升序排列：

that selects IDs of all records of the same type, with the same dtIn date, ordered by stOut in ascending order:

SELECT DISTINCT tbl.id FROM tbl   
  LEFT JOIN tbl AS t1
  ON tbl.type = t1.type AND
     tbl.dtIn = t1.dtIn
  ORDER BY tbl.dtOut ASC

但是这给了我一个错误：

But it gives me an error:

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

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

我尝试将ORDER BY放在不同的位置，但这似乎不起作用。我在这里做什么错了？

I tried putting that ORDER BY in different places and it all doesn't seem to work. What am I doing wrong here?

推荐答案

当您缩小单个ID的范围时，您就有可能每个ID可能会有更多与其关联的一个dtOut。发生这种情况时，Sql Server将如何知道要使用哪个顺序？

When you narrow it down individual id's, you create the possibility that each id might have more than one dtOut associated with it. When that happens, how will Sql Server know which order to use?

您可以尝试：

SELECT t1.id
FROM tbl t1
LEFT JOIN  tbl t2 on t1.type = t2.type AND t1.dtIn = t2.dtIn
GROUP BY t1.id, t2.dtOut
ORDER BY t2.dtOut

但是，正如我上面提到的如果匹配的ID大于右侧表中的记录，则可以多次列出具有相同ID的可能性。

However, as I mentioned above this can open the possibility of having the same id listed more than once, if it matches to more than record on the right-side table.

这篇关于使用LEFT JOIN进行SELECT DISTINCT，在t-SQL中按ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！