27,582
社区成员
发帖
与我相关
我的任务
分享sql 按时间分期读取显示数据，并相减
小弟遇到两个相当棘手的问题，请各位大虾帮忙解决下：
问题：
数据库中存在一个销售的表，有客户、单价、数量、日期
如
kehu danjia shuliang riqi
---- -------- -------- ------
A 10.00 50 2008-8-8
A 10.00 30 2008-8-1
A 10.00 40 2008-7-25
A 20.00 25 2008-6-3
B 15.00 40 2008-8-2
B 10.00 60 2008-7-5
C 15.00 40 2008-6-6
D 20.00 30 2008-2-28
统计的需求就是按财务里的一个月一期来统计，将每一期的每个客户的销售额统计出来
如上的结果是
kehu 8期金额 7金额 6金额 5金额 4金额 3金额 2金额 1金额
---- ---- ---- ---- ----- ----- ---- ---- ----
A 800.00 300 500 0 0 0 0 0
B 600.00 600 0 0 0 0 0 0
C 0 0 600 0 0 0 0 0
D 0 0 0 0 0 0 600 0
第二个问题（和第一格问题有关）
现在有格表就是客户余额表，要根据用户的每期的购货金额来分配。
kehu yu'e
----- -----
A 1200.00
B 300.00
C 0
D 0
则
kehu 8期余额 7余额 6余额 5余额 4余额 3余额 2余额 1余额
---- ---- ---- ---- ----- ----- ---- ---- ----
A 800.00 300.00 100.00 0 0 0 0 0
B 300.00 0 0 0 0 0 0 0
C 0 0 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0
方法："总余额"-"本期金额"="余额1"，如果"余额1">0,则本期余额=本期金额，并且再用"余额1"-"下期金额"="余额2",直到相减<=0.
大侠们帮看看哈！分不够可以加哈
问题：
数据库中存在一个销售的表，有客户、单价、数量、日期
如
kehu danjia shuliang riqi
---- -------- -------- ------
A 10.00 50 2008-8-8
A 10.00 30 2008-8-1
A 10.00 40 2008-7-25
A 20.00 25 2008-6-3
B 15.00 40 2008-8-2
B 10.00 60 2008-7-5
C 15.00 40 2008-6-6
D 20.00 30 2008-2-28
统计的需求就是按财务里的一个月一期来统计，将每一期的每个客户的销售额统计出来
如上的结果是
kehu 8期金额 7金额 6金额 5金额 4金额 3金额 2金额 1金额
---- ---- ---- ---- ----- ----- ---- ---- ----
A 800.00 300 500 0 0 0 0 0
B 600.00 600 0 0 0 0 0 0
C 0 0 600 0 0 0 0 0
D 0 0 0 0 0 0 600 0
第二个问题（和第一格问题有关）
现在有格表就是客户余额表，要根据用户的每期的购货金额来分配。
kehu yu'e
----- -----
A 1200.00
B 300.00
C 0
D 0
则
kehu 8期余额 7余额 6余额 5余额 4余额 3余额 2余额 1余额
---- ---- ---- ---- ----- ----- ---- ---- ----
A 800.00 300.00 100.00 0 0 0 0 0
B 300.00 0 0 0 0 0 0 0
C 0 0 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0
方法："总余额"-"本期金额"="余额1"，如果"余额1">0,则本期余额=本期金额，并且再用"余额1"-"下期金额"="余额2",直到相减<=0.
大侠们帮看看哈！分不够可以加哈
...全文
请发表友善的回复…
发表回复
yfcomeon 2008-08-08
- 打赏
- 举报
kehu 8期余额 7余额 6余额 5余额 4余额 3余额 2余额 1余额
---- ---- ---- ---- ----- ----- ---- ---- ----
A 800.00 300.00 100.00 0 0 0 0 0
B 300.00 0 0 0 0 0 0 0
C 0 0 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0
第四个应该是这样的
第二个问题，你的解法看不大懂，能说下原理么
---- ---- ---- ---- ----- ----- ---- ---- ----
A 800.00 300.00 100.00 0 0 0 0 0
B 300.00 0 0 0 0 0 0 0
C 0 0 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0
第四个应该是这样的
第二个问题，你的解法看不大懂，能说下原理么
中国风 2008-08-08
- 打赏
- 举报
[Quote=引用 5 楼 yfcomeon 的回复:]
就是余额要平分到每一期去，先分给最近的一期，如果还分不完再分给下一期，这样一直下去，直到都分配完。每一期最多分配的额就是他自己的销售额。
[/Quote]
看看上面是不是你要的結果..
問題1：1樓
問題2：4樓
就是余额要平分到每一期去，先分给最近的一期，如果还分不完再分给下一期，这样一直下去，直到都分配完。每一期最多分配的额就是他自己的销售额。
[/Quote]
看看上面是不是你要的結果..
問題1：1樓
問題2：4樓
yfcomeon 2008-08-08
- 打赏
- 举报
我们这里是赊账的，就是钱要还的话，肯定就是先还最早的欠款。我这里的余额就是客户的欠我们的欠款，分配下去就是每期客户欠我们的钱。可能是我没说清楚，不好意思。
yfcomeon 2008-08-08
- 打赏
- 举报
就是余额要平分到每一期去，先分给最近的一期，如果还分不完再分给下一期，这样一直下去，直到都分配完。每一期最多分配的额就是他自己的销售额。
中国风 2008-08-08
- 打赏
- 举报
銷售不是先進先出？
樓主的第二個結果集為后進先出
樓主的第二個結果集為后進先出
declare @month nvarchar(10),@s nvarchar(4000)
select top 1 @month=month([riqi]),@s='' from #T order by month([riqi]) desc
while @month>0
select @s=@s+',['+@month+N'金额]=sum(case when month([riqi])='+@month+' then Price else 0 end)',@month=@month-1
exec('select [kehu]'+@s+
'from
(select a.[kehu],case when b.[yue]<a.sumPrice-(a.[danjia]*a.[shuliang]) then 0 when b.[yue]<a.sumPrice then b.[yue]-(a.sumPrice-(a.[danjia]*a.[shuliang]))
else a.[danjia]*a.[shuliang] end as Price,[riqi]
from
(select *,sumPrice=(select sum([danjia]*[shuliang]) from #T where [kehu]=a.[kehu] and [riqi]>=a.[riqi])from #T a)a
join
#T2 b on a.[kehu]=b.[kehu])T
group by [kehu],year([riqi])')
/*
kehu 8金额 7金额 6金额 5金额 4金额 3金额 2金额 1金额
---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A 800.00 400.00 0.00 0.00 0.00 0.00 0.00 0.00
B 300.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
C 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
D 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
(4 個資料列受到影響)
*/
中国风 2008-08-08
- 打赏
- 举报
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([kehu] nvarchar(1),[danjia] decimal(18,2),[shuliang] int,[riqi] Datetime)
Insert #T
select N'A',10.00,50,'2008-8-8' union all
select N'A',10.00,30,'2008-8-1' union all
select N'A',10.00,40,'2008-7-25' union all
select N'A',20.00,25,'2008-6-3' union all
select N'B',15.00,40,'2008-8-2' union all
select N'B',10.00,60,'2008-7-5' union all
select N'C',15.00,40,'2008-6-6' union all
select N'D',20.00,30,'2008-2-28'
Go
if not object_id('Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([kehu] nvarchar(1),[yue] decimal(18,2))
Insert #T2
select N'A',1200.00 union all
select N'B',300.00 union all
select N'C',0 union all
select N'D',0
Go
declare @month nvarchar(10),@s nvarchar(4000)
select top 1 @month=month([riqi]),@s='' from #T order by month([riqi]) desc
while @month>0
select @s=@s+',['+@month+N'金额]=sum(case when month([riqi])='+@month+' then Price else 0 end)',@month=@month-1
exec('select [kehu]'+@s+
'from
(select a.[kehu],case when b.[yue]>=a.sumPrice then 0 when b.[yue]>a.sumPrice-(a.[danjia]*a.[shuliang]) then a.sumPrice-b.[yue] --改改
else a.[danjia]*a.[shuliang] end as Price,[riqi]
from
(select *,sumPrice=(select sum([danjia]*[shuliang]) from #T where [kehu]=a.[kehu] and [riqi]<=a.[riqi])from #T a)a
join
#T2 b on a.[kehu]=b.[kehu])T
group by [kehu],year([riqi])')
kehu 8金额 7金额 6金额 5金额 4金额 3金额 2金额 1金额
---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A 500.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
B 600.00 300.00 0.00 0.00 0.00 0.00 0.00 0.00
C 0.00 0.00 600.00 0.00 0.00 0.00 0.00 0.00
D 0.00 0.00 0.00 0.00 0.00 0.00 600.00 0.00
(4 個資料列受到影響)
中国风 2008-08-08
- 打赏
- 举报
樓主的結果集與數據是否一致，猜一個
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([kehu] nvarchar(1),[danjia] decimal(18,2),[shuliang] int,[riqi] Datetime)
Insert #T
select N'A',10.00,50,'2008-8-8' union all
select N'A',10.00,30,'2008-8-1' union all
select N'A',10.00,40,'2008-7-25' union all
select N'A',20.00,25,'2008-6-3' union all
select N'B',15.00,40,'2008-8-2' union all
select N'B',10.00,60,'2008-7-5' union all
select N'C',15.00,40,'2008-6-6' union all
select N'D',20.00,30,'2008-2-28'
Go
if not object_id('Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([kehu] nvarchar(1),[yue] decimal(18,2))
Insert #T2
select N'A',1200.00 union all
select N'B',300.00 union all
select N'C',0 union all
select N'D',0
Go
declare @month nvarchar(10),@s nvarchar(4000)
select top 1 @month=month([riqi]),@s='' from #T order by month([riqi]) desc
while @month>0
select @s=@s+',['+@month+N'金额]=sum(case when month([riqi])='+@month+' then Price else 0 end)',@month=@month-1
exec('select [kehu]'+@s+
'from
(select a.[kehu],case when b.[yue]>=a.sumPrice then 0 when b.[yue]>a.sumPrice-(a.[danjia]*a.[shuliang]) then a.sumPrice-(a.[danjia]*a.[shuliang])
else a.[danjia]*a.[shuliang] end as Price,[riqi]
from
(select *,sumPrice=(select sum([danjia]*[shuliang]) from #T where [kehu]=a.[kehu] and [riqi]<=a.[riqi])from #T a)a
join
#T2 b on a.[kehu]=b.[kehu])T
group by [kehu],year([riqi])')
kehu 8金额 7金额 6金额 5金额 4金额 3金额 2金额 1金额
---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A 500.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
B 600.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
C 0.00 0.00 600.00 0.00 0.00 0.00 0.00 0.00
D 0.00 0.00 0.00 0.00 0.00 0.00 600.00 0.00
(4 個資料列受到影響)
中国风 2008-08-08
- 打赏
- 举报
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([kehu] nvarchar(1),[danjia] decimal(18,2),[shuliang] int,[riqi] Datetime)
Insert #T
select N'A',10.00,50,'2008-8-8' union all
select N'A',10.00,30,'2008-8-1' union all
select N'A',10.00,40,'2008-7-25' union all
select N'A',20.00,25,'2008-6-3' union all
select N'B',15.00,40,'2008-8-2' union all
select N'B',10.00,60,'2008-7-5' union all
select N'C',15.00,40,'2008-6-6' union all
select N'D',20.00,30,'2008-2-28'
Go
declare @month nvarchar(10),@s nvarchar(4000)
select top 1 @month=month([riqi]),@s='' from #T order by month([riqi]) desc
while @month>0
select @s=@s+',['+@month+N'金额]=sum(case when month([riqi])='+@month+' then [danjia]*[shuliang] else 0 end)',@month=@month-1
exec('select [kehu]'+@s+' from #T group by [kehu],year([riqi])')
kehu 8金额 7金额 6金额 5金额 4金额 3金额 2金额 1金额
---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A 800.00 400.00 500.00 0.00 0.00 0.00 0.00 0.00
B 600.00 600.00 0.00 0.00 0.00 0.00 0.00 0.00
C 0.00 0.00 600.00 0.00 0.00 0.00 0.00 0.00
D 0.00 0.00 0.00 0.00 0.00 0.00 600.00 0.00
(4 個資料列受到影響)
中国风 2008-08-08
- 打赏
- 举报
[Quote=引用 8 楼 yfcomeon 的回复:]
kehu 8期余额 7余额 6余额 5余额 4余额 3余额 2余额 1余额
---- ---- ---- ---- ----- ----- ---- ---- ----
A 800.00 300.00 100.00 0 0 0 0 0
B 300.00 0 0 0 0 0 0 0
C 0 0 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0
第四个应该是这样的
第二…
[/Quote]
第二个问题是采用后進先出,通過客户金額表的余额，扣減銷售表金額。。
kehu 8期余额 7余额 6余额 5余额 4余额 3余额 2余额 1余额
---- ---- ---- ---- ----- ----- ---- ---- ----
A 800.00 300.00 100.00 0 0 0 0 0
B 300.00 0 0 0 0 0 0 0
C 0 0 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0
第四个应该是这样的
第二…
[/Quote]
第二个问题是采用后進先出,通過客户金額表的余额，扣減銷售表金額。。
中国风 2008-08-08
- 打赏
- 举报
A 10.00 40 2008-7-25
A 20.00 25 2008-6-3
A的6月為500=20*25
A的7月為400=10*40
--以上300在那里來的
kehu 8期金额 7金额 6金额 5金额 4金额 3金额 2金额 1金额
---- ---- ---- ---- ----- ----- ---- ---- ----
A 800.00 300 500 0 0 0 0 0
B 600.00 600 0 0 0 0 0 0
C 0 0 600 0 0 0 0 0
D 0 0 0 0 0 0 600 0
A 20.00 25 2008-6-3
A的6月為500=20*25
A的7月為400=10*40
--以上300在那里來的
kehu 8期金额 7金额 6金额 5金额 4金额 3金额 2金额 1金额
---- ---- ---- ---- ----- ----- ---- ---- ----
A 800.00 300 500 0 0 0 0 0
B 600.00 600 0 0 0 0 0 0
C 0 0 600 0 0 0 0 0
D 0 0 0 0 0 0 600 0
