How to find destroyed data rows using audit trail with SQL
I am using an audit table to find what records a single destructive SQL statement that was run on say '2011-01-01 08:00:00.000'. You can assume that each MyTable record only got updated once on this day the script ran. This SQL was probably run by a user and can not be retrieved. Everytime there is a change to MyTable an audit trail of the new value gets store开发者_如何学编程d in AuditMyTable.
My original table schema:
MyTable(AccountId int PK, Amount int)
My audit trail table for MyTable schema:
AuditMyTable(AccountId int, Amount int, AuditDate datetime)
My question is how do I write a SQL join from AuditMyTable against itself so that I can get back the following data:
AuditMyTable.AccountId, AuditMyTable.Amount as [NullAmount], AuditMyTablePrevious.Amount as [PreviousNotNullAmount]
First, you have to get your sequence (I'm making one from your dates, assuming they are strictly increasing):
SELECT AccountId, cur.AuditDate AS cur_AuditDate, MAX(prev.AuditDate) AS prev_AuditDate
FROM AuditMyTable AS cur
LEFT JOIN AuditMyTable AS prev
ON prev.AccountId = cur.AccountId
AND prev.AuditDate < cur.AuditDate
GROUP BY AccountId, cur.AuditDate
Then use it:
WITH Ordering AS (
SELECT AccountId, cur.AuditDate AS cur_AuditDate, MAX(prev.AuditDate) AS prev_AuditDate
FROM AuditMyTable AS cur
LEFT JOIN AuditMyTable AS prev
ON prev.AccountId = cur.AccountId
AND prev.AuditDate < cur.AuditDate
GROUP BY AccountId, cur.AuditDate
)
SELECT cur.AccountId, cur.Amount as [NullAmount], prev.Amount as [PreviousNotNullAmount]
FROM AuditMyTable AS cur
INNER JOIN Ordering
ON Ordering.AccountId = cur.AccountId
AND Ordering.cur_AuditDate = cur.Audit_Date
LEFT JOIN AuditMyTable AS prev
ON prev.AccountId = cur.AccountId
AND prev.Audit_Date = Ordering.prev_AuditDate
精彩评论