查询以显示另一个表中的旧值和新值

Question

我在SQL Server 2012中有3个表。

表History是对表A中的值进行的所有更改的历史。它可以对PRICE、LOT、INTEREST进行多次更改，但在大多数情况下，该值只更改一次。

表A

AID PRICE   LOT INTEREST
------------------------
1   1500    10  0.5
2   2500    20  1.5

表B

BID AID
--------
11  1
22  2

表历史记录。

BID     ChangeField     OldValue    NewValue   ChangeDate
------------------------------------------------------------
11      PRICE           1700        1500       1/1/22
11      LOT             15          10         12/15/21 
11      update_flag                 M          1/1/22

我需要一个查询，该查询显示具有表History中的旧值和新值的表a。如果有1个以上的更改，则对于"旧"值，获取以前的最新值。示例：

AID OldPRICE    NewPRICE    OldLot  NewLot  OldInterest NewInterest
----------------------------------------------------------------
1   1700        1500        15      10      0.5         0.5
2   2500        2500        20      20      1.5         1.5

我该怎么做？非常感谢。

Answer 1

首先需要连接所有三个表，并根据AID进行聚合。一旦有了这些值，查询就需要有选择地从历史记录中选择值(如果存在的话(。

例如：

select
a.aid,
max(case when h.changefield = 'PRICE' then coalesce(h.oldvalue, a.price) end) as oldprice,
max(case when h.changefield = 'PRICE' then coalesce(h.newvalue, a.price) end) as newprice,
max(case when h.changefield = 'LOT' then coalesce(h.oldvalue, a.lot) end) as oldlot,
max(case when h.changefield = 'LOT' then coalesce(h.newvalue, a.lot) end) as newlot,
max(case when h.changefield = 'INTEREST' then coalesce(h.oldvalue, a.interest) end) as oldinterest,
max(case when h.changefield = 'INTEREST' then coalesce(h.newvalue, a.interest) end) as newinterest 
from table_a a
left join table_b b on b.aid = a.aid
left join history h on h.bid = b.bid
group by a.aid

Answer 2

您可以尝试将OUTER JOIN与条件聚合函数一起使用。

查询1：

SELECT A.AID,
MAX(ISNULL(CASE WHEN h.ChangeField = 'PRICE' THEN h.OldValue END,A.PRICE)) OldPRICE,
MAX(ISNULL(CASE WHEN h.ChangeField = 'PRICE' THEN h.NewValue END,A.PRICE)) NewPRICE,
MAX(ISNULL(CASE WHEN h.ChangeField = 'LOT' THEN h.OldValue END,A.LOT)) OldLot,
MAX(ISNULL(CASE WHEN h.ChangeField = 'LOT' THEN h.NewValue END,A.LOT)) NewLot,
MAX(ISNULL(CASE WHEN h.ChangeField = 'INTEREST' THEN h.OldValue END,A.INTEREST)) OldInterest,
MAX(ISNULL(CASE WHEN h.ChangeField = 'INTEREST' THEN h.NewValue END,A.INTEREST)) NewInterest
FROM A 
LEFT JOIN B ON A.AID = B.AID
LEFT JOIN History h ON B.BID = h.BID
GROUP BY A.AID

结果：

| AID | OldPRICE | NewPRICE | OldLot | NewLot | OldInterest | NewInterest |
|-----|----------|----------|--------|--------|-------------|-------------|
|   1 |     1700 |     1500 |     15 |     10 |         0.5 |         0.5 |
|   2 |     2500 |     2500 |     20 |     20 |         1.5 |         1.5 |