php - 在 Union 中结合 Distinct 和 TOP 1 | SQL 服务器 2012

Question

我有以下 SQL 查询：

SELECT DISTINCT LEFT (employeeidno, 4)deptcode 
from employeemasterfile 
ORDER BY deptcode ASC

产生一个DISTINCT值D001toD051

SELECT TOP 1 RIGHT (employeeidno, 7) empno 
from employeemasterfile  
ORDER BY empno DESC 

产生一个值0000267

我在这里想要实现的是 to 的每个DISTINCT值都D001应该D051产生它的TOP 1值。

这就是我想要达到的目标。

deptcode :D001; empno: 0000016

deptcode :D002; empno: 0000024

deptcode :D004; empno: 0000029

deptcode :D005; empno: 0000020

deptcode :D006; empno: 0000056

deptcode :D007; empno: 0000164

deptcode 将显示所有唯一的 D001-D007，empno 将显示每个唯一 deptcode 的 TOP1。

select deptcode, empno 
from
(
SELECT TOP 1 RIGHT (employeeidno, 7) empno from employeemasterfile  ORDER BY empno DESC
UNION
SELECT DISTINCT LEFT (employeeidno, 4)deptcode from employeemasterfile ORDER BY deptcode ASC
) results
ORDER BY deptcode

不幸的是我不能这样做，因为如果我要添加一个新参数，UNION 将不起作用。

我可以就这个问题寻求您的帮助吗？

示例数据如下所示

D0010000001
D0010000002
D0010000003
D0010000004
D0010000005
D0010000006
D0010000007
D0010000008
D0010000009
D0010000010
D0010000011
D0010000012
D0010000013
D0010000014
D0010000015
D0010000016
D0020000001
D0020000002
D0020000003
D0020000004
D0020000005
D0020000006
D0020000007
D0020000008
D0020000009
D0020000010
D0020000011
D0020000012
D0020000013
D0020000014
D0020000015
D0020000016
D0020000017
D0020000018
D0020000019

依此类推，直到 D0520000001

标签： phpsqlsql-serversqlsrv

Answer 1

你可以使用 row_number()

select deptcode,empno from
 ( select LEFT (employeeidno, 4) deptcode,
          RIGHT (employeeidno, 7) empno,
  row_number()over(partition by LEFT (employeeidno, 4) ordere by RIGHT (employeeidno, 7) desc)
 from employeemasterfile
) a where a.rn=1