代码之家 › 专栏 › 技术社区 › Albert

SQL Server-需要一个SQL查询来标识/突出显示审核表中的特定更改

sql-server-2008 sql-server-2005 sql-server sql

Albert · 技术社区 · 14 年前

假设我在审计表中存储了一些数据,其中主数据表上的触发器将所有发票记录更新写入该审计表。审计表包含以下数据:

InvoiceID CustomerID  ItemSold    AmountSold     SalesPerson  ModifyDate
1001      96          Widget      800            Robert       2001-1-1
1006      85          Thinger     350            Phil         2001-1-8
1001      96          Widget      800            Bobby        2001-1-9
1005      22          Widget      400            Robert       2001-1-10
1006      44          Thinger     500            Mike         2001-2-5
1001      96          Widget      250            Robert       2001-6-4

我想写一个查询来确定 每当销售人员字段更改时 对于任何特定的发票ID(例如:每当销售人员将销售更改为其名称时)。

所以在上面的例子中,我想确定2001-1-9年发生的变化,发票ID 1001的销售从Robert变为Bobby,2001-6-4的销售从Bobby变为Robert……所以对这个特定ID进行了两次更改。我还想确定2001-2-5年发票ID 1006的销售发生的变化。从菲尔到迈克。

如何编写一个SQL查询来标识/突出显示这些更改?

表当前不包含主键,但如果需要,我可以添加一个。

4 回复 | 直到 14 年前

HLGEM 14 年前

如果您添加一个主键(应该这样做,从长远来看,它将使您在此表上所需的一些查询更加容易) 那么你需要的是一个自我加入。像这样的事情可能会发生:

select a.invoiceId, a.SalesPerson as FirstSalesPerson, 
    a.Modifydate as FirstModifyDate, b.SalesPerson as SecondSalesPerson, 
    B.Modifydate as SecondModifyDate  
from myaudittable a
join myadudittable b
   on a.InvoiceID = b.InvoiceID
where a.AuditIDd <>b.AuditID and a.ModifyDate < b.ModifyDate
   and a.SalesPerson<>b.SalesPerson
order by InvoiceID

Joe Stefanelli 14 年前

应该这样做。

declare @Audit table (
    InvoiceID int,
    CustomerID int,
    ItemSold varchar(10),
    AmountSold int,
    SalesPerson varchar(10),
    ModifyDate datetime
)

insert into @Audit
    (InvoiceID, CustomerID, ItemSold, AmountSold, SalesPerson, ModifyDate)
    values
    (1001, 96, 'Widget', 800, 'Robert', '2001-1-1'),
    (1006, 85, 'Thinger', 350, 'Phil', '2001-1-8'),
    (1001, 96, 'Widget', 800, 'Bobby', '2001-1-9'),
    (1005, 22, 'Widget', 400, 'Robert', '2001-1-10'),
    (1006, 44, 'Thinger', 500, 'Mike', '2001-2-5'),
    (1001, 96, 'Widget', 250, 'Robert', '2001-6-4')

select a2.InvoiceID, a2.SalesPerson, a2.ModifyDate
    from @Audit a1
        inner join @Audit a2
            on a1.InvoiceID = a2.InvoiceID
                and a1.ModifyDate < a2.ModifyDate
                and a1.SalesPerson <> a2.SalesPerson

GalacticCowboy 14 年前

我想这是一个更完整的答案。它假定:

至少SQL Server 2005
ModifyDate列是在审计日志中创建记录的时间。
存在标识主键auditID

declare @Audit table 
(
    AuditID int identity(1,1),
    InvoiceID int,
    CustomerID int,
    ItemSold varchar(10),
    AmountSold int,
    SalesPerson varchar(10),
    ModifyDate datetime
)

;with orders (InvoiceID, SalesPerson, ModifyDate, idx)
as
(
    select 
        InvoiceID, 
        SalesPerson, 
        ModifyDate, 
        row_number() over (partition by InvoiceID order by AuditID desc)
    from @Audit
) 

select o2.InvoiceID, o2.SalesPerson, o2.ModifyDate from orders o1 inner join orders o2 
on 
    o1.InvoiceID = o2.InvoiceID and 
    o1.SalesPerson <> o2.SalesPerson and
    o1.idx = o2.idx-1
order by InvoiceID, ModifyDate desc

Albert 14 年前

我从发布的答案中使用了一些零碎的内容,但唯一能够隔离销售人员实际变化的方法是使用子查询。否则我得到的结果太多了,很难找出记录更改销售人员的实际日期。

 select InvoiceId,SalesPerson,auditdate from myaudittable where InvoiceId in 
 (select distinct a.InvoiceId 
 from myaudittable a inner join myaudittable b on a.InvoiceId = b.InvoiceId and         
 a.SalesPerson <> b.SalesPerson)                
 group by InvoiceId,SalesPerson