- SQL code
--问题在下面第（3）部分标志了，即：第（3）部分这段更新代码，自己写的比较笨拙，大侠们能否给出更好的想法？--因为实际中，还有大概十几个这样的列需要更新后再插入(这里只给出了NumIDs和Trans两列)，自己写的这段硬编码，--实际中不太可行，总不能一个一个这样手写吧？还容易出错。--可操作性不好。。--（1）部分测试数据--原表create table temp_1(ID int,NumIDs varchar(50),BuckID int,Trans varchar(50)) insert into temp_1 values (762943 ,'(C) 20 - 52' ,4, '(D) 308 - 550,837') insert into temp_1 values (762389 ,'(B) 8 - 20 ' ,4, '(D) 308 - 550,837')insert into temp_1 values (762286 ,'(D) 52 - 77,885' ,3, '(C) 154 - 308 ')insert into temp_1 values (762164 ,'(A) 0 - 8 ' ,2, '(B) 84 - 154 ')insert into temp_1 values (761637 ,'(A) 0 - 8 ' ,3, '(C) 154 - 308 ')insert into temp_1 values (761357 ,'(D) 52 - 77,885' ,4, '(D) 308 - 550,837') insert into temp_1 values (761213 ,'(A) 0 - 8 ' ,3, '(C) 154 - 308 ')insert into temp_1 values (760836 ,'(B) 8 - 20 ' ,4, '(D) 308 - 550,837') insert into temp_1 values (760337 ,'(B) 8 - 20 ' ,2, '(B) 84 - 154 ')insert into temp_1 values (54 ,'(B) 8 - 20 ' ,4, '(A) 0 - 84') go--（2）追加到原表temp_1中的数据--新表temp_2create table temp_2(ID int,NumIDs varchar(50),BuckID int,Trans varchar(50))insert into temp_2 values (4467 ,'(C) 20 - 50' ,4, '(D) 400 - 590,000') insert into temp_2 values (5533 ,'(B) 15 - 20 ' ,4, '(D) 400 - 590,000')insert into temp_2 values (76 '(D) 50 - 80,885' ,3, '(C) 150 - 400 ')insert into temp_2 values (164 ,'(A) 0 - 15 ' ,2, '(B) 30 - 150 ')insert into temp_2 values (637 ,'(A) 0 - 15 ' ,3, '(A) 0 - 30 ')go--（3）如下是：需要重写代码的部分--要求是:将temp_2中的两列NumIDs和Trans取值,更新为temp_1中NumIDs和Trans的取值，再追加到temp_1中。update temp_2set NumIDs=case NumIDs when '(A) 0 - 15 ' then '(A) 0 - 8 ' when '(B) 15 - 20 ' then '(B) 8 - 20 ' when '(C) 20 - 50' then '(C) 20 - 52' when '(D) 50 - 80,885' then '(D) 52 - 77,885' end, Trans=case Trans when '(A) 0 - 30 ' then '(A) 0 - 84' when '(B) 30 - 150 ' then '(B) 84 - 154 ' when '(C) 150 - 400' then '(C) 154 - 308 ' when '(D) 400 - 590,000' then '(D) 308 - 550,837' end; insert into temp_1 select * from temp_2select * from temp_1 order by Trans
------解决方案--------------------
- SQL code
--1楼写差了，是这个create table temp_1(ID int,NumIDs varchar(50),BuckID int,Trans varchar(50)) insert into temp_1 values (762943 ,'(C) 20 - 52' ,4, '(D) 308 - 550,837') insert into temp_1 values (762389 ,'(B) 8 - 20 ' ,4, '(D) 308 - 550,837')insert into temp_1 values (762286 ,'(D) 52 - 77,885' ,3, '(C) 154 - 308 ')insert into temp_1 values (762164 ,'(A) 0 - 8 ' ,2, '(B) 84 - 154 ')insert into temp_1 values (761637 ,'(A) 0 - 8 ' ,3, '(C) 154 - 308 ')insert into temp_1 values (761357 ,'(D) 52 - 77,885' ,4, '(D) 308 - 550,837') insert into temp_1 values (761213 ,'(A) 0 - 8 ' ,3, '(C) 154 - 308 ')insert into temp_1 values (760836 ,'(B) 8 - 20 ' ,4, '(D) 308 - 550,837') insert into temp_1 values (760337 ,'(B) 8 - 20 ' ,2, '(B) 84 - 154 ')insert into temp_1 values (54 ,'(B) 8 - 20 ' ,4, '(A) 0 - 84') go--（2）追加到原表temp_1中的数据--新表temp_2create table temp_2(ID int,NumIDs varchar(50),BuckID int,Trans varchar(50))insert into temp_2 values (4467 ,'(C) 20 - 50' ,4, '(D) 400 - 590,000') insert into temp_2 values (5533 ,'(B) 15 - 20 ' ,4, '(D) 400 - 590,000')insert into temp_2 values (76 ,'(D) 50 - 80,885' ,3, '(C) 150 - 400 ')insert into temp_2 values (164 ,'(A) 0 - 15 ' ,2, '(B) 30 - 150 ')insert into temp_2 values (637 ,'(A) 0 - 15 ' ,3, '(A) 0 - 30 ')goselect a.id,b.numids,a.buckid,c.transfrom temp_2 a join temp_1 b on left(a.numids,3) = left(b.numids,3) join temp_1 c on left(a.trans,3) = left(c.trans,3)group by a.id,b.numids,a.buckid,c.trans--（3）如下是：需要重写代码的部分--要求是:将temp_2中的两列NumIDs和Trans取值,更新为temp_1中NumIDs和Trans的取值，再追加到temp_1中。update temp_2set NumIDs=case NumIDs when '(A) 0 - 15 ' then '(A) 0 - 8 ' when '(B) 15 - 20 ' then '(B) 8 - 20 ' when '(C) 20 - 50' then '(C) 20 - 52' when '(D) 50 - 80,885' then '(D) 52 - 77,885' end, Trans=case Trans when '(A) 0 - 30 ' then '(A) 0 - 84' when '(B) 30 - 150 ' then '(B) 84 - 154 ' when '(C) 150 - 400' then '(C) 154 - 308 ' when '(D) 400 - 590,000' then '(D) 308 - 550,837' end; select * from temp_2drop table temp_1,temp_2/********************id numids buckid trans----------- -------------------------------------------------- ----------- --------------------------------------------------76 (D) 52 - 77,885 3 (C) 154 - 308 164 (A) 0 - 8 2 (B) 84 - 154 637 (A) 0 - 8 3 (A) 0 - 844467 (C) 20 - 52 4 (D) 308 - 550,8375533 (B) 8 - 20 4 (D) 308 - 550,837(5 行受影响)(5 行受影响)ID NumIDs BuckID Trans----------- -------------------------------------------------- ----------- --------------------------------------------------4467 (C) 20 - 52 4 (D) 308 - 550,8375533 (B) 8 - 20 4 (D) 308 - 550,83776 (D) 52 - 77,885 3 (C) 154 - 308 164 (A) 0 - 8 2 (B) 84 - 154 637 (A) 0 - 8 3 (A) 0 - 84(5 行受影响)
------解决方案--------------------
- SQL code
create table temp_1(ID int,NumIDs varchar(50),BuckID int,Trans varchar(50)) insert into temp_1 values (762943 ,'(C) 20 - 52' ,4, '(D) 308 - 550,837') insert into temp_1 values (762389 ,'(B) 8 - 20 ' ,4, '(D) 308 - 550,837')insert into temp_1 values (762286 ,'(D) 52 - 77,885' ,3, '(C) 154 - 308 ')insert into temp_1 values (762164 ,'(A) 0 - 8 ' ,2, '(B) 84 - 154 ')insert into temp_1 values (761637 ,'(A) 0 - 8 ' ,3, '(C) 154 - 308 ')insert into temp_1 values (761357 ,'(D) 52 - 77,885' ,4, '(D) 308 - 550,837') insert into temp_1 values (761213 ,'(A) 0 - 8 ' ,3, '(C) 154 - 308 ')insert into temp_1 values (760836 ,'(B) 8 - 20 ' ,4, '(D) 308 - 550,837') insert into temp_1 values (760337 ,'(B) 8 - 20 ' ,2, '(B) 84 - 154 ')insert into temp_1 values (54 ,'(B) 8 - 20 ' ,4, '(A) 0 - 84') go--新表temp_2create table temp_2(ID int,NumIDs varchar(50),BuckID int,Trans varchar(50))insert into temp_2 values (4467 ,'(C) 20 - 50' ,4, '(D) 400 - 590,000') insert into temp_2 values (5533 ,'(B) 15 - 20 ' ,4, '(D) 400 - 590,000')insert into temp_2 values (76 ,'(D) 50 - 80,885' ,3, '(C) 150 - 400 ')insert into temp_2 values (164 ,'(A) 0 - 15 ' ,2, '(B) 30 - 150 ')insert into temp_2 values (637 ,'(A) 0 - 15 ' ,3, '(A) 0 - 30 ')go--select * from temp_1--select * from temp_2--分两次更新--先更新NumIDsupdate t2set t2.NumIDs = t1.NumIDsfrom temp_2 t2 join temp_1 t1 on left(t1.NumIDs, 3) = left(t2.NumIDs, 3);--再更新Transupdate t2set t2.Trans = t1.Transfrom temp_2 t2 join temp_1 t1 on left(t1.Trans, 3) = left(t2.Trans, 3);--再追加到temp_1中。insert into temp_1 select * from temp_2;select * from temp_1;drop table temp_1,temp_2;/*ID NumIDs BuckID Trans----------- -------------------------------------------------- ----------- --------------------------------------------------762943 (C) 20 - 52 4 (D) 308 - 550,837762389 (B) 8 - 20 4 (D) 308 - 550,837762286 (D) 52 - 77,885 3 (C) 154 - 308 762164 (A) 0 - 8 2 (B) 84 - 154 761637 (A) 0 - 8 3 (C) 154 - 308 761357 (D) 52 - 77,885 4 (D) 308 - 550,837761213 (A) 0 - 8 3 (C) 154 - 308 760836 (B) 8 - 20 4 (D) 308 - 550,837760337 (B) 8 - 20 2 (B) 84 - 154 54 (B) 8 - 20 4 (A) 0 - 844467 (C) 20 - 52 4 (D) 308 - 550,8375533 (B) 8 - 20 4 (D) 308 - 550,83776 (D) 52 - 77,885 3 (C) 154 - 308 164 (A) 0 - 8 2 (B) 84 - 154 637 (A) 0 - 8 3 (A) 0 - 84*/