我有一个类似的sql查询:
SELECT Name, Text as Value, UpdateDate from A WHERE UserID = @userId
UNION
SELECT Name, STR(Value), UpdateDate FROM B WHERE UserID = @userId
表CCD_ 1和CCD_。如果它们都有一个名称,我只希望我的结果集中有最新的一行
目前,我的结果集看起来像这样:
Name | Value | UpdateDate
Foo '23' Jan-1-2012
Foo 'A' Feb-7-2013
Bar '42' Jan-3-2011
我想把它浓缩为:
Name | Value
Foo 'A'
Bar '42'
我意识到我可以在客户端中很容易地做到这一点,但如果某种Group-By
魔法可以在一个查询中做到这一点将非常棒。希望它也不会太贵。
WITH unionTB
AS
(
SELECT Name, Text as Value, UpdateDate
from A WHERE UserID = @userId
UNION
SELECT Name, STR(Value), UpdateDate
FROM B WHERE UserID = @userId
),
recordList
AS
(
SELECT Name, Value, UpdateDate,
ROW_NUMBER() OVER (PARTITION BY Name
ORDER BY UpdateDate DESC) rn
FROM unionTB
)
SELECT Name, Value, UpdateDate
FROM recordList
WHERE rn = 1
- SQLFiddle演示