SELECT DISTINCT with LEFT JOIN, ORDERed BY in t-SQL

Question

我在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日期、由stOut按升序排序的所有记录的ID：

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

但它给了我一个错误：

如果select DISTINCT为指定

我试着把ORDER BY放在不同的地方，但似乎都不起作用。我在这里做错了什么？

Answer 1

通过不包含在DISTINCT项列表中的列进行ORDER是没有意义的。

让我们使用一个简单的场景。首先，一个表FruitPerson:

Fruit  PersonName
-----  ------
Apple  Joe
Apple  Mary
Peach  Karen
Peach  Lance

以下是与您尝试执行的操作等效的查询：

SELECT DISTINCT Fruit
FROM FruitPerson
ORDER BY PersonName;

没有ORDER BY的查询结果是：

Fruit
-----
Apple
Peach

但现在的问题是：引擎应该如何按照PersonName对"Apple"one_answers"Peach"这两个值进行排序哪些人名？每个水果有两个人！究竟应该使用哪个名称来决定Apple还是Peach排在第一位？

相反，将查询重写为聚合：

SELECT Fruit, MinName = Min(PersonName) -- which name to order on
FROM FruitPerson
GROUP BY Fruit -- here's how you get your distinctness
ORDER BY MinName;

Answer 2

当您将其缩小到单个id时，您会创建每个id可能有多个与其关联的dtOut的可能性。当这种情况发生时，Sql Server将如何知道使用哪个顺序？

你可以试试：

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的可能性。

Answer 3

您必须在选择列表中包括tbl.dtOut列，这样它才能知道要订购什么。

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

Answer 4

当使用"DISTINCT"关键字时，只能使用ORDER BY子句中的列，这些列是select语句的一部分。所以你只能通过tbl.id 订购

如果id是唯一的，DISTINCT关键字没有任何意义，会导致性能下降，您可以将其删除。如果它们不是唯一的，并且您决定在保留DISTINCT的同时也选择tbl.dtOut，则tbl.id和tbl.dtOut对可以为您提供多个具有相同tbl.id的条目，这可能是不需要的。

查看此文章以了解可能的解决方法和禁止此类查询的原因。

Answer 5

出现此错误是因为您没有将（tbl.dtOut）放入选择列表中。

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

将获得一个结果集（有3行）如果你也想要id字段，那么

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

（有6行，因为它给出了不同的组合off-id/date字段）