有如下表(table1):
id xmname price1 price2 price3 lx
1 a 3.12 40.5 23.5 1
2 s 12.23 23.5 14.5 0
2 d 156.4 23.5 14.5 0
3 a 263.5 53.3 45.2 2
.
.
.
比如编号2,有2个重复记录,但xmname不同,price1不同,但price2和price3相同,
如何将有相同编号的记录price2和price3保留原值并将其它相同编号记录的price2和price3修改为0
如:
id xmname price1 price2 price3 lx
.
.
2 s 12.23 23.5 14.5 0
2 d 156.4 0 0 0
.
.
id xmname price1 price2 price3 lx
1 a 3.12 40.5 23.5 1
2 s 12.23 23.5 14.5 0
2 d 156.4 23.5 14.5 0
3 a 263.5 53.3 45.2 2
.
.
.
比如编号2,有2个重复记录,但xmname不同,price1不同,但price2和price3相同,
如何将有相同编号的记录price2和price3保留原值并将其它相同编号记录的price2和price3修改为0
如:
id xmname price1 price2 price3 lx
.
.
2 s 12.23 23.5 14.5 0
2 d 156.4 0 0 0
.
.
set
price2=0,price3=0
from
table1 t
where
t.xmname<>(select top 1 xmname from table1 where id=t.id and price2=t.price2 and price3=t.price3)
insert into 表1
select 1,'a',3.12,40.5, 23.5,1
union all select 2,'s',12.23,3.5, 14.5,0
union all select 2,'d',156.4,3.5, 14.5,0
union all select 3,'a',263.5,53.3, 45.2, 2update 表1
set price2=0,price3=0
from 表1
where exists(select 1 from 表1 t where 表1.id=t.id and 表1.xmname<>t.xmname and 表1.xmname<t.xmname and 表1.price2=t.price2 and 表1.price3=t.price3)
insert into @t select 1,'a',3.12 ,40.5,23.5,1
insert into @t select 2,'s',12.23,23.5,14.5,0
insert into @t select 2,'d',156.4,23.5,14.5,0
insert into @t select 3,'a',263.5,53.3,45.2,2
update t
set
price2=0,price3=0
from
@t t
where
t.xmname<>(select top 1 xmname from @t where id=t.id and price2=t.price2 and price3=t.price3)
select * from @t
/*
id xmname price1 price2 price3 lx
----------- -------- ------- ------- ------- -----------
1 a 3.1 40.5 23.5 1
2 s 12.2 23.5 14.5 0
2 d 156.4 .0 .0 0
3 a 263.5 53.3 45.2 2
*/
1. id相同的>=2條
2. xmname不同,price1不同,但price2和price3相同