declare @step int,@i int,@l int=1, @namevarchar(10), @st datetime='2001-01-01' declare @t table(id int,st datetime,ed datetime) set @i= 12/@step while @l<@i begin insert into @t(id,st,ed)values(@l,dateadd(m,(@l -1)*@step,@st),dateadd(m,@l*@step,@st) set @l=@l+1 end select name,id,sum(value) from table ,@t where date>=st and date<ed group by name,id
Declare @BeginDate datetime Decalre @EndDate datetimeselect Name,sum(value) from Table where Date between @BeginDate and @EndDate group by Name
create pro pro_test declare @step int,--(几个月统计一次) @name varchar(10),(人员) @st datetime='2001-01-01'(开始日期) as declare @i int,@l int=1 declare @t table(id int,st datetime,ed datetime) set @i= 12/@step while @l<@i begin insert into @t(id,st,ed)values(@l,dateadd(m,(@l -1)*@step,@st),dateadd(m,@l*@step,@st) set @l=@l+1 end select name,id,sum(value) from table ,@t where date>=st and date<ed group by name,id
多谢caiyunxia(monkey),我试试!!
create table # (name varchar(10),date datetime ,value decimal(10,2)) insert into # (name,date,value) values('aa','2002-01-01',10) insert into # (name,date,value) values('aa','2002-02-01',10) insert into # (name,date,value) values('aa','2002-03-01',10) insert into # (name,date,value) values('aa','2002-02-02',10) insert into # (name,date,value) values('aa','2002-03-01',10) insert into # (name,date,value) values('aa','2002-04-01',10) insert into # (name,date,value) values('aa','2002-05-01',10) insert into # (name,date,value) values('aa','2002-06-01',10) insert into # (name,date,value) values('aa','2002-07-01',10) insert into # (name,date,value) values('aa','2002-08-01',10) insert into # (name,date,value) values('aa','2002-09-01',10) insert into # (name,date,value) values('aa','2002-10-01',10) insert into # (name,date,value) values('aa','2002-11-01',10) insert into # (name,date,value) values('aa','2002-12-01',10) declare @step int,--(几个月统计一次) @name varchar(10), @st datetime set @st = '2002-01-01' set @step=3 set @name='aa' declare @i int,@l int set @l=1 declare @t table(id int,st datetime,ed datetime) set @i= 12/@step while @l<=@i begin insert into @t(id,st,ed)values(@l,dateadd(m,(@l -1)*@step,@st),dateadd(m,@l*@step,@st)) set @l=@l+1 end
select name,id,sum(value) from # ,@t where date>=st and date<ed group by name,id drop table #
name id ---------- ----------- ---------------------------------------- aa 1 50.00 aa 2 30.00 aa 3 30.00 aa 4 30.00
set @step =4 name id ---------- ----------- ---------------------------------------- aa 1 60.00 aa 2 40.00 aa 3 40.00 6 name id ---------- ----------- ---------------------------------------- aa 1 80.00 aa 2 60.00 2 name id ---------- ----------- ---------------------------------------- aa 1 30.00 aa 2 30.00 aa 3 20.00 aa 4 20.00 aa 5 20.00 aa 6 20.00
declare @begin datetime,--开始日期 @end datetime,--结束日期 @c datetime,@step int--间隔select @c=@begin,@step=2 while (convert(char(6),@c,112)<convert(char(6),@end,112)) beginselect 部门,sum(数量) from table where month(日期字段) between month(@c) and month(dateadd(mm,@step,@c)) group by 部门set @c=dateadd(mm,@step,@c)end
trying: select sum(???) from TABLE group by month(DATE)/2
--对于表 {name(人),date(时间),value(消费金额)},设表名为:Tab --试试: Declare @SD smalldatetime, --起始日期 @ED smalldatetime, --终止日期 @MC tinyint --月份数<假定你的时间段是以月份为单位的。> Select Name,Phase=(Month([Date])-Month(@SD))/@MC,Sum(Value) From Tab Where [Date] between @SD And @ED Group By Name,Phase --这里请注意如果你的时间段是破月的情形,需要在统计前对日期进行偏移运算。 Declare @DD tinyint --(2.6---3.5 视为二月,偏移天数为5) Select @DD = 5 Select Name,Phase=(Month([Date]-@DD)-Month(@SD-@DD))/@MC,Sum(Value) From Tab Where [Date] between @SD And @ED Group By Name,Phase --(2.26---3.25视为三月,偏移天数为25,月份再加1。) Select @DD = 25 Select Name,Phase=(Month([Date]-@DD)-Month(@SD-@DD) +1)/@MC,Sum(Value) From Tab Where [Date] between @SD And @ED Group By Name,Phase --////////////////////////////////////////////// --如果仅以某一固定天数来分段,则语句更简单。 Declare @SD smalldatetime, --起始日期 @ED smalldatetime, --终止日期 @DC tinyint --分段天数 Select Name,Phase=([Date]-@SD)/@DC, Sum(Value) From Tab Where [Date] between @SD And @ED Group By Name,Phase
@namevarchar(10),
@st datetime='2001-01-01'
declare @t table(id int,st datetime,ed datetime)
set @i= 12/@step
while @l<@i
begin
insert into @t(id,st,ed)values(@l,dateadd(m,(@l -1)*@step,@st),dateadd(m,@l*@step,@st)
set @l=@l+1
end
select name,id,sum(value)
from table ,@t
where date>=st and date<ed
group by name,id
Decalre @EndDate datetimeselect Name,sum(value)
from Table
where Date between @BeginDate and @EndDate
group by Name
declare @step int,--(几个月统计一次)
@name varchar(10),(人员)
@st datetime='2001-01-01'(开始日期)
as
declare @i int,@l int=1
declare @t table(id int,st datetime,ed datetime)
set @i= 12/@step
while @l<@i
begin
insert into @t(id,st,ed)values(@l,dateadd(m,(@l -1)*@step,@st),dateadd(m,@l*@step,@st)
set @l=@l+1
end
select name,id,sum(value)
from table ,@t
where date>=st and date<ed
group by name,id
insert into # (name,date,value) values('aa','2002-01-01',10)
insert into # (name,date,value) values('aa','2002-02-01',10)
insert into # (name,date,value) values('aa','2002-03-01',10)
insert into # (name,date,value) values('aa','2002-02-02',10)
insert into # (name,date,value) values('aa','2002-03-01',10)
insert into # (name,date,value) values('aa','2002-04-01',10)
insert into # (name,date,value) values('aa','2002-05-01',10)
insert into # (name,date,value) values('aa','2002-06-01',10)
insert into # (name,date,value) values('aa','2002-07-01',10)
insert into # (name,date,value) values('aa','2002-08-01',10)
insert into # (name,date,value) values('aa','2002-09-01',10)
insert into # (name,date,value) values('aa','2002-10-01',10)
insert into # (name,date,value) values('aa','2002-11-01',10)
insert into # (name,date,value) values('aa','2002-12-01',10)
declare @step int,--(几个月统计一次)
@name varchar(10),
@st datetime
set @st = '2002-01-01'
set @step=3
set @name='aa'
declare @i int,@l int
set @l=1
declare @t table(id int,st datetime,ed datetime)
set @i= 12/@step
while @l<=@i
begin
insert into @t(id,st,ed)values(@l,dateadd(m,(@l -1)*@step,@st),dateadd(m,@l*@step,@st))
set @l=@l+1
end
select name,id,sum(value)
from # ,@t
where date>=st and date<ed
group by name,id
drop table #
name id
---------- ----------- ----------------------------------------
aa 1 50.00
aa 2 30.00
aa 3 30.00
aa 4 30.00
name id
---------- ----------- ----------------------------------------
aa 1 60.00
aa 2 40.00
aa 3 40.00
6
name id
---------- ----------- ----------------------------------------
aa 1 80.00
aa 2 60.00
2
name id
---------- ----------- ----------------------------------------
aa 1 30.00
aa 2 30.00
aa 3 20.00
aa 4 20.00
aa 5 20.00
aa 6 20.00
declare @begin datetime,--开始日期
@end datetime,--结束日期
@c datetime,@step int--间隔select @c=@begin,@step=2
while (convert(char(6),@c,112)<convert(char(6),@end,112))
beginselect 部门,sum(数量)
from table
where month(日期字段) between month(@c) and month(dateadd(mm,@step,@c))
group by 部门set @c=dateadd(mm,@step,@c)end
select sum(???)
from TABLE
group by month(DATE)/2
--试试:
Declare @SD smalldatetime, --起始日期
@ED smalldatetime, --终止日期
@MC tinyint --月份数<假定你的时间段是以月份为单位的。>
Select Name,Phase=(Month([Date])-Month(@SD))/@MC,Sum(Value)
From Tab
Where [Date] between @SD And @ED
Group By Name,Phase
--这里请注意如果你的时间段是破月的情形,需要在统计前对日期进行偏移运算。
Declare @DD tinyint
--(2.6---3.5 视为二月,偏移天数为5)
Select @DD = 5
Select Name,Phase=(Month([Date]-@DD)-Month(@SD-@DD))/@MC,Sum(Value)
From Tab
Where [Date] between @SD And @ED
Group By Name,Phase
--(2.26---3.25视为三月,偏移天数为25,月份再加1。)
Select @DD = 25
Select Name,Phase=(Month([Date]-@DD)-Month(@SD-@DD) +1)/@MC,Sum(Value)
From Tab
Where [Date] between @SD And @ED
Group By Name,Phase
--//////////////////////////////////////////////
--如果仅以某一固定天数来分段,则语句更简单。
Declare @SD smalldatetime, --起始日期
@ED smalldatetime, --终止日期
@DC tinyint --分段天数
Select Name,Phase=([Date]-@SD)/@DC, Sum(Value)
From Tab
Where [Date] between @SD And @ED
Group By Name,Phase
caiyunxia(monkey)兄的按是每@step个月分组统计
所以我的@step等于caiyunxia(monkey)兄的@step-1 测试正确