sql - 显示历史表中的更改历史

Question

我有一个历史表，它是由插入和更新触发器创建的。历史记录行包含插入/更新时的行。

我被要求做的是显示每个用户随时间的变化。因此，下面是我的历史表形式的内容，然后我创建了一个虚拟的预期结果。

DECLARE @MyTable TABLE
(
    id INT NOT NULL IDENTITY(1,1),
    userId INT NOT NULL,
    locationId INT NOT NULL,
    roleId INT NOT NULL,
    lastUpdateUserId INT NOT NULL,
    lastUpdateDate DATETIME NOT NULL
)


INSERT INTO @MyTable
(userId, locationId, roleId, lastUpdateUserId, lastUpdateDate)
SELECT 1, 1000, 1, 7, GETDATE()+1 UNION
SELECT 2, 1100, 5, 9, GETDATE()+2 UNION
SELECT 2, 1110, 5, 6, GETDATE()+3 UNION
SELECT 1, 1100, 3, 6, GETDATE()+4 UNION
SELECT 4, 1500, 5, 8, GETDATE()+5 UNION
SELECT 7, 1000, 8, 9, GETDATE()+6 UNION
SELECT 7, 1100, 9, 9, GETDATE()+7 UNION
SELECT 1, 1000, 3, 7, GETDATE()+8 UNION
SELECT 9, 1100, 5, 2, GETDATE()+9 UNION
SELECT 9, 1100, 6, 5, GETDATE()+10 

SELECT * FROM @MyTable ORDER BY Id

DECLARE @ExpectedResult TABLE
(
    ChangeType CHAR(1), -- I=Insert, U=Update
    UserId INT,
    ChangeDate DATETIME,
    ChangedByUser INT,
    FieldName VARCHAR(20),
    OldValue INT,
    NewValue INT
)

INSERT INTO @ExpectedResult
(ChangeType, UserId, ChangeDate, ChangedByUser, FieldName, OldValue, NewValue)
SELECT 'I', 1, '2015-APR-30 09:56:28', 7, 'locationId', NULL, 1000 UNION -- Row1
SELECT 'I', 1, '2015-APR-30 09:56:28', 7, 'roleId', NULL, 1 UNION -- Row1
SELECT 'U', 1, '2015-APR-07 10:27:42', 7, 'roleId', 1, 3 UNION -- Row 2
SELECT 'U', 1, '2015-MAY-03 10:27:42', 6, 'locationId', 1000, 1100 UNION -- Row 3
SELECT 'I', 2, '2015-MAY-01 10:27:42', 9, 'roleId', NULL, 5 UNION -- Row5
SELECT 'I', 2, '2015-MAY-01 10:27:42', 9, 'locationId', NULL, 1100 -- Row5

SELECT * FROM @ExpectedResult

@MyTable 拥有当前的数据。我正在尝试将其转换为@ExpectedResults。我们正在报告 roleId 和 locationId 的变化。每次更改时，每一列都需要单独一行。因此，在插入时，我们有两行(因为我们监视两个字段的更改)。当更新一列时，需要将其表示为一个“U”行。如果两个字段都在同一个 UPDATE 语句中更新，那么这将导致 @Expected 中有两个更新行。

我从 Cursor 开始，但希望有更有效的方法来实现这一点。

Answer 1

要在单独的行中获取角色 ID 和位置 ID，您可以使用简单的 UNION ALL。

并使用 ROW_NUMBER() 窗口函数组合旧值和新值，如下所示:

;with t as(
    select *,
    ROW_NUMBER() OVER(partition by userid Order BY lastUpdateDate) rn
    from @MyTable
),
a as (
select userId, 'locationId' as fieldname,
locationId as value, lastUpdateUserId, lastUpdateDate, rn
from t
UNION ALL
select userId, 'roleId' as fieldname,
roleId as value, lastUpdateUserId, lastUpdateDate, rn
from t
)
select CASE WHEN a2.userId IS NULL THEN 'I' ELSE 'U' END as ChangeType,
a1.userId, a1.lastUpdateDate, a1.lastUpdateUserId, a1.fieldname, a1.value as newValue, a2.value as oldvalue
FROM a a1 LEFT JOIN a a2
ON a1.userId = a2.userId and a1.fieldname = a2.fieldname
AND a1.rn = a2.rn+1
order by 2,3,5

上述查询中的a1 别名包含“新值”，a2 包含“旧值”。当您使用真实数据时，您还需要按字段名(可能还有表名)进行分区，并按它们进行连接

结果:

ChangeType userId      lastUpdateDate          lastUpdateUserId fieldname  newValue    oldvalue
---------- ----------- ----------------------- ---------------- ---------- ----------- -----------
I          1           2015-04-30 12:20:59.183 7                locationId 1000        NULL
I          1           2015-04-30 12:20:59.183 7                roleId     1           NULL
U          1           2015-05-03 12:20:59.183 6                locationId 1100        1000
U          1           2015-05-03 12:20:59.183 6                roleId     3           1
U          1           2015-05-07 12:20:59.183 7                locationId 1000        1100
U          1           2015-05-07 12:20:59.183 7                roleId     3           3
I          2           2015-05-01 12:20:59.183 9                locationId 1100        NULL
I          2           2015-05-01 12:20:59.183 9                roleId     5           NULL
U          2           2015-05-02 12:20:59.183 6                locationId 1110        1100
U          2           2015-05-02 12:20:59.183 6                roleId     5           5
I          4           2015-05-04 12:20:59.183 8                locationId 1500        NULL
I          4           2015-05-04 12:20:59.183 8                roleId     5           NULL
I          7           2015-05-05 12:20:59.183 9                locationId 1000        NULL
I          7           2015-05-05 12:20:59.183 9                roleId     8           NULL
U          7           2015-05-06 12:20:59.183 9                locationId 1100        1000
U          7           2015-05-06 12:20:59.183 9                roleId     9           8
I          9           2015-05-08 12:20:59.183 2                locationId 1100        NULL
I          9           2015-05-08 12:20:59.183 2                roleId     5           NULL
U          9           2015-05-09 12:20:59.183 5                locationId 1100        1100
U          9           2015-05-09 12:20:59.183 5                roleId     6           5

(20 row(s) affected)