Row_Number() 在分区上 - 更简单的方法 [英] Row_Number() Over Partition - Easier Way
本文介绍了Row_Number() 在分区上 - 更简单的方法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下查询:
INSERT into @TEST (col1,staffnumber,rn)
SELECT starttime as col1,staffnumber,ROW_NUMBER() OVER (PARTITION BY StaffNumber ORDER BY STARTTIME DESC) AS rn
FROM @TempTable2 t2
UNION
SELECT eventdate as col1, staffnumber,ROW_NUMBER() OVER (PARTITION BY StaffNumber ORDER BY EVENTDATE DESC) AS rn
FROM @TempTable1 t1
但是,显然,这给了我以下值:
But, obviously, this gives me the following values:
COL1 | staffnumber | rn
10/02/2014 10:29:24 1 1
10/02/2014 09:08:19 1 3
10/02/2014 10:00:23 1 2
10/02/2014 10:00:29 1 1
然而,我需要它显示:
COL1 | staffnumber | rn
10/02/2014 10:29:24 1 1
10/02/2014 09:08:19 1 4
10/02/2014 10:00:23 1 3
10/02/2014 10:00:29 1 2
我可以通过以下方式做到这一点:
I can do this by doing the following:
INSERT into @TEST1 (col1,staffnumber,rn)
SELECT convert(time,col1),staffnumber,ROW_NUMBER() OVER (PARTITION BY StaffNumber ORDER BY convert(time,COL1) DESC) AS rn
FROM @TEST
但是必须有一种更简单、更有效的方法来做到这一点.有什么想法吗?
But there must be an easier more efficient way of doing it. Any ideas?
推荐答案
计算UNION
之后的ROW_NUMBER
.
SELECT *, ROW_NUMBER() OVER (PARTITION BY StaffNumber ORDER BY STARTTIME DESC) AS rn
FROM (
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
) x
我怀疑你需要UNION ALL
.
这篇关于Row_Number() 在分区上 - 更简单的方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文