billno(*) billtype outdate money
1 销售 2011-01-01 100
2 销售 2011-01-02 100
3 回款 2011-02-10 50
4 回款 2011-02-12 100
5 销售 2011-03-02 100
---------------------------------------------------
想得到如下结果:
billno billtype outdate money hkdate(回款日期) hkmoney(回款金额)
1 销售 2011-01-01 100 2011-02-10 50
1 销售 2011-01-01 100 2011-02-12 50
2 销售 2011-01-02 100 2011-02-12 50
5 销售 2011-03-02 100 null 0就是需要得到每笔销售款的具体回款日期是多少号。。麻烦各位大虾们了。。
1 销售 2011-01-01 100
2 销售 2011-01-02 100
3 回款 2011-02-10 50
4 回款 2011-02-12 100
5 销售 2011-03-02 100
---------------------------------------------------
想得到如下结果:
billno billtype outdate money hkdate(回款日期) hkmoney(回款金额)
1 销售 2011-01-01 100 2011-02-10 50
1 销售 2011-01-01 100 2011-02-12 50
2 销售 2011-01-02 100 2011-02-12 50
5 销售 2011-03-02 100 null 0就是需要得到每笔销售款的具体回款日期是多少号。。麻烦各位大虾们了。。
解决方案 »
- 多表关联 求和的问题
- 各位高手能高抬贵手教我如何在SQL2005 中创建作业和调度吗?在下感激在心呐!
- 关于时间格式
- 一个小存储过程的问题,大家帮帮忙
- 送分问题~~~~~~``急~~~~~~~~~~~~~
- SQL语句操作excel的sheet1表的一个实例,望指导!
- ///怎么统计一个值在字段里出现过几次???
- 一个高难度的SQL问题,求教解决办法。
- 数据拷到服务器下的各数据库,同时复制到另一数据库???(在线等待)
- 如何实现从存储过程的执行结果中筛选数据
- 不显示删除回复显示所有回复显示星级回复显示得分回复 vb通过ADO COMMAND执行存储过程,“对象关闭时,不允许操作”[问题点数:20分]
- 求SQL SERVER 2008 R2 教程
------------------------------------------------------
3楼 NF_kyle
销售和回款是在一张表里面的,没有任何联系
create table paylog (
gold int not null,
paydate datetime not null,
des nvarchar(10) not null
)
set nocount on
insert into paylog
select '80','2010-09-10 13:18','付费'
union all
select '100','2010-09-11 14:18','赠送'
union all
select '30','2010-09-12 14:20','赠送'
union all
select '40','2010-09-13 14:20','付费'
union all
select '90','2010-09-14 14:20','付费'
union all
select '30','2010-09-15 14:20','赠送'
gocreate table costlog (
gold int not null,
costdate datetime not null
)
insert into costlog
select '50','2010-09-12 14:00'
union all
select '80','2010-09-13 14:18'
union all
select '10','2010-09-14 14:20'
union all
select '60','2010-09-15 14:20'
union all
select '50','2010-09-16 14:20'
union all
select '1000','2010-09-17 14:20'
go
--结果
/*
gold paydate des gold costdate details
80 2010-09-10 13:18:00.000 付费 50 2010-09-12 14:00:00.000 50
80 2010-09-10 13:18:00.000 付费 80 2010-09-13 14:18:00.000 30
100 2010-09-11 14:18:00.000 赠送 80 2010-09-13 14:18:00.000 50
100 2010-09-11 14:18:00.000 赠送 10 2010-09-14 14:20:00.000 10
100 2010-09-11 14:18:00.000 赠送 60 2010-09-15 14:20:00.000 40
30 2010-09-12 14:20:00.000 赠送 60 2010-09-15 14:20:00.000 20
30 2010-09-12 14:20:00.000 赠送 50 2010-09-16 14:20:00.000 10
40 2010-09-13 14:20:00.000 付费 50 2010-09-16 14:20:00.000 40
90 2010-09-14 14:20:00.000 付费 1000 2010-09-17 14:20:00.000 90
30 2010-09-15 14:20:00.000 赠送 1000 2010-09-17 14:20:00.000 30
*/
--1:
create table num(indexno int)
declare @i int
set @i=1
while @i<=2000
begin
insert into num
select @i
set @i=@i+1
end
go;with pay_t as (select row_number() over (order by paydate) as indexno,a.*
from paylog a,num b where a.gold>=b.indexno
),cost_t as (
select row_number() over (order by costdate) as indexno,a.*
from costlog a,num b where a.gold>=b.indexno
)select a.gold,a.paydate,a.des,b.gold,b.costdate,count(1) as details
from pay_t a,cost_t b where a.indexno=b.indexno group by a.gold,a.paydate,a.des,b.gold,b.costdate
order by b.costdate,a.paydate
go--这个实现比较容易,但不适合小数。
--
--2:with t1 as
(
select id = row_number() over(order by getdate()),*,sumgold=(select sum(gold) from paylog where paydate <= a.paydate) from paylog a
)
,t2 as
(
select id = row_number() over(order by getdate()),*,sumgold=(select sum(gold) from costlog where costdate <= a.costdate) from costlog a
)
,t3 as
(
select a.*,bid1=b.id
from t1 a
cross apply (select top (1) * from t2 where sumgold >= a.sumgold order by sumgold) b
)
,t4 as
(
select a.*,bid2=isnull(b.bid1,1) from t3 a left join t3 b on a.id = b.id + 1
)
,t5 as
(
select paygold=a.gold,a.paydate,a.des,costgold=b.gold,b.costdate,details=
case
when a.sumgold>=b.sumgold then
case when a.gold-a.sumgold+b.sumgold >= b.gold then b.gold else a.gold-a.sumgold+b.sumgold end
else
case when b.gold-b.sumgold+a.sumgold >= a.gold then a.gold else b.gold-b.sumgold+a.sumgold end
end
from t4 a join t2 b on b.id between a.bid2 and a.bid1
)
select * from t5 where details > 0
go--这个大家容易迷糊,但弄懂不难,主要是效率有点低!
--
--3:create table cun(gold int,paydate datetime,[des] nvarchar(10),go int,costdate datetime,details int)
godeclare my_cursor cursor scroll
for
select * from paylog
open my_cursor
declare @gold int
declare @paydate datetime
declare @des nvarchar(10)
set @gold = 0
fetch next from my_cursor into @gold,@paydate,@des
while (@@fetch_status = 0)
begin
declare next_cursor cursor scroll
for
select * from costlog
open next_cursor
declare @go int
declare @costdate datetime
declare @details int
declare @gocun int
declare @decun int
set @decun = 0
set @go = 0
set @details = 0
set @gocun = @gold
fetch next from next_cursor into @go,@costdate
set @gocun = @gocun - @go
while(@@fetch_status = 0)
begin
if(@gocun = 0)
begin
set @details = @gold
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from my_cursor into @gold,@paydate,@des
fetch next from next_cursor into @go,@costdate
set @gocun = @gold - @go
set @decun = 0
end
if(@gocun > 0)
begin
set @details = @go - @decun
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from next_cursor into @go,@costdate
set @gocun = @gocun - @go
set @decun = 0
end
if(@gocun < 0)
begin
set @details = @go + @gocun - @decun
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from my_cursor into @gold,@paydate,@des
set @gocun = @gold + @gocun
set @decun = @details
end
end
close next_cursor
deallocate next_cursor
end
close my_cursor
deallocate my_cursor
select * from cun
goset nocount off
drop table paylog,costlog,cun,num/***************gold paydate des gold costdate details
----------- ----------------------- ---------- ----------- ----------------------- -----------
80 2010-09-10 13:18:00.000 付费 50 2010-09-12 14:00:00.000 50
80 2010-09-10 13:18:00.000 付费 80 2010-09-13 14:18:00.000 30
100 2010-09-11 14:18:00.000 赠送 80 2010-09-13 14:18:00.000 50
100 2010-09-11 14:18:00.000 赠送 10 2010-09-14 14:20:00.000 10
100 2010-09-11 14:18:00.000 赠送 60 2010-09-15 14:20:00.000 40
30 2010-09-12 14:20:00.000 赠送 60 2010-09-15 14:20:00.000 20
30 2010-09-12 14:20:00.000 赠送 50 2010-09-16 14:20:00.000 10
40 2010-09-13 14:20:00.000 付费 50 2010-09-16 14:20:00.000 40
90 2010-09-14 14:20:00.000 付费 1000 2010-09-17 14:20:00.000 90
30 2010-09-15 14:20:00.000 赠送 1000 2010-09-17 14:20:00.000 30paygold paydate des costgold costdate details
----------- ----------------------- ---------- ----------- ----------------------- -----------
80 2010-09-10 13:18:00.000 付费 50 2010-09-12 14:00:00.000 50
80 2010-09-10 13:18:00.000 付费 80 2010-09-13 14:18:00.000 30
100 2010-09-11 14:18:00.000 赠送 80 2010-09-13 14:18:00.000 50
100 2010-09-11 14:18:00.000 赠送 10 2010-09-14 14:20:00.000 10
100 2010-09-11 14:18:00.000 赠送 60 2010-09-15 14:20:00.000 40
30 2010-09-12 14:20:00.000 赠送 60 2010-09-15 14:20:00.000 20
30 2010-09-12 14:20:00.000 赠送 50 2010-09-16 14:20:00.000 10
40 2010-09-13 14:20:00.000 付费 50 2010-09-16 14:20:00.000 40
90 2010-09-14 14:20:00.000 付费 1000 2010-09-17 14:20:00.000 90
30 2010-09-15 14:20:00.000 赠送 1000 2010-09-17 14:20:00.000 30gold paydate des go costdate details
----------- ----------------------- ---------- ----------- ----------------------- -----------
80 2010-09-10 13:18:00.000 付费 50 2010-09-12 14:00:00.000 50
80 2010-09-10 13:18:00.000 付费 80 2010-09-13 14:18:00.000 30
100 2010-09-11 14:18:00.000 赠送 80 2010-09-13 14:18:00.000 50
100 2010-09-11 14:18:00.000 赠送 10 2010-09-14 14:20:00.000 10
100 2010-09-11 14:18:00.000 赠送 60 2010-09-15 14:20:00.000 40
30 2010-09-12 14:20:00.000 赠送 60 2010-09-15 14:20:00.000 20
30 2010-09-12 14:20:00.000 赠送 50 2010-09-16 14:20:00.000 10
40 2010-09-13 14:20:00.000 付费 50 2010-09-16 14:20:00.000 40
90 2010-09-14 14:20:00.000 付费 1000 2010-09-17 14:20:00.000 90
30 2010-09-15 14:20:00.000 赠送 1000 2010-09-17 14:20:00.000 30
create table tb(billno int,billtype varchar(20),outdate datetime,[money] int)
insert into tb
select 1 ,'销售' ,'2011-01-01' ,100 union all
select 2 ,'销售' ,'2011-01-02' ,100 union all
select 3 ,'回款' ,'2011-02-10' ,50 union all
select 4 ,'回款' ,'2011-02-12' ,100 union all
select 5 ,'销售' ,'2011-03-02' ,100
go
---------------------------------------------------
--想得到如下结果:
--billno billtype outdate money hkdate(回款日期) hkmoney(回款金额)
--1 销售 2011-01-01 100 2011-02-10 50
--1 销售 2011-01-01 100 2011-02-12 50
--2 销售 2011-01-02 100 2011-02-12 50
--5 销售 2011-03-02 100 null 0
--create table cun
(
billno int,
billtype varchar(20),
outdate datetime,
[money] int,
hkdate datetime,
hkmoney int
)
godeclare my_cursor cursor scroll
for
select * from tb where billtype = '销售' order by billno
open my_cursor
declare @billno int
declare @billtype varchar(20)
declare @outdate datetime
declare @money int
fetch next from my_cursor into @billno,@billtype,@outdate,@money
while (@@fetch_status = 0)
begin
declare next_cursor cursor scroll
for
select * from tb where billtype = '回款' order by billno
open next_cursor
declare @hkbillno int
declare @hkbilltype varchar(20)
declare @hkoutdate datetime
declare @hkmoney int
fetch next from next_cursor into @hkbillno,@hkbilltype,@hkoutdate,@hkmoney
while(@@fetch_status = 0)
begin
if(@money>=@hkmoney)
begin
insert into cun select @billno,@billtype,@outdate,@money,@hkoutdate,@money-@hkmoney
set @money=@money-@hkmoney
fetch next from next_cursor into @hkbillno,@hkbilltype,@hkoutdate,@hkmoney
end
if(@money<@hkmoney)
begin
insert into cun select @billno,@billtype,@outdate,@money,@hkoutdate,@hkmoney-@money
set @hkmoney=@hkmoney-@money
fetch next from my_cursor into @billno,@billtype,@outdate,@money
end
end
close next_cursor
deallocate next_cursor
end
close my_cursor
deallocate my_cursor
select * from cun
union all
select billno,billtype,outdate,[money],null,null
from tb t
where not exists (select 1 from cun where billno = t.billno and billtype = t.billtype)
and billtype = '销售'
godrop table tb,cun/******************billno billtype outdate money hkdate hkmoney
----------- -------------------- ----------------------- ----------- ----------------------- -----------
1 销售 2011-01-01 00:00:00.000 100 2011-02-10 00:00:00.000 50
1 销售 2011-01-01 00:00:00.000 50 2011-02-12 00:00:00.000 50
2 销售 2011-01-02 00:00:00.000 100 2011-02-12 00:00:00.000 50
5 销售 2011-03-02 00:00:00.000 100 NULL NULL(4 行受影响)