甲单位平均每天销10,乙单位平均每天的销售也是10,丙单位平均每天的销售也是10。 那我想得到结果是;2008-1-1,10
2008-1-2,20
2008-1-3,20
2008-1-4,20
2008-1-5,30
2008-1-6,30
2008-1-7,30
2008-1-8,30
2008-1-9,30
2008-1-10,30
2008-1-11,30
2008-1-12,20
2008-1-13,20
2008-1-14,20
2008-1-15,20
2008-1-16,10
2008-1-17,0
2008-1-18,0
2008-1-19,0
2008-1-20,0
2008-1-2,20
2008-1-3,20
2008-1-4,20
2008-1-5,30
2008-1-6,30
2008-1-7,30
2008-1-8,30
2008-1-9,30
2008-1-10,30
2008-1-11,30
2008-1-12,20
2008-1-13,20
2008-1-14,20
2008-1-15,20
2008-1-16,10
2008-1-17,0
2008-1-18,0
2008-1-19,0
2008-1-20,0
分别还是平均?
列出数据和要的结果
set nocount on;
declare @T table([Col1] nvarchar(1),[StartDate] Datetime,[EndDate] Datetime,[Qty] int)
Insert @T
select N'甲','2008-1-1','2008-1-15',150 union all
select N'乙','2008-1-6','2008-1-11',60 union all--2008-1-5改为2008-1-6
select N'丙','2008-1-2','2008-1-16',150
;with Date
as(
select cast('2008-1-1' as datetime) as Col
union all
select Col+1 from Date where Col<'2008-1-20')
select
a.Col,isnull(sum(avgQty),0) as Qty
from
Date a
cross join
(Select [Col1],[StartDate],[EndDate],[Qty]/datediff(d,[StartDate],[EndDate]+1) avgQty from @T) b
where
a.Col between b.[StartDate] and b.[EndDate]
group by all a.Col
order by a.Col
Col Qty
----------------------- -----------
2008-01-01 00:00:00.000 10
2008-01-02 00:00:00.000 20
2008-01-03 00:00:00.000 20
2008-01-04 00:00:00.000 20
2008-01-05 00:00:00.000 20
2008-01-06 00:00:00.000 30
2008-01-07 00:00:00.000 30
2008-01-08 00:00:00.000 30
2008-01-09 00:00:00.000 30
2008-01-10 00:00:00.000 30
2008-01-11 00:00:00.000 30
2008-01-12 00:00:00.000 20
2008-01-13 00:00:00.000 20
2008-01-14 00:00:00.000 20
2008-01-15 00:00:00.000 20
2008-01-16 00:00:00.000 10
2008-01-17 00:00:00.000 0
2008-01-18 00:00:00.000 0
2008-01-19 00:00:00.000 0
2008-01-20 00:00:00.000 0
但问题是在查询时并不知道表里有多少个单位,可能有N多个单位,这种情况下如何组织语句呢?