显示历史表中的更改历史

Question

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

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

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的更改。对于每一项更改，它都需要为每一列设置一个单独的行。因此，在insert上，我们有两行(因为我们监视两个字段的更改)。当一个列被更新时，它需要被表示为一个'U‘线。如果在同一个UPDATE语句中更新了两个字段，那么这将导致@Expected中的两个update行。

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

Answer 1

要获得单独行上的roleID和locationID，可以使用简单的UNION。

要组合旧值和新值，请使用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)