如何跟踪 SQL Server 中记录的变化? [英] How to track changes in records in SQL Server?

问题描述

我有下表，里面有所有学生的跟踪记录.

I have the following table which has tracking records of all students.

|==========================================|
| ID      |  Department     | Date         |
|==========================================|
| 001     | English         | Feb 3 2017   |
| 001     | English         | Feb 4 2017   |
| 001     | Science         | Mar 1 2017   |
| 001     | Science         | Apr 2 2017   |
| 001     | Maths           | Apr 7 2017   |
| 002     | Maths           | Feb 1 2017   |
| 002     | Maths           | Apr 7 2017   |
| 003     | Maths           | Apr 3 2017   |
| 004     | Science         | Feb 1 2017   |
| 004     | Maths           | Apr 7 2017   |
|==========================================| 

我需要在学生换部门之前获取之前的记录.对于上面的例子，返回的记录集应该是

I need to fetch the previous record just before when the student has changed the department. For the example above, the record set returned should be

对于 001，

| 001     | English         | Feb 4 2017   |
| 001     | Science         | Apr 2 2017   |

对于 002 和 003

没有变化

对于 004

| 004     | Science         | Feb 1 2017   |

同样的用户也有可能变回同一个部门.例如，user001 可以从部门 a 更改为部门 b 再更改为部门 c，然后再更改为部门 a.我已阅读有关 T-SQL 发送和接收的信息.但不确定在这种情况下是否会有所帮助.请帮忙.

There is also a possibility that the same user can change back to the same department. for example, user001 can change from dept a to dept b to dept c and back to dept a. I have read about T-SQL send and receive. But not sure if that would help in this scenario. Please help.

推荐答案

一种方法是使用带有分区的 ROW_NUMBER 函数来检测 Department 列更改.

One way to do it is to use ROW_NUMBER function with partitioning to detect when the value of the Department column changes.

示例数据

DECLARE @T TABLE (ID int, Department nvarchar(100), dt date);
INSERT INTO @T (ID, Department, dt) VALUES
(1, 'English', 'Feb 3 2017'),
(1, 'English', 'Feb 4 2017'),
(1, 'Science', 'Mar 1 2017'),
(1, 'Science', 'Apr 2 2017'),
(1, 'Maths  ', 'Apr 7 2017'),
(2, 'Maths  ', 'Feb 1 2017'),
(2, 'Maths  ', 'Apr 7 2017'),
(3, 'Maths  ', 'Apr 3 2017'),
(4, 'Science', 'Feb 1 2017'),
(4, 'Maths  ', 'Apr 7 2017');

查询

WITH
CTE
AS
(
    SELECT
        ID
        ,Department
        ,dt
        ,ROW_NUMBER() OVER (PARTITION BY ID, Department ORDER BY dt DESC) AS rnPart
        ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY dt DESC) AS rnID
    FROM @T
)
SELECT
    ID
    ,Department
    ,dt
FROM CTE
WHERE
    rnPart = 1
    AND rnID <> 1
ORDER BY
    ID
    ,dt
;

结果

+----+------------+------------+
| ID | Department |     dt     |
+----+------------+------------+
|  1 | English    | 2017-02-04 |
|  1 | Science    | 2017-04-02 |
|  4 | Science    | 2017-02-01 |
+----+------------+------------+

这篇关于如何跟踪 SQL Server 中记录的变化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！