我有一个带有历史记录表的SQL Server 2008数据库，用于在主表中记录更改。我需要报告“费率”列的当前(最新)值，以及与当前不同的最新的先前值。
因此，给定类似:
id | rate | uninteresting | updated_on | version
-----+--------+---------------+--------------+----------
123 | 1.20 | foo | 2010-10-18 | 1500
456 | 2.10 | bar | 2010-10-12 | 2123
123 | 1.20 | baz | 2010-10-10 | 1499
123 | 1.10 | baz | 2010-10-08 | 1498
456 | 2.00 | bar | 2010-10-11 | 2122
123 | 1.00 | baz | 2010-08-01 | 1497
456 | 2.00 | quux | 2010-10-05 | 2121
456 | 1.95 | quux | 2010-09-07 | 2120
我想生产:
id | cur_rate | cur_ver | updated_on | prev_rate | prev_ver | prev_updated
-----+----------+---------+------------+-----------+----------+-------------
123 | 1.20 | 1500 | 2010-10-18 | 1.10 | 1498 | 2010-10-08
456 | 2.10 | 2123 | 2010-10-12 | 2.00 | 2122 | 2010-10-11
请注意，我正在寻找速率与最新条目不同的最新条目。
我尝试了各种方法，但是要么获得太多结果，要么根本没有结果。有什么建议么？
最佳答案
有两种方法可以实现此目的。这是一种方法
Declare @table as table(
id int,
rate decimal(10,5) ,
uninteresting varchar(10) ,
updated_on date,
version int )
INSERT INTO @table
VALUES
(123 , 1.20 , 'foo ' , '2010-10-18' , 1500),
(456, 2.1, ' bar ', ' 2010-10-12 ', 2123),
(123, 1.2, ' baz ', ' 2010-10-10 ', 1499),
(123, 1.1, ' baz ', ' 2010-10-08 ', 1498),
(456, 2, ' bar ', ' 2010-10-11 ', 2122),
(123, 1, ' baz ', ' 2010-08-01 ', 1497),
(456, 2, ' quux ', ' 2010-10-05 ', 2121),
(456, 1.95, ' quux ', ' 2010-09-07 ', 2120)
;WITH rates
AS (SELECT Row_number() OVER ( PARTITION BY curr.id, curr.rate ORDER BY curr.updated_on DESC) AS rn,
curr.id,
curr.rate cur_rate,
curr.version cur_ver,
curr.updated_on,
previous.rate prev_rate,
previous.version prev_ver,
previous.updated_on prev_updated
FROM
@table curr
LEFT JOIN @table previous
ON curr.id = previous.id
AND curr.rate <> previous.rate
AND curr.updated_on > previous.updated_on
)
SELECT
id,
cur_rate,
cur_ver,
updated_on,
prev_rate,
prev_ver,
prev_updated
FROM
rates
WHERE
rn = 1
产生这个结果
id cur_rate cur_ver updated_on prev_rate prev_ver prev_updated
----------- -------- ----------- ---------- --------- ----------- ------------
123 1.00000 1497 2010-08-01 NULL NULL NULL
123 1.10000 1498 2010-10-08 1.00000 1497 2010-08-01
123 1.20000 1500 2010-10-18 1.10000 1498 2010-10-08
456 1.95000 2120 2010-09-07 NULL NULL NULL
456 2.00000 2122 2010-10-11 1.95000 2120 2010-09-07
456 2.10000 2123 2010-10-12 2.00000 2122 2010-10-11
如果您更改rn以将分区中的速率降低例如
( PARTITION BY curr.id ORDER BY curr.updated_on DESC) AS rn,你得到
id cur_rate cur_ver updated_on prev_rate prev_ver prev_updated
----------- -------- ----------- ---------- --------- ----------- ------------
123 1.20000 1500 2010-10-18 1.10000 1498 2010-10-08
456 2.10000 2123 2010-10-12 2.00000 2122 2010-10-11