MSSQL2000某重复行设置
表数据如下:
BC , CC , BA , BALA , USERDEF1 , USERDEF2 , USERDEF3 , USERDEF4
SC-2010-11-18-0001 A01 33 22 2 6 2 23
SC-2010-11-18-0001 A01 33 22 3 52 23 23
SC-2010-11-18-0001 A01 33 22 3 4 22 23
SC-2010-11-18-0001 A01 33 22 7 3 21 23
SC-2010-11-18-0001 A01 33 22 9 2 23 23SC-2010-11-18-0003 A02 2 2 2 2 2 2
把相同单号BC,相同客户CC ,相同金额BA , 相同实收金额BALA 均相同时则把其它行内容设置为0。
要求结果如下:BC , CC , BA , BALA , USERDEF1 , USERDEF2 , USERDEF3 , USERDEF4
SC-2010-11-18-0001 A01 33 22 2 6 2 23
SC-2010-11-18-0001 A01 0 0 3 52 23 23
SC-2010-11-18-0001 A01 0 0 3 4 22 23
SC-2010-11-18-0001 A01 0 0 7 3 21 23
SC-2010-11-18-0001 A01 0 0 9 2 23 23SC-2010-11-18-0003 A02 2 2 2 2 2 2
表数据如下:
BC , CC , BA , BALA , USERDEF1 , USERDEF2 , USERDEF3 , USERDEF4
SC-2010-11-18-0001 A01 33 22 2 6 2 23
SC-2010-11-18-0001 A01 33 22 3 52 23 23
SC-2010-11-18-0001 A01 33 22 3 4 22 23
SC-2010-11-18-0001 A01 33 22 7 3 21 23
SC-2010-11-18-0001 A01 33 22 9 2 23 23SC-2010-11-18-0003 A02 2 2 2 2 2 2
把相同单号BC,相同客户CC ,相同金额BA , 相同实收金额BALA 均相同时则把其它行内容设置为0。
要求结果如下:BC , CC , BA , BALA , USERDEF1 , USERDEF2 , USERDEF3 , USERDEF4
SC-2010-11-18-0001 A01 33 22 2 6 2 23
SC-2010-11-18-0001 A01 0 0 3 52 23 23
SC-2010-11-18-0001 A01 0 0 3 4 22 23
SC-2010-11-18-0001 A01 0 0 7 3 21 23
SC-2010-11-18-0001 A01 0 0 9 2 23 23SC-2010-11-18-0003 A02 2 2 2 2 2 2
as
(select cnt=row_number()over(partition by bc,cc,ba,lala order by (select 1)),
*
from tb)
update tt
set ba=0,bala=0
where id!=1
set BA = 0 , BALA = 0
from tb t where USERDEF1 not in (select min(USERDEF1) from tb
where BC = t.BC and CC = t.CC and BA = t.BA and BALA = t.BALA)
go
create table [tb] ([BC] nvarchar(36),[CC] nvarchar(6),[BA] int,[BALA] int,[USERDEF1] int,[USERDEF2] int,[USERDEF3] int,[USERDEF4] int)
insert into [tb]
select 'SC-2010-11-18-0001','A01',33,22,2,6,2,23 union all
select 'SC-2010-11-18-0001','A01',33,22,3,52,23,23 union all
select 'SC-2010-11-18-0001','A01',33,22,3,4,22,23 union all
select 'SC-2010-11-18-0001','A01',33,22,7,3,21,23 union all
select 'SC-2010-11-18-0001','A01',33,22,9,2,23,23
;with tt
as
(select ID=row_number()over(partition by bc,cc,ba,bala order by (select 1)),
*
from tb)
update tt
set ba=0,bala=0
where id!=1
select *
from tb
/*
BC CC BA BALA USERDEF1 USERDEF2 USERDEF3 USERDEF4
------------------------------------ ------ ----------- ----------- ----------- ----------- ----------- -----------
SC-2010-11-18-0001 A01 33 22 2 6 2 23
SC-2010-11-18-0001 A01 0 0 3 52 23 23
SC-2010-11-18-0001 A01 0 0 3 4 22 23
SC-2010-11-18-0001 A01 0 0 7 3 21 23
SC-2010-11-18-0001 A01 0 0 9 2 23 23(5 row(s) affected)
*/
create table tb(BC varchar(50), CC varchar(10), BA int, BALA int, USERDEF1 int, USERDEF2 int, USERDEF3 int, USERDEF4 int)
insert into tb values('SC-2010-11-18-0001', 'A01', 33 ,22 ,2 ,6 ,2 ,23)
insert into tb values('SC-2010-11-18-0001', 'A01', 33 ,22 ,3 ,52 ,23 ,23)
insert into tb values('SC-2010-11-18-0001', 'A01', 33 ,22 ,3 ,4 ,22 ,23)
insert into tb values('SC-2010-11-18-0001', 'A01', 33 ,22 ,7 ,3 ,21 ,23)
insert into tb values('SC-2010-11-18-0001', 'A01', 33 ,22 ,9 ,2 ,23 ,23)
goupdate tb
set BA = 0 , BALA = 0
from tb t where USERDEF1 not in (select min(USERDEF1) from tb
where BC = t.BC and CC = t.CC and BA = t.BA and BALA = t.BALA)select * from tbdrop table tb/*
BC CC BA BALA USERDEF1 USERDEF2 USERDEF3 USERDEF4
-------------------------------------------------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
SC-2010-11-18-0001 A01 33 22 2 6 2 23
SC-2010-11-18-0001 A01 0 0 3 52 23 23
SC-2010-11-18-0001 A01 0 0 3 4 22 23
SC-2010-11-18-0001 A01 0 0 7 3 21 23
SC-2010-11-18-0001 A01 0 0 9 2 23 23(所影响的行数为 5 行)
*/
--按照指标相同时保留第一条记录.
create table tb(BC varchar(50), CC varchar(10), BA int, BALA int, USERDEF1 int, USERDEF2 int, USERDEF3 int, USERDEF4 int)
insert into tb values('SC-2010-11-18-0001', 'A01', 33 ,22 ,2 ,6 ,2 ,23)
insert into tb values('SC-2010-11-18-0001', 'A01', 33 ,22 ,3 ,52 ,23 ,23)
insert into tb values('SC-2010-11-18-0001', 'A01', 33 ,22 ,3 ,4 ,22 ,23)
insert into tb values('SC-2010-11-18-0001', 'A01', 33 ,22 ,7 ,3 ,21 ,23)
insert into tb values('SC-2010-11-18-0001', 'A01', 33 ,22 ,9 ,2 ,23 ,23)
goupdate tb
set BA = 0 , BALA = 0
from tb t where USERDEF1 <> (select top 1 USERDEF1 from tb
where BC = t.BC and CC = t.CC and BA = t.BA and BALA = t.BALA)select * from tbdrop table tb/*
BC CC BA BALA USERDEF1 USERDEF2 USERDEF3 USERDEF4
-------------------------------------------------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
SC-2010-11-18-0001 A01 33 22 2 6 2 23
SC-2010-11-18-0001 A01 0 0 3 52 23 23
SC-2010-11-18-0001 A01 0 0 3 4 22 23
SC-2010-11-18-0001 A01 0 0 7 3 21 23
SC-2010-11-18-0001 A01 0 0 9 2 23 23(所影响的行数为 5 行)
*/