一个表table1包含Code, price, qty 三个字段和其他如unit,name等字段,qty可能正数或负数。
其中部分记录code和price重复,要求查询出符合以下条件的记录: 当两条记录的code和price相同但qty正负相互抵消时,则剔除该两条记录需要考虑:
有可能存在多条记录的code和price相同,但仅有两条qty可以抵消时,只剔除该两条记录,其他的要保留。
如果有必要,可以假设存在唯一值的字段ID请问如何实现?
其中部分记录code和price重复,要求查询出符合以下条件的记录: 当两条记录的code和price相同但qty正负相互抵消时,则剔除该两条记录需要考虑:
有可能存在多条记录的code和price相同,但仅有两条qty可以抵消时,只剔除该两条记录,其他的要保留。
如果有必要,可以假设存在唯一值的字段ID请问如何实现?
(
select code,price , abs(qty) qty from tb group by code,price , abs(qty) having count(1) > 1
) n where n.code = m.code and n.price = m.price and n.qty = abs(m.qty)
)
--> Author : js_szy
--> Target : ★★★
--> Date : 2009-12-22 10:47:14
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (Code varchar(1),price int,qty int)
insert into @tb
select 'a',1,-2 union all
select 'a',1,2 union all
select 'a',1,3 union all
select 'b',2,1delete @tb
from @tb t
where exists(select * from @tb where code=t.code and price=t.price and qty+t.qty=0)
select * from @tbCode price qty
---- ----------- -----------
a 1 3
b 2 1(2 行受影响)
select m.* from tb m where not exists(select 1 from
(
select code,price , abs(qty) qty from tb group by code,price , abs(qty) having count(1) > 1
) n where n.code = m.code and n.price = m.price and n.qty = abs(m.qty)
)--删除
delete tb from tb m where exists(select 1 from
(
select code,price , abs(qty) qty from tb group by code,price , abs(qty) having count(1) > 1
) n where n.code = m.code and n.price = m.price and n.qty = abs(m.qty)
)
code price qty
01 12.00 2
02 13.00 -5
02 13.00 5
02 13.00 5
03 15.00 2
03 18.00 3
要求得到结果:
01 12.00 2
02 13.00 5
03 15.00 2
03 18.00 3
说明:code=02 price=13.00 qty=5和-5 的其中两条记录因为qty正负抵消而剔除掉
go
create table [tb] (id int identity(1,1),code nvarchar(4),price numeric(4,2),qty int)
insert into [tb]
select '01',12.00,2 union all
select '02',13.00,-5 union all
select '02',13.00,5 union all
select '02',13.00,5 union all
select '03',15.00,2 union all
select '03',18.00,3
select * from
tb t
where not exists(select * from tb where id!=t.id-1 and code=t.code and price=t.price and qty+t.qty=0)
/*
id code price qty
----------- ---- --------------------------------------- -----------
1 01 12.00 2
3 02 13.00 5
5 03 15.00 2
6 03 18.00 3(4 個資料列受到影響)*/
go
create table [tb] (id int identity(1,1),code nvarchar(4),price numeric(4,2),qty int)
insert into [tb]
select '01',12.00,2 union all
select '02',13.00,-5 union all
select '02',13.00,5 union all
select '02',13.00,5 union all
select '03',15.00,2 union all
select '03',18.00,3
delete t from
tb t
where exists(select * from tb where id!=t.id-1 and code=t.code and price=t.price and qty+t.qty=0)
select * from tb
/*
id code price qty
----------- ---- --------------------------------------- -----------
1 01 12.00 2
3 02 13.00 5
5 03 15.00 2
6 03 18.00 3(4 個資料列受到影響)*/
declare @tb table (Code varchar(1),price int,qty int)
insert into @tb
select 'a',1,-2 union all
select 'a',1,2 union all
select 'a',1,2 union all
select 'b',2,1delete @tb
from @tb t
where exists(select * from @tb where code=t.code and price=t.price and qty+t.qty=0)
select * from @tbCode price qty
---- ----------- -----------
b 2 1正确结果应该为:
Code price qty
---- ----------- -----------
a 1 2
b 2 1
--> Author : js_szy
--> Target : ★★★
--> Date : 2009-12-22 10:57:07
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
if object_id('tb')is not null drop table tb
go
create table tb(code varchar(2),price numeric(4,2),qty int)
insert into tb
select '01',12.00,2 union all
select '02',13.00,-5 union all
select '02',13.00,5 union all
select '02',13.00,5 union all
select '03',15.00,2 union all
select '03',18.00,3alter table tb add id int identity(1,1)delete tb
from tb t
where exists(select * from tb where code=t.code and price=t.price and id!=t.id and qty+t.qty=0)
and not exists(select * from tb where code=t.code and price=t.price and qty=t.qty and id>t.id)select code,price,qty from tb
---- --------------------------------------- -----------
01 12.00 2
02 13.00 5
03 15.00 2
03 18.00 3(4 行受影响)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-22 10:53:36
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] varchar(2),[price] numeric(4,2),[qty] int)
insert [tb]
select '01',12.00,2 union all
select '02',13.00,-5 union all
select '02',13.00,5 union all
select '02',13.00,5 union all
select '03',15.00,2 union all
select '03',18.00,3
--------------开始查询--------------------------
;with f as
(
select *,id=row_number()over(order by getdate()) from tb
)
delete
t
from
f t
where
exists(select * from f where code=t.code and price=t.price and qty+t.qty=0 and id<>t.id-1)select * from tb----------------结果----------------------------
/* code price qty
---- --------------------------------------- -----------
01 12.00 2
02 13.00 5
03 15.00 2
03 18.00 3(4 行受影响)
*/
insert into @tb
select 1,1,1,1,'A'
union all
select 2,1,1,-1,'A'
union all
select 3,1,2,2,'A'
union all
select 4,1,2,2,'A'
union all
select 5,1,2,3,'A'
union all
select 6,1,2,-2,'A'select t1.id,t1.code,t1.price,t1.qty,t1.unit from
( select row_number() over(partition by code,price, qty order by code ) as num,*from @tb) t1
left join ( select row_number() over(partition by code,price, qty order by code) as num,*from @tb ) t2
on t1.code=t2.code and t1.price =t2.price and t1.qty = -t2.qty and t1.num = t2.num
where isnull(t1.qty,0) + isnull(t2.qty,0) <>0
/*
id code price qty unit
----------- ---------------------- ---------------------- ---------------------- --------------------
4 1 2 2 A
5 1 2 3 A
*/
select t1.id,t1.code,t1.price,t1.qty,t1.unit from
( select row_number() over(partition by code,price, qty order by code ) as num,*from @tb) t1
left join ( select row_number() over(partition by code,price, qty order by code) as num,*from @tb ) t2
on t1.code=t2.code and t1.price =t2.price and t1.qty = -t2.qty and t1.num = t2.num
where isnull(t1.qty,0) + isnull(t2.qty,0) =0
/*
id code price qty unit
----------- ---------------------- ---------------------- ---------------------- --------------------
2 1 1 -1 A
1 1 1 1 A
6 1 2 -2 A
3 1 2 2 A
*/
----------- ---- --------------------------------------- -----------
1 01 12.00 2
2 02 13.00 -5
3 02 13.00 5
4 02 13.00 5
5 03 15.00 2
6 03 18.00 3
7 02 13.00 -5
--> Author : js_szy
--> Target : ★★★
--> Date : 2009-12-22 12:19:47
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (ID int,CODE varchar(2),PRICE numeric(4,2),QTY int)
insert into @tb
select 1,'01',12.00,2 union all
select 2,'02',13.00,-5 union all
select 3,'02',13.00,5 union all
select 4,'02',13.00,5 union all
select 5,'03',15.00,2 union all
select 6,'03',18.00,3 union all
select 7,'02',13.00,-5 while exists(
select 1 from @tb t
where exists(select * from @tb where code=t.code and price=t.price and id!=t.id and qty+t.qty=0)
)
delete @tb
from @tb t
where exists(select * from @tb where code=t.code and price=t.price and id!=t.id and qty+t.qty=0)
and not exists(select * from @tb where code=t.code and price=t.price and qty=t.qty and id>t.id)
select id,code,price,qty from @tb
id code price qty
----------- ---- --------------------------------------- -----------
1 01 12.00 2
5 03 15.00 2
6 03 18.00 3(3 行受影响)