SQL -在一列上选择DISTINCT

Question

我有一张像这样的桌子

  ID  NAME     TL_NUM    TL_NAME      MANAGER_NUM
  ===============================================
  1   F-23      2         T-2         1
  2   B-23      3         T-3         2
  3   F-24      2         T-2         1
  4   F-25      1         T-1         2
  5   F-26      2         T-2         2 
  6   F-27      1         T-1         2

现在，我想要获得如下结果集

  TL_NUM    TL_NAME    MANAGER_NUM
  ================================
  2         T-2         1
  3         T-3         2
  1         T-1         2
  2         T-2         2 

我尝试使用以下查询

   select DISTINCT TL_NUM, TL_NAME,MANAGER_NUM
   from dataTable

当使用这个查询而不是上面的查询时，它给了我一个正确的结果集：

  select DISTINCT TL_NUM, TL_NAME
   from dataTable

但是它没有在我的结果集中返回正确的'MANAGER_NUM‘。谁能让我知道确切的问题是什么，我相信是因为DISTINCT？

Answer 1

您的查询看起来很好，工作正常，但根据输出结果排序不正确。我已经测试了该查询，它工作正常。

select DISTINCT TL_NUM, TL_NAME ,MANAGER_NUM from #dataTable order by MANAGER_NUM

Answer 2

经过测试，它确实返回了正确的值-您是否正确地查看了这些值？以下是用于创建表的工作版本的测试脚本：

CREATE TABLE #dataTable (id int identity, Name varchar(10), TL_NUM smallint, TL_NAME varchar(10), MANAGER_NUM smallint)
INSERT INTO #datatable (Name, TL_NUM, TL_NAME, MANAGER_NUM)
VALUES 
     ('F-23',      2,         'T-2',         1),
     ('B-23',      3,         'T-3',         2),
     ('F-24',      2,         'T-2',         1),
     ('F-25',      1,         'T-1',         2),
     ('F-26',      2,         'T-2',         2),
     ('F-27',      1,         'T-1',         2)

然后我使用了你的查询：

select DISTINCT TL_NUM, TL_NAME ,MANAGER_NUM
from #dataTable

并设置了以下返回值：

---------------------------------
|TL_NUM |TL_NAME    |MANAGER_NUM|
---------------------------------
|1      |T-1        |2          |
|2      |T-2        |1          |
|2      |T-2        |2          |
|3      |T-3        |2          |
---------------------------------

主要区别在于返回行的顺序，但返回的行具有正确的值。

Answer 3

看看我下面的小提琴：

DECLARE @tmp TABLE ( ID INT,NAME NVARCHAR(100),TL_NUM INT,TL_NAME NVARCHAR(100),MANAGER_NUM INT)

INSERT INTO @tmp
VALUES
(1, 'F-23', 2, 'T-2', 1)
,(2, 'B-23', 3, 'T-3', 2)
,(3, 'F-24', 2, 'T-2', 1)
,(4, 'F-25', 1, 'T-1', 2)
,(5, 'F-26', 2, 'T-2', 2)
,(6, 'F-27', 1, 'T-1', 2)

SELECT   DISTINCT t.TL_NUM ,t.TL_NAME ,t.MANAGER_NUM 
FROM     @tmp AS t
ORDER BY t.MANAGER_NUM
        ,t.TL_NAME DESC