首页>Database>2020-12-02 11:31source

t sql:查询以基于另一个表的列更新表的一列

我有两个具有以下结构的表：

Create table Customer_info
(Customer_num  varchar(50),
 Customer_Branch  int);
Create table Customer_Transaction_Info
(Customer_num     varchar(50),
 Branch_Code      int,
 Trns_Measure_One int,
 Trns_Measure_Two int
 );

每个表的样本数据：

insert into Customer_info(Customer_num , Customer_Branch)
values('A',1),
      ('B',2),
      ('C',3);
   Customer_num     Customer_Branch
------------------- ---------------
     A                      1
     B                      2
     C                      3
insert into Customer_Transaction_Info (Customer_num,Branch_Code,Trns_Measure_One,Trns_Measure_Two)
Values ('A',1,10,0),
       ('A',2,20,5),
       ('A',3,14,0),
       ('B',2,10,10),
       ('B',1,36,0),
       ('C',3,14,0),
       ('C',1,10,18);
 Customer_num    Branch_Code Trns_Measure_One Trns_Measure_Two
 --------------- ----------- ----------------  ---------------
    A               1           10               0
    A               2           20               5
    A               3           14               0
 -------------------------------------------------------------
    B               2           10               10
    B               1           36               0
 -------------------------------------------------------------
    C               3           14               0
    C               1           10               18

我需要做的是这样

对于每个 Customer_num 在 Customer_Transaction_Info ， Trns_Measure_Two 必须分配给正确的 Branch_Code 对于第一个表中的那个客户 Customer_info
对于每个 Customer_num 在 Customer_Transaction_Info ， Trns_Measure_Two 必须将错误分支更新为0。

因此，基于这些，期望的结果如下：

Customer_num    Branch_Code Trns_Measure_One Trns_Measure_Two
 --------------- ----------- ----------------  ---------------
    A               1           10               0---> Update to 5
    A               2           20               5---> Update to 0
    A               3           14               0
 -------------------------------------------------------------
    B               2           10               10---> This record is Correct
    B               1           36               0
 -------------------------------------------------------------
    C               3           14               0 ---> Update to 18
    C               1           10               18---> Update to 0

我们必须考虑以下事实：

只有一个 Customer_Branch 对于每个 Customer_num 在 Customer_info 表。
只有一个非零的 Trns_Measure_Two 对于每个 Customer_num 在 Customer_Transaction_Info

预先感谢

queryperformance

tsql

sqlserver2014

query

update

最新回答

1月前

1 #

UPDATE Customer_Transaction_Info
SET Trns_Measure_Two = subquery.Trns_Measure_Two
FROM (SELECT cti.Customer_num, cti.Branch_Code, cti.Trns_Measure_One,
             CASE WHEN ci.Customer_Branch = cti.Branch_Code
                  THEN MAX(cti.Trns_Measure_Two) OVER (PARTITION BY cti.Customer_num)
                  ELSE 0
                  END Trns_Measure_Two
      FROM Customer_info ci
      JOIN Customer_Transaction_Info cti ON ci.Customer_num = cti.Customer_num) subquery
WHERE Customer_Transaction_Info.Customer_num = subquery.Customer_num
  AND Customer_Transaction_Info.Branch_Code = subquery.Branch_Code

小提琴

1月前

2 #

这应该解决第一个要求：

UPDATE CTI.Trns_Measure_Two -- Updating Customer_info.Trns_Measure_Two
SET CTI.Trns_Measure_Two = CTI2.Trns_Measure_Two -- Set it to the correct value (the only non-zero value for that Customer_num)
FROM Customer_Transaction_Info AS CTI
INNER JOIN Customer_info AS CI -- Inner joining Customer_info to ensure we only update for the correct Customer and Branch
    ON CTI.Customer_num = CI.Customer_num
    AND CTI.Branch_Code = CI.Customer_Branch
INNER JOIN Customer_Transaction_Info AS CTI2 -- Need to re-join to Customer_Transaction_Info on Customer_num and Trns_Measure_Two <> 0 to get this customer's non-zero correct value
    ON CTI.Customer_num = CTI2.Customer_num
    AND CTI2.Trns_Measure_Two <> 0

这应该为您的第二个要求更新不正确的分支：

UPDATE CTI.Trns_Measure_Two -- Updating Customer_info.Trns_Measure_Two
SET CTI.Trns_Measure_Two = 0 -- Setting incorrect values to 0
FROM Customer_Transaction_Info AS CTI
LEFT JOIN Customer_info AS CI -- Left joining to Customer_info so we can see where there's no match by Customer_num and Customer_Branch
    ON CTI.Customer_num = CI.Customer_num
    AND CTI.Branch_Code = CI.Customer_Branch
WHERE CI.Customer_num IS NULL -- This filters Customer_Transaction_Info to only the bad records (where there's no match to Customer_info)

请参阅注释以获取有关这两个更新语句如何工作的信息。

1月前

3 #

update a
set a.Trns_Measure_Two = case
                            when b.Trns_Measure_Two is null then 0
                            else b.Trns_Measure_Two end
  from Customer_Transaction_Info a
  left join (select t1.Customer_num, t1.Customer_Branch, t2.Trns_Measure_Two
               from Customer_info t1
              inner join (select distinct i.Customer_num, i.Trns_Measure_Two
                           from Customer_Transaction_Info i
                          where i.Trns_Measure_Two > 0) t2
                 on t1.Customer_num = t2.Customer_num) b
    on a.Customer_num = b.Customer_num
   and a.Branch_Code = b.Customer_Branch;

Codebug

t sql:查询以基于另一个表的列更新表的一列

queryperformance

tsql

sqlserver2014

query

update

相关问题