估计适用SQL Server 2000,全凭印象,无从测试,所以不确定可行否:select
x.Item,
x.TransDate,
sum(y.Qty) as Qty,
'每日结算' as Type
from
(select
m.Item,dateadd(d,m.TransDate,n.id-1) as TransDate
from
A m,sysobjects n
where
datediff(m,m.TransDate,dateadd(d,m.TransDate,n.id-1))=0) x,
a y
where
x.Item=y.Item
and
x.TransDate>=y.TransDate
and
datediff(m.x.TransDate,y.TransDate)=0
group by
x.Item,x.TransDate
x.Item,
x.TransDate,
sum(y.Qty) as Qty,
'每日结算' as Type
from
(select
m.Item,dateadd(d,m.TransDate,n.id-1) as TransDate
from
A m,sysobjects n
where
datediff(m,m.TransDate,dateadd(d,m.TransDate,n.id-1))=0) x,
a y
where
x.Item=y.Item
and
x.TransDate>=y.TransDate
and
datediff(m.x.TransDate,y.TransDate)=0
group by
x.Item,x.TransDate
x.Item,
x.TransDate,
sum(y.Qty) as Qty,
'每日结算' as Type
from
(select
m.Item,dateadd(d,m.TransDate,n.id-1) as TransDate
from
A m,sysobjects n
where
datediff(m,m.TransDate,dateadd(d,m.TransDate,n.id-1))=0) x,
a y
where
x.Item=y.Item
and
x.TransDate>=y.TransDate
and
datediff(m, x.TransDate, y.TransDate)=0
group by
x.Item,x.TransDate
要全部统计亦可,如果有几十万种物料,这查询够呛的更多的是设计问题,而非某SQL问题
只能从SQL优化上着手了
执行了一下,提示date overflow 了
需要自动添加, 6月,每个item都要有30条,7月 31条
等等
SELECT T1.Item,SUM(T2.Qty)Qty,T1.TransDate TransDateStart,'每日结算'[Type]
,COUNT(T2.Item)RN
FROM A T1
JOIN A T2 ON T1.Item=T2.Item AND T1.TransDate>=T2.TransDate
GROUP BY T1.Item,T1.Qty,T1.TransDate
)
SELECT T3.Item,T3.Qty,DATEADD(DAY,T4.number,TransDateStart)TransDate,T3.[Type] FROM(
SELECT T1.*
,ISNULL(T2.TransDateStart,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(T1.TransDateStart),T1.TransDateStart)))TransDateEnd
FROM CTE T1
LEFT JOIN CTE T2 ON T1.Item=T2.Item AND T1.RN+1=T2.RN
)T3
JOIN master..spt_values T4 ON T4.type='P'AND T4.number<DATEDIFF(DAY,TransDateStart,TransDateEnd)
ORDER BY T3.Item,TransDateSQL2005+
C001 但是没有1号数据的话,
比如
c001 2014/06/12 100 每天
c001 2014/06/22 200 每天c001的数据就从12号开始,没有前11天的数据能改进一下更感激不尽了
IF OBJECT_ID('tempdb..#t')>0 DROP TABLE #t
GO
create table #A
(
Item nvarchar(20),
Qty numeric(16,2),
TransDate datetime,
[Type] nvarchar(10)
)goinsert into #A values
('A001', '100.00', '2014/06/01', N'月初'),
('A001', '50.00', '2014/06/10', N'每天') ,
('A001', '-20.00', '2014/06/20', N'每天') ,
('B001', '200.00', '2014/06/01', N'月初') ,
('B001', '60.00', '2014/06/15', N'每天')with cte as
(
select *,ROW_NUMBER()OVER(PARTITION BY Item order by TransDate)id
from #A
)
select *,
case
when datediff(day,TransDate,(select TransDate from cte where Item = a.Item and id = a.id+1)) is not null
then datediff(day,TransDate,(select TransDate from cte where Item = a.Item and id = a.id+1))
else datediff(day,TransDate,dateadd(day,-1,cast(DATEPART(YEAR,TransDate) as varchar(10))+right('-0'+cast(DATEPART(MM,TransDate)+1 as varchar(10)),3)+'-01' )) end nb,
(select SUM(Qty) from cte where Item = a.Item and id <= a.id ) nqty
into #t
from cte a;with cte as
(
select ROW_NUMBER()over(PARTITION by Item order by DATEADD(day,b.number,TransDate)) id,a.Item,a.nqty,newdate=DATEADD(day,b.number,TransDate) from #t a
join master..spt_values b on b.number< a.nb and b.type = 'P'
)
select * from cte
union all
select id,Item,nqty,dateadd(day,1,newdate) from cte a
where id = (select MAX(id) from cte where Item = a.Item)
order by Item,newdate
('A001', '100.00', '2014/06/01', N'月初'),
('A001', '50.00', '2014/06/10', N'每天') ,
('A001', '-20.00', '2014/06/20', N'每天') ,
('B001', '200.00', '2014/06/01', N'月初') ,
('B001', '60.00', '2014/06/15', N'每天') 到这里没问题,select出来的数据都对
如果再加上
('C001', '60.00', '2014/06/12', N'每天') 没有月初的数据结果
C001的数据就是从12号开始了,1-11号的数据没有,想select出来
C001 6/1 0 每日结算
...
C001 6/11 0 每日结算
C001 6/12 60 每日结算
...
C001 6/30 60 每日结算
Item TransDate Qty TYPE
A001 2014-06-01 100.0000000000000000 月初
A001 2014-06-10 50.0000000000000000 每天
A001 2014-06-20 -30.0000000000000000 每天
B001 2014-06-01 200.0000000000000000 月初
C001 2014-06-12 50.0000000000000000 每天
C001 2014-06-22 30.0000000000000000 每天
select出来的结果
A001 B001的都对,就省略
1 C001 50.0000000000000000 2014-06-12
2 C001 50.0000000000000000 2014-06-13
3 C001 50.0000000000000000 2014-06-14
4 C001 50.0000000000000000 2014-06-15
5 C001 50.0000000000000000 2014-06-16
6 C001 50.0000000000000000 2014-06-17
7 C001 50.0000000000000000 2014-06-18
8 C001 50.0000000000000000 2014-06-19
9 C001 50.0000000000000000 2014-06-20
10 C001 50.0000000000000000 2014-06-21
11 C001 80.0000000000000000 2014-06-22
12 C001 80.0000000000000000 2014-06-23
13 C001 80.0000000000000000 2014-06-24
14 C001 80.0000000000000000 2014-06-25
15 C001 80.0000000000000000 2014-06-26
16 C001 80.0000000000000000 2014-06-27
17 C001 80.0000000000000000 2014-06-28
18 C001 80.0000000000000000 2014-06-29
18 C001 80.0000000000000000 2014-06-30