出库表：编号 日期 数量
----------------------------
001 2009-1-1 30
001 2009-1-15 -2
001 2009-1-18 -5
001 2009-2-1 15
001 2009-2-2 -10
001 2009-2-15 -2要求查询得出（查询截止日期2009-3-1），需要考虑先进先出：编号 库龄小于30天 库龄30-89天 库龄90天以上
-------------------------------------------------------------
001 15 11 0这样的sql语句怎样写？
----------------------------
001 2009-1-1 30
001 2009-1-15 -2
001 2009-1-18 -5
001 2009-2-1 15
001 2009-2-2 -10
001 2009-2-15 -2要求查询得出（查询截止日期2009-3-1），需要考虑先进先出：编号 库龄小于30天 库龄30-89天 库龄90天以上
-------------------------------------------------------------
001 15 11 0这样的sql语句怎样写？
但不明白的是库龄分析跟先进先出有什么关系吗？
编号,
库龄小于30天=sum(case when datediff(dd,日期,'2009-3-1')<30 then 数量 else 0 end),
库龄小于库龄30-89天=sum(case when datediff(dd,日期,'2009-3-1') between 31 and 89 then 数量 else 0 end),
库龄90天以上=sum(case when datediff(dd,日期,'2009-3-1')>90 then 数量 else 0 end)
group by
编号
datediff(day,rq,getdate())>=30 and datediff(day,rq,getdate())<=89
datediff(day,rq,getdate())>=90
分别对应小于30，30-89，超过90在WHERE中指定即可
----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-08-31 16:11:20
-- Verstion:
-- 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]([编号] varchar(3),[日期] datetime,[数量] int)
insert [tb]
select '001','2009-1-1',30 union all
select '001','2009-1-15',-2 union all
select '001','2009-1-18',-5 union all
select '001','2009-2-1',15 union all
select '001','2009-2-2',-10 union all
select '001','2009-2-15',-2
--------------开始查询--------------------------
select
编号,
库龄小于30天=sum(case when datediff(dd,日期,'2009-3-1')<30 then 数量 else 0 end),
[库龄小于库龄30-89天]=sum(case when datediff(dd,日期,'2009-3-1') between 31 and 89 then 数量 else 0 end),
库龄90天以上=sum(case when datediff(dd,日期,'2009-3-1')>90 then 数量 else 0 end)
from
tb
group by
编号----------------结果----------------------------
/* 编号 库龄小于30天 库龄小于库龄30-89天 库龄90天以上
---- ----------- ------------ -----------
001 3 23 0(1 行受影响)
*/
insert into @t1 values('001', '2009-1-15', -2 )
insert into @t1 values('001', '2009-1-18', -5 )
insert into @t1 values('001', '2009-2-1', 15 )
insert into @t1 values('001', '2009-2-2', -10 )
insert into @t1 values('001', '2009-2-15', -2 )select a.编号 ,
SUM( case a.日期 when 0 then a.数量 else 0 end) as 库龄小于30天,
SUM( case a.日期 when 1 then a.数量 else 0 end) as 库龄3089天 ,
SUM( case a.日期 when 2 then a.数量 else 0 end) as 库龄90天以上
from
(select 编号, 数量, case when DATEDIFF(day,日期,'2009-3-1') <30 then 0
when DATEDIFF(day,日期,'2009-3-1') between 30 and 89 then 1
else 2 end as 日期
from @t1)a group by a.编号
declare @t1 table( 编号 varchar(10), 日期 date , 数量 int)insert into @t1 values('001', '2009-1-1', 30 )
insert into @t1 values('001', '2009-1-15', -2 )
insert into @t1 values('001', '2009-1-18', -5 )
insert into @t1 values('001', '2009-2-1', 15 )
insert into @t1 values('001', '2009-2-2', -10 )
insert into @t1 values('001', '2009-2-15', -2 )select b.编号,
case when b.[库龄90天以上]+b.[库龄3089天以上负]+b.库龄3089天以上正 + b.库龄小于30天负>=0 then b.库龄小于30天正
else b.库龄小于30天正 + b.[库龄90天以上]+b.[库龄3089天以上负]+b.库龄3089天以上正 + b.库龄小于30天负
end as '库龄小于30天', case when b.[库龄90天以上] + b.库龄3089天以上负 + b.库龄小于30天负 >=0 then b.库龄3089天以上正
when b.[库龄90天以上] + b.库龄3089天以上负 + b.库龄小于30天负 + b.库龄3089天以上正 >=0 then
b.[库龄90天以上] + b.库龄3089天以上负 + b.库龄小于30天负 + b.库龄3089天以上正
else 0
end as '库龄3089天',
b.[库龄90天以上]
from(
select
a.编号,
(select isnull(SUM(数量),0) from @t1 where 编号=a.编号 and
DATEDIFF(day,日期,'2009-3-1') >89) as '库龄90天以上',
(select isnull(SUM(数量),0) from @t1 where 编号=a.编号 and
DATEDIFF(day,日期,'2009-3-1') between 30 and 89 and 数量>=0) as '库龄3089天以上正',
(select isnull(SUM(数量),0) from @t1 where 编号=a.编号 and
DATEDIFF(day,日期,'2009-3-1') between 30 and 89 and 数量<0) as '库龄3089天以上负',
(select isnull(SUM(数量),0) from @t1 where 编号=a.编号 and
DATEDIFF(day,日期,'2009-3-1') <30 and 数量>=0) as '库龄小于30天正',
(select isnull(SUM(数量),0) from @t1 where 编号=a.编号 and
DATEDIFF(day,日期,'2009-3-1') <30 and 数量<0) as '库龄小于30天负'
from @t1 a group by a.编号)b
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] varchar(3),[日期] datetime,[数量] int)
insert [tb]
select '001','2009-1-1',30 union all
select '001','2009-1-15',-2 union all
select '001','2009-1-18',-5 union all
select '001','2009-2-1',15 union all
select '001','2009-2-2',-10 union all
select '001','2009-2-15',-2
---查询---
select
a.编号,
a.日期,
a.入库数量,
b.出库总量
into #
from
(select 编号,日期,数量 as 入库数量 from tb where 数量>0) a
left join
(select 编号,sum(数量) as 出库总量 from tb where 数量<0 group by 编号) b
on
a.编号=b.编号select
编号,
[库龄小于30天]=sum(case when datediff(day,日期,'2009-3-1')<30 then 库存 else 0 end),
[库龄30-89天]=sum(case when datediff(day,日期,'2009-3-1') between 30 and 89 then 库存 else 0 end),
[库龄90天以上]=sum(case when datediff(day,日期,'2009-3-1')>=90 then 库存 else 0 end)
from
(
select
编号,
日期,
库存=case when 库存<0 then 0 else case when 入库数量<库存 then 入库数量 else 库存 end end
from
(
select *,(select sum(入库数量) from # where 日期<=t.日期)+出库总量 as 库存 from # t
) t) tt
group by
编号drop table #---结果---
编号 库龄小于30天 库龄30-89天 库龄90天以上
---- ----------- ----------- -----------
001 15 11 0（所影响的行数为 1 行）
if object_id('[tab]') is not null drop table [tb]
go
create table [tab]([编号] varchar(3),[日期] datetime,[数量] int)
insert [tab]
select '001','2009-1-1',30 union all
select '001','2009-1-15',-2 union all
select '001','2009-1-18',-5 union all
select '001','2009-2-1',15 union all
select '001','2009-2-2',-10 union all
select '001','2009-2-15',-2
--select * from tab
select [编号], sum(case when datediff(dd,[日期],'2009-03-01')<30 then [数量] else 0 end) as '30',
sum(case when datediff(dd,[日期],'2009-03-01') between 30 and 89 then [数量] else 0 end) as '3089',
sum(case when datediff(dd,[日期],'2009-03-01')>=90 then [数量] else 0 end) as '90'
into #in
from tab
where [数量]>0
group by [编号]select [编号], sum(case when datediff(dd,[日期],'2009-03-01')<30 then [数量] else 0 end) as '30',
sum(case when datediff(dd,[日期],'2009-03-01') between 30 and 89 then [数量] else 0 end) as '3089',
sum(case when datediff(dd,[日期],'2009-03-01')>=90 then [数量] else 0 end) as '90'
into #out
from tab
where [数量]<0
group by [编号]
--select * from #in
--select * from #outselect a.[编号], case when a.[3089]+a.[90]+b.[30]+b.[3089]+b.[90]>=0 then a.[30] else a.[30]+a.[3089]+a.[90]+b.[30]+b.[3089]+b.[90] end as '库龄小于30天',
case when a.[90]+b.[30]+b.[3089]+b.[90]>=0 then a.[3089] else a.[3089]+a.[90]+b.[30]+b.[3089]+b.[90] end as '库龄30-89天',
case when a.[90]+b.[30]+b.[3089]+b.[90]>0 then a.[90]+b.[30]+b.[3089]+b.[90] else 0 end as '库龄90天以上'
from #in a inner join #out b on a.[编号] = b.[编号]
/*---------------------------------------
瘦狼阿亮 | 钱不是问题，问题是没钱！我乐于享受学习的过程
---------------------------------------*/--建立测试环境表
declare @tmp table
(listID varchar(10),baseDate datetime,baseAmount smallint)/*表字段说明
listID--编号
baseDate--日期
baseAmount--数量
*/--插入测试数据
insert into @tmp
select '001','2009-01-01',30 union all
select '001','2009-01-15',-2 union all
select '001','2009-01-18',-5 union all
select '001','2009-02-01',15 union all
select '001','2009-02-02',-10 union all
select '001','2009-02-15',-2 /*执行结果：
（所影响的行数为 6 行）
*/--检查测试数据
select * from @tmp/*执行结果：
listID baseDate baseAmount
---------- ------------------------------------------------------ ----------
001 2009-01-01 00:00:00.000 30
001 2009-01-15 00:00:00.000 -2
001 2009-01-18 00:00:00.000 -5
001 2009-02-01 00:00:00.000 15
001 2009-02-02 00:00:00.000 -10
001 2009-02-15 00:00:00.000 -2（所影响的行数为 6 行）
*/--查询结果与数据测试比对
select basedate,adddate=datediff(day,basedate,'2009-03-01'),baseAmount from @tmp/*执行结果：
basedate adddate baseAmount
------------------------------------------------------ ----------- ----------
2009-01-01 00:00:00.000 59 30
2009-01-15 00:00:00.000 45 -2
2009-01-18 00:00:00.000 42 -5
2009-02-01 00:00:00.000 28 15
2009-02-02 00:00:00.000 27 -10
2009-02-15 00:00:00.000 14 -2（所影响的行数为 6 行）
*/--执行查询
select
listID,
smallin30_Amount=sum(case when datediff(dd,baseDate,'2009-03-01')<30 then baseAmount else 0 end),
smallin89_Amount=sum(case when datediff(dd,baseDate,'2009-03-01') between 30 and 89 then baseAmount else 0 end),
bigin90_Amount=sum(case when datediff(dd,baseDate,'2009-03-01')>=90 then baseAmount else 0 end)
from @tmp
group by listID/*执行结果：
listID smallin30_Amount smallin89_Amount bigin90_Amount
---------- ---------------- ---------------- --------------
001 3 23 0（所影响的行数为 1 行）
*/
想知道LZ的结果是如何得出的？如果我整错了，大家就一阵地拍砖吧……在执行查询以前，我做了一个数据比对查询：
--查询结果与数据测试比对
select basedate,adddate=datediff(day,basedate,'2009-03-01'),baseAmount from @tmp/*执行结果：
basedate adddate baseAmount
------------------------------------------------------ ----------- ----------
2009-01-01 00:00:00.000 59 30
2009-01-15 00:00:00.000 45 -2
2009-01-18 00:00:00.000 42 -5
2009-02-01 00:00:00.000 28 15
2009-02-02 00:00:00.000 27 -10
2009-02-15 00:00:00.000 14 -2（所影响的行数为 6 行）
*/这个查询就是为了验证自己的结果是否正确。1、从数据上看，以2009－03－01为截至日期，库龄小于30天的记录如下：
basedate adddate baseAmount
------------------------------------------------------ ----------- ----------
2009-02-01 00:00:00.000 28 15
2009-02-02 00:00:00.000 27 -10
2009-02-15 00:00:00.000 14 -2而库龄的合计为：15－10－2＝32、而库龄大于或等于30天而小于89天的记录如下：
basedate adddate baseAmount
------------------------------------------------------ ----------- ----------
2009-01-01 00:00:00.000 59 30
2009-01-15 00:00:00.000 45 -2
2009-01-18 00:00:00.000 42 -5而库龄的合计为：30－2－5＝233、最后一个大于或等于90天的记录在测试数据中没有，所以这个结果和LZ给出的结果一致。所以，想向楼主求证一下，到底楼主是不是要的这样的结果？
先按段搜出3个段的入库量：15，30，0
再sum（所有出库）=19
做先进先出减法：用sum减入库，从最早的开始，够减就清零，sum扣除。依次直到不够减：库存减去sum，sum＝0，新库存表就是所得
select
case
--如果大于30天的库存+总消耗小于0,证明总消耗超过30天以后的库存,要使用30天内的库存来进行运算
when (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=30 and C >0)<0
then
(select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where c>0)
else
--'1'
(select Isnull(sum(c),0) from liupengtttt where datediff(day,b,'2009-03-1') <30 and c>0)
end as c1,
case
--如果大于90天的库存+总消耗小于0,证明总消耗超过90天以后的库存,要使用30-89天的来进行运算
when (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=90 and C >0)<0
then
case
when (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=30 and C >0) >=0
then (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=30 and C >0)
else
'0'
end
else
(select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=30 and datediff(day,b,'2009-03-1')<90 and C >0 )
end as c2,
case
--如果大于90天的库存+总消耗大于等于0 证明 90天以后的库存超过总消耗,显示90天后库存与消耗的值,否则显示0
when (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=90 and C >0)>=0
then (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=90 and C >0)
else
'0'
end as c3引一朋友写的，已验证结果正确（加多种情况验证）