MSSQL列值相同时去除重复数据表tb如下
MGOODSID, DGOODSID, QTY , DQT
10001 , 30001 , 30 , 87
10001 , 40001 , 20 , 99
10008 , 30001 , 30 , 88
10009 , 7000 , 50 , 67
10009 , 7000 , 33 , 67要求结果如下:
当列DGOODSID是相同时,DQTY和DQT分别只取一次值
MGOODSID, DGOODSID, QTY , DQT
10001 , 30001 , 30 , 87
10001 , 40001 , 20 , 99
10008 , 30001 , 0 , 88 --- 这里变0
10009 , 7000 , 50 , 67
10009 , 7000 , 33 , 0 ---这里变0
MGOODSID, DGOODSID, QTY , DQT
10001 , 30001 , 30 , 87
10001 , 40001 , 20 , 99
10008 , 30001 , 30 , 88
10009 , 7000 , 50 , 67
10009 , 7000 , 33 , 67要求结果如下:
当列DGOODSID是相同时,DQTY和DQT分别只取一次值
MGOODSID, DGOODSID, QTY , DQT
10001 , 30001 , 30 , 87
10001 , 40001 , 20 , 99
10008 , 30001 , 0 , 88 --- 这里变0
10009 , 7000 , 50 , 67
10009 , 7000 , 33 , 0 ---这里变0
insert into tb select 10001 , 30001 , 30 , 87
insert into tb select 10001 , 40001 , 20 , 99
insert into tb select 10008 , 30001 , 30 , 88
insert into tb select 10009 , 7000 , 50 , 67
insert into tb select 10009 , 7000 , 33 , 67
go
select mgoodsid,dgoodsid,qty,
(case when exists(select 1 from tb where dgoodsid=a.dgoodsid and (qty>a.qty or mgoodsid<a.mgoodsid)) then 0 else dqt end)dqt
from tb a
go
drop table tb
/*
mgoodsid dgoodsid qty dqt
----------- ----------- ----------- -----------
10001 30001 30 87
10001 40001 20 99
10008 30001 30 0
10009 7000 50 67
10009 7000 33 0(5 行受影响)*/
create table tb(MGOODSID int, DGOODSID int, QTY int, DQT int)
insert into tb select 10001 , 30001 , 30 , 87
insert into tb select 10001 , 40001 , 20 , 99
insert into tb select 10008 , 30001 , 30 , 88
insert into tb select 10009 , 7000 , 50 , 67
insert into tb select 10009 , 7000 , 33 , 67
go
select mgoodsid,dgoodsid,
(case when exists(select 1 from tb where dgoodsid=a.dgoodsid and qty=a.qty and mgoodsid<a.mgoodsid) then 0 else qty end)qty,
(case when exists(select 1 from tb where dgoodsid=a.dgoodsid and dqt=a.dqt and qty>a.qty) then 0 else dqt end)dqt
from tb a
go
drop table tb
/*
mgoodsid dgoodsid qty dqt
----------- ----------- ----------- -----------
10001 30001 30 87
10001 40001 20 99
10008 30001 0 88
10009 7000 50 67
10009 7000 33 0(5 行受影响)
*/
不过,有点牵强,如果式子中的比较不是这样,那就无法判断哪个该为零了.
可能是我表达的不太清楚了.1/当列DGOODIS中的值内容相同作为前提条件, 并且DQTY列值相同时,QTY只取其中一个数2/当列DGOODIS中的值内容相同作为前提条件, 并且DQT列值相同时,DQT只取其中一个数
不过,有点牵强,如果式子中的比较不是这样,那就无法判断哪个该为零了是的,如果其它列的内容改变了一下,或MGOODSID/DGOODID/QTY/QT列内容相同时,可能
出来的结果就不一样了
drop table #temp
create table #temp
(
MGOODSID int,
DGOODSID int,
QTY int,
DQT int
)
insert #temp
select 10001 , 30001 , 30 , 87 union all
select 10001 , 40001 , 20 , 99 union all
select 10008 , 30001 , 30 , 88 union all
select 10009 , 7000 , 50 , 67 union all
select 10009 , 7000 , 33 , 67
go
--SQL:
;WITH cte as
(
select
rowno = ROW_NUMBER() over(order by getdate()),
*
from #temp
)
update A
set QTY = case when EXISTS(select 1 from cte where QTY = A.QTY and rowno < A.rowno) then 0 else QTY end,
DQT = case when EXISTS(select 1 from cte where DQT = A.DQT and rowno < A.rowno) then 0 else DQT end
from cte A
go
select * from #temp
/*
MGOODSID DGOODSID QTY DQT
10001 30001 30 87
10001 40001 20 99
10008 30001 0 88
10009 7000 50 67
10009 7000 33 0
*/
我是在MSSQL2000环境下使用的,麻烦帮忙一下
select id = identity(int, 1,1), * into # from #temp
go
update A
set QTY = case when EXISTS(select 1 from # where QTY = A.QTY and id < A.id) then 0 else QTY end,
DQT = case when EXISTS(select 1 from # where DQT = A.DQT and id < A.id) then 0 else DQT end
from # A
go
select * from #
/*
id MGOODSID DGOODSID QTY DQT
1 10001 30001 30 87
2 10001 40001 20 99
3 10008 30001 0 88
4 10009 7000 50 67
5 10009 7000 33 0
*/
set QTY = case when EXISTS(select 1 from # where QTY = A.QTY and id < A.id) then 0 else QTY end,
DQT = case when EXISTS(select 1 from # where DQT = A.DQT and id < A.id) then 0 else DQT end
from # A是不是要增加多个条件 dgoodsid=a.dgoodsid