以下代码实现了根据不同时间周期要求返回sqlserver数据分组求平均数据，可以取得每年、每月、每天、每星期的分组平均数据，按星期分组GroupFlag字段存的时该星期周一最早的时间。
1 -- ================================================
2 -- Template generated from Template Explorer using:
3 -- Create Procedure (New Menu).SQL
4 --
5 -- Use the Specify Values for Template Parameters
6 -- command (Ctrl-Shift-M) to fill in the parameter
7 -- values below.
8 --
9 -- This block of comments will not be included in
10 -- the definition of the procedure.
11 -- ================================================
12 SET ANSI_NULLS ON
13 GO
14 SET QUOTED_IDENTIFIER ON
15 GO
16 -- =============================================
17 -- Author: <Author,,Name>
18 -- Create date: <Create Date,,>
19 -- Description: <Description,,>
20 -- =============================================
21 alter PROCEDURE DxoilData_GroupData_Get
22 -- Add the parameters for the stored procedure here
23 @GroupType int
24 AS
25 BEGIN
26 -- SET NOCOUNT ON added to prevent extra result sets from
27 -- interfering with SELECT statements.
28 SET NOCOUNT off;
29
30 -- Insert statements for procedure here
31 create table #GroupData(
32 QtyTD decimal(18,4),
33 DensityLoad decimal(18,4),
34 DensityArrival decimal(18,4),
35 WaterLoad decimal(18,4),
36 WaterArrival decimal(18,3),
37 SulfurLoad decimal(18,3),
38 SulfurArrival decimal(18,3),
39 GroupFlag varchar(50))
40
41
42 if( @GroupType=0)--按年分组
43 begin
44 insert into #GroupData
45 SELECT avg(qtytd),avg(DensityLoad),avg(DensityArrival),avg(WaterLoad),avg(WaterArrival),avg(SulfurLoad),avg(SulfurArrival),year(DateofArrival) from dxoildata group by year(DateofArrival)
46 end
47
48 if( @GroupType=1)--按月分组
49 begin
50 insert into #GroupData
51 SELECT avg(qtytd),avg(DensityLoad),avg(DensityArrival),avg(WaterLoad),avg(WaterArrival),avg(SulfurLoad),avg(SulfurArrival),cast(year(DateofArrival) as varchar(50))+'-'+cast(month(DateofArrival) as varchar(50)) from dxoildata group by year(DateofArrival),month(DateofArrival)
52 end
53
54 if( @GroupType=2)--按日分组
55 begin
56 insert into #GroupData
57 SELECT avg(qtytd),avg(DensityLoad),avg(DensityArrival),avg(WaterLoad),avg(WaterArrival),avg(SulfurLoad),avg(SulfurArrival),cast(year(DateofArrival) as varchar(50))+'-'+cast(month(DateofArrival) as varchar(50))+'-'+cast(day(DateofArrival) as varchar(50)) from dxoildata group by year(DateofArrival),month(DateofArrival),day(DateofArrival)
58 end
59
60 if( @GroupType=3)--按星期分组
61 begin
62 set datefirst 1
63 create table #Week(MondayDate datetime,DateIndex int identity(1,1))
64 --将是星期一的日期存入临时表#Week
65 insert into #Week(MondayDate)
66 select distinct DateofArrival from dxoildata where datepart(weekday, DateofArrival)=1
67 --将分组数据存入临时表#GroupData
68 declare @Index int
69 declare @MaxIndex int
70 select @MaxIndex=max(DateIndex) from #Week
71 --循环插入数据
72 set @Index=1
73 while(@Index<@MaxIndex)
74 begin
75 declare @DateMonday datetime
76 select @DateMonday=MondayDate from #Week where DateIndex=@Index
77
78 insert into #GroupData
79 select avg(qtytd),avg(DensityLoad),avg(DensityArrival),avg(WaterLoad),avg(WaterArrival),avg(SulfurLoad),avg(SulfurArrival),min(convert(varchar(50),DateofArrival,20)) from dxoildata where DateofArrival>=@DateMonday and DateofArrival<=DateAdd(dd,7,@DateMonday)
80
81 set @Index=@Index+1
82 end
83 --删除临时表#Week
84 drop table #Week
85 end
86
87 select * from #GroupData
88 drop table #GroupData
89
90 END
91 GO
效果如下：
1.以年为周期；
2.以月为周期；
3.以日为周期；
4.以星期为周期；
