product 表
productid productdesc productprice
amq AAAAA 800
amq BBBBB 500
cmb CCCCC 600
cmb DDDDD 560
zkl EEEEE 890
zkl FFFFF 810
zkl GGGGG 823
efo HHHHH 645
efo IIIII 987
efo JJJJJ 239
efo KKKKK 176
pij LLLLL 129
pij IIIII 239
uyt IIIII 239
现在数据需要做迁移
将productid的amq移至到cmb的栏位,productid的cmb移至到zkl,依次类推
想变成如下数据productid productdesc productprice
amq AAAAA 800
amq BBBBB 500
amq CCCCC 600
amq DDDDD 560
cmb EEEEE 890
cmb FFFFF 810
cmb GGGGG 823
zkl HHHHH 645
zkl IIIII 987
zkl JJJJJ 239
zkl KKKKK 176
efo LLLLL 129
efo IIIII 239
pij IIIII 239如何通过sql语句实现?
productid productdesc productprice
amq AAAAA 800
amq BBBBB 500
cmb CCCCC 600
cmb DDDDD 560
zkl EEEEE 890
zkl FFFFF 810
zkl GGGGG 823
efo HHHHH 645
efo IIIII 987
efo JJJJJ 239
efo KKKKK 176
pij LLLLL 129
pij IIIII 239
uyt IIIII 239
现在数据需要做迁移
将productid的amq移至到cmb的栏位,productid的cmb移至到zkl,依次类推
想变成如下数据productid productdesc productprice
amq AAAAA 800
amq BBBBB 500
amq CCCCC 600
amq DDDDD 560
cmb EEEEE 890
cmb FFFFF 810
cmb GGGGG 823
zkl HHHHH 645
zkl IIIII 987
zkl JJJJJ 239
zkl KKKKK 176
efo LLLLL 129
efo IIIII 239
pij IIIII 239如何通过sql语句实现?
product 表
productid productdesc productprice
amq AAAAA 800
amq BBBBB 500
cmb CCCCC 600
cmb DDDDD 560
zkl EEEEE 890
zkl FFFFF 810
zkl GGGGG 823
efo HHHHH 645
efo IIIII 987
efo JJJJJ 239
efo KKKKK 176
pij LLLLL 129
pij IIIII 239
uyt JJJJJ 452
现在数据需要做迁移
将productid的amq移至到cmb的栏位,productid的cmb移至到zkl,依次类推
想变成如下数据productid productdesc productprice
amq AAAAA 800
amq BBBBB 500
amq CCCCC 600
amq DDDDD 560
cmb EEEEE 890
cmb FFFFF 810
cmb GGGGG 823
zkl HHHHH 645
zkl IIIII 987
zkl JJJJJ 239
zkl KKKKK 176
efo LLLLL 129
efo IIIII 239
pij JJJJJ 452
;with cte as
(
select rowid=row_number()over(order by newid() asc), id=row_number()over(partition by productid order by newid() asc), * from product
)
update cte set productid=b.productid from cte a,cte b where a.rowid>1 and b.rowid>a.rowid and a.productid<>b.productid
GO
--insert into #tb values(
insert into tb values('amq','AAAAA',800)
insert into tb values('amq',' BBBBB',500)
insert into tb values('cmb', 'CCCCC', 600)
insert into tb values('cmb' ,'DDDDD',560)
insert into tb values('zkl' ,'EEEEE',890)
insert into tb values('zkl', 'FFFFF',810)
insert into tb values('zkl',' GGGGG',823)
insert into tb values('efo', 'HHHHH',645)
insert into tb values('efo', 'IIIII',987)
insert into tb values('efo', 'JJJJJ',239)
insert into tb values('efo', 'KKKKK',176)
insert into tb values('pij', 'LLLLL',29)
insert into tb values('pij', 'IIIII',239)
insert into tb values('uyt', 'IIIII',239)
GO
--写入临时表
select *,id=identity(int,1,1) into # from tbupdate tb set productid=(select top 1 a.productid from # a inner join # b on a.id>1 and b.id=a.id+1 and a.productid<>b.productid where b.productid=tb.productid) from tbupdate tb set productid=(select top 1 productid from tb t where productid is not null) from tb where productid is nullselect * from tb
/**
productid productdesc productprice
---------- -------------------- ------------
amq AAAAA 800
amq BBBBB 500
amq CCCCC 600
amq DDDDD 560
cmb EEEEE 890
cmb FFFFF 810
cmb GGGGG 823
zkl HHHHH 645
zkl IIIII 987
zkl JJJJJ 239
zkl KKKKK 176
efo LLLLL 29
efo IIIII 239
pij IIIII 239(所影响的行数为 14 行)
**/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'prod'))
DROP TABLE prod
create table prod
(
productid varchar(20),
productdesc varchar(30),
productprice int
)
go
insert into prod
select 'amq','AAAAA',800 union all
select 'amq','BBBBB',500 union all
select 'cmb','CCCCC', 600 union all
select 'cmb','DDDDD', 560 union all
select 'zkl','EEEEE', 890 union all
select 'zkl','FFFFF', 810 union all
select 'zkl','GGGGG', 823 union all
select 'efo','HHHHH', 645 union all
select 'efo','IIIII', 987 union all
select 'efo','JJJJJ', 239 union all
select 'efo','KKKKK', 176 union all
select 'pij','LLLLL', 129 union all
select 'pij','IIIII', 239 union all
select 'uyt','JJJJJ', 452
go
select * from prod--Updated
declare @st_productid varchar(50)
declare @ls_productid varchar(50)
declare @nw_productid varchar(50)
declare @nw_productdesc varchar(50)
declare @nw_productprice varchar(50)select top 1 @st_productid=productid from prod
set @ls_productid=@st_productidDECLARE cur_kpid CURSOR FOR
select productid,productdesc,productprice from prod
OPEN cur_kpid
FETCH NEXT FROM cur_kpid INTO @nw_productid,@nw_productdesc,@nw_productprice
WHILE @@FETCH_STATUS = 0
BEGIN
if(@ls_productid<>@nw_productid and @ls_productid<>@st_productid)
begin
set @st_productid=@ls_productid
end
begin
if(@ls_productid<>@nw_productid)
begin
set @ls_productid=@nw_productid
endupdate prod set productid=@st_productid where productid=@nw_productid and productdesc=@nw_productdesc and productprice=@nw_productprice
end
FETCH NEXT FROM cur_kpid INTO @nw_productid,@nw_productdesc,@nw_productprice
END
CLOSE cur_kpid
DEALLOCATE cur_kpid
/*--Results
amq AAAAA 800
amq BBBBB 500
amq CCCCC 600
amq DDDDD 560
cmb EEEEE 890
cmb FFFFF 810
cmb GGGGG 823
zkl HHHHH 645
zkl IIIII 987
zkl JJJJJ 239
zkl KKKKK 176
efo LLLLL 129
efo IIIII 239
pij JJJJJ 452
*/