表A :
salesdate salesid barcode salesprice qty status
08-15-2010 R011000036 L0000022 3360.00 1.00 sales
08-15-2010 R011000045 L0000024 3360.00 1.00 sales
09-17-2010 R011000046 L0000024 -3360.0 -1.00 return
10-13-2010 R011000066 L0000024 3360.00 1.00 sales我要取8月份的销售明细得出效果是:
salesdate salesid barcode salesprice qty
08-15-2010 R011000036 L0000022 3360.00 1.00条件是:比如说L0000024这一件货,是8月份卖出去的,但9月份退回来,10月份再买出去,
我要取8月份的数据,那么L0000024就不要取了,也就是说,首先要判断这一件有没有退回来。
如果我要取9月份的数据,那么就是也没有这一件货,因为9月份是退回来,取10月份的数据才有的。
salesdate salesid barcode salesprice qty status
08-15-2010 R011000036 L0000022 3360.00 1.00 sales
08-15-2010 R011000045 L0000024 3360.00 1.00 sales
09-17-2010 R011000046 L0000024 -3360.0 -1.00 return
10-13-2010 R011000066 L0000024 3360.00 1.00 sales我要取8月份的销售明细得出效果是:
salesdate salesid barcode salesprice qty
08-15-2010 R011000036 L0000022 3360.00 1.00条件是:比如说L0000024这一件货,是8月份卖出去的,但9月份退回来,10月份再买出去,
我要取8月份的数据,那么L0000024就不要取了,也就是说,首先要判断这一件有没有退回来。
如果我要取9月份的数据,那么就是也没有这一件货,因为9月份是退回来,取10月份的数据才有的。
from
table a
where not exist (select 1 from table where a.salesprice=-(table.price) and table.salesdate>a.salesdate)
参考下面的语句,自己写写吧.
游标的基本写法declare @id int,@name varchar(20);
declare cur cursor fast_forward for
select id,name from a;
open cur;
fetch next from cur into @id,@name;
while @@fetch_status=0
begin
--做你要做的事
fetch next from cur into @id,@name;
end
close cur;
deallocate cur;
if object_id('tempdb.dbo.#') is not null drop table #
create table #(salesdate datetime, salesid varchar(10), barcode varchar(8), salesprice float, qty float, status varchar(8))
insert into #
select '08-15-2010', 'R011000036', 'L0000022', 3360.00, 1.00, 'sales' union all
select '08-15-2010', 'R011000045', 'L0000024', 3360.00, 1.00, 'sales' union all
select '09-17-2010', 'R011000046', 'L0000024', -3360.0, -1.00, 'return' union all
select '10-23-2010', 'R011000066', 'L0000024', 3360.00, 1.00, 'sales'select * from # t where month(salesdate)=8 and status='sales'
and not exists (select 1 from # where barcode=t.barcode and salesdate>t.salesdate and status='return')/*
salesdate salesid barcode salesprice qty status
----------------------- ---------- -------- ---------------------- ---------------------- --------
2010-08-15 00:00:00.000 R011000036 L0000022 3360 1 sales
*/
and not exists
(
select 1 from # where barcode=t.barcode and salesdate>t.salesdate and status='return'
)/*
salesdate salesid barcode salesprice qty status
----------------------- ---------- -------- ---------------------- ---------------------- --------
2010-08-15 00:00:00.000 R011000036 L0000022 3360 1 sales
*/
他的例子数据本身就很多逻辑上的问题,数量倒使其次,可以关联对冲,但有什么可能根据barcode来计算sale了多少呢?类似这种问题,你可以解释得几多啊。我只不过提供方法,自己实现要懂得因地制宜,举一反三。
--LZ的意思是取货品当月sales,下一个没有返回returns的值。
--这里借用SQLCenter大哥的数据,以免--这里是8月的
select * from # t where month(salesdate)=8 and status='sales'
and not exists (select 1 from # where month(salesdate)=month(t.salesdate)+1 and barcode=t.barcode and salesdate>t.salesdate and status='return')
-- 加上month(salesdate)=month(t.salesdate)+1
--如果是其它月
select * from # t where status='sales'
and not exists (select 1 from # where month(salesdate)=month(t.salesdate)+1 and barcode=t.barcode and salesdate>t.salesdate and status='return')salesdate salesid barcode salesprice qty status
----------------------- ---------- -------- ---------------------- ---------------------- --------
2010-08-15 00:00:00.000 R011000036 L0000022 3360 1 sales
2010-10-23 00:00:00.000 R011000066 L0000024 3360 1 sales(2 行受影响)