用于比较历史记录表中的列中的当前值和先前值的SQL查询

  • Z时代
  • 2024-01-10
  • 分类：问答

我有一个SQL Server 2008数据库和历史记录表，用于在主表中记录更改。我需要报告'rate'列的当前（最新）值，以及最近一次与当前值不同的前一个值。用于比较历史记录表中的列中的当前值和先前值的SQL查询

因此，鉴于这样的：

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

回答：

对于基于我将在我的测试没有工作的方式有些道理的。我不得不这样添加previous.date在ROW_NUMBER顺序：

ROW_NUMBER() OVER (PARTITION BY curr.id, curr.status_id ORDER BY curr.row_created_date DESC, previous.row_created_date DESC) AS rn,

我的情况略有不同，我需要还可以去来回我的“地位”，因为它可能会改变。这是为我工作的代码。

DECLARE @mytemptable TABLE 
(
    tableid INT IDENTITY(1,1) PRIMARY KEY, 
    id INT, 
    status_id INT, 
    [user_id] INT, 
    row_created_date DATE 
) 
INSERT INTO @mytemptable VALUES (112266980, 1, 5, GETDATE()-21); 
INSERT INTO @mytemptable VALUES (112266980, 2, 5, GETDATE()-14); 
INSERT INTO @mytemptable VALUES (112266980, 3, 6, GETDATE()-7); 
INSERT INTO @mytemptable VALUES (112266980, 4, 8, GETDATE()); 
INSERT INTO @mytemptable VALUES (112277777, 1, 5, GETDATE()-21); 
INSERT INTO @mytemptable VALUES (112277777, 2, 5, GETDATE()-14); 
INSERT INTO @mytemptable VALUES (112277777, 3, 5, GETDATE()-6); 
INSERT INTO @mytemptable VALUES (112266666, 1, 5, GETDATE()-40); 
INSERT INTO @mytemptable VALUES (112266666, 2, 5, GETDATE()-30); 
INSERT INTO @mytemptable VALUES (112266666, 3, 5, GETDATE()-25); 
INSERT INTO @mytemptable VALUES (112266666, 2, 5, GETDATE()-20); 
SELECT * FROM @mytemptable ORDER BY id, row_created_date DESC 
;WITH statuses 
      AS (
       SELECT 
       ROW_NUMBER() OVER (PARTITION BY curr.id, curr.status_id, curr.row_created_date ORDER BY curr.row_created_date DESC, previous.row_created_date DESC) AS rn, 
       curr.id, 
       curr.status_id curr_status_id, 
       curr.user_id AS curr_user_id, 
       curr.row_created_date AS curr_datetime, 
       previous.status_id prev_status_id, 
       previous.user_id AS prev_user_id, 
       previous.row_created_date AS prev_datetime 
       FROM 
       @mytemptable AS curr 
       LEFT JOIN @mytemptable AS previous 
        ON curr.id = previous.id 
         AND curr.status_id <> previous.status_id 
         AND curr.row_created_date > previous.row_created_date 
      ) 
    SELECT 
     id, 
     curr_status_id, 
     curr_user_id, 
     curr_datetime, 
     prev_status_id, 
     prev_user_id, 
     prev_datetime 
    FROM 
     statuses 
    WHERE 
     rn = 1 
    ORDER BY 
     id, curr_datetime DESC

以上是 用于比较历史记录表中的列中的当前值和先前值的SQL查询 的全部内容， 来源链接： utcz.com/qa/266078.html

回到顶部