每1天:
select stcd,convert(datetime,convert(char(8),ymdhm,112)) as ymdhm,sum(dtrn) as dtrn from st_rnfl_ys_t group by stcd,convert(datetime,convert(char(8),ymdhm,112))
select stcd,convert(datetime,convert(char(8),ymdhm,112)) as ymdhm,sum(dtrn) as dtrn from st_rnfl_ys_t group by stcd,convert(datetime,convert(char(8),ymdhm,112))
stcd ymdhm dtrn
-----------------------------------
0001 2002-6-2 60
0001 2002-6-3 30
0001 2002-6-4 64
0001 2002-6-5 77
0001 2002-6-6 NULL///此处时间如落空则统计结果补空
0002 2002-6-2 NULL///此处时间如落空则统计结果补空
0002 2002-6-3 NULL///此处时间如落空则统计结果补空
0002 2002-6-4 50
0002 2002-6-5 40
0002 2002-6-6 141
declare @dt datetime
select @dt=min(convert(datetime,convert(char(8),ymdhm,112))) from st_rnfl_ys_tselect stcd,datediff(day,@dt,ymdhm)/2 as diff2days,sum(dtrn) as dtrn from st_rnfl_ys_t group by stcd,datediff(day,@dt,ymdhm)/2每3天:
declare @dt datetime
select @dt=min(convert(datetime,convert(char(8),ymdhm,112))) from st_rnfl_ys_tselect stcd,datediff(day,@dt,ymdhm)/3 as diff3days,sum(dtrn) as dtrn from st_rnfl_ys_t group by stcd,datediff(day,@dt,ymdhm)/3
select stcd,substring(ymdhm,1,10),sum(dtrn) as dtrn from a group by stcd,substring(ymdhm,1,10)
如果该部门在某个时间上没有销售的话就为空,这个好象难实现啊!
from st_rnfl_ys_t a full outer join #temp b on a.ymdhm=b.TimeField
from
(select stcd
from st_rnfl_ys_t
group by stcd )as a cross join
(select cast(convert(char(10),ymdhm,112) as datetime) as ymdhm ,sum(dtrn) as dtrn
from st_rnfl_ys_t group by cast(convert(char(10),ymdhm,112) as datetime)
) as b
order by a.stcd,b.ymdhm