如何在 SQL Server 中跟踪记录中的更改

Question

我有下表，其中包含所有学生的跟踪记录。

|==========================================|
| 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   |
|==========================================| 

我需要在学生更改部门之前获取以前的记录。对于上面的示例，返回的记录集应为

对于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发送和接收的信息。但不确定在这种情况下是否有帮助。请帮忙。

Answer 1

一种方法

是使用ROW_NUMBER分区函数来检测Department列的值何时更改。

示例数据

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 |
+----+------------+------------+

Answer 2

这可以通过在需要跟踪的表上创建插入/更新触发器来完成。使用 sql Server 中的"插入/删除"逻辑表，可以跟踪表上新插入和修改的字段。

插入(

逻辑表(：它将为您提供新插入的记录/更新值(列值(的详细信息。

已删除(逻辑表(：它会在修改/删除字段之前为您提供字段的旧值。

Answer 3

我不太确定，但您可以获得有关存储过程概念的帮助。它们是作为数据库一部分的函数。您可以在进行查询时设置显示