select ym=convert(varchar(7),a,120), total=sum(b) from 表A group by convert(varchar(7),a,120) order by convert(varchar(7),a,120)
select 月份=(rtrim(year(A))+'-'+ltrim(month(A))), 和=sum(B) from 表 group by (rtrim(year(A))+'-'+ltrim(month(A)))
--没看清楚结果。这样一来做。 --定义一个中间表--测试环境declare @t table( a datetime,value int) insert into @t select '2006-1-2',10 union all select '2005-12-1',20 union all select '2006-1-15',30 union all select '2006-3-1',40 union all select '2005-12-12',50 union all select '2006-1-10',60 union all select '2006-6-6',70 --测试语句 declare @StartTime datetime,@Endtime datetime select @StartTime=min(a) from @t select @Endtime=max(a) from @t declare @time table(dDate datetime) --计算连续时间 while @StartTime<=@Endtime begin insert into @time select @StartTime set @StartTime=Dateadd(month,1,@StartTime) end--查询 select 月份=left(convert(varchar(10),A.dDate,120),7), 和=COALESCE(sum(B.value),0) from @time A left join @t B on left(convert(varchar(10),A.dDate,120),7)=left(convert(varchar(10),B.a,120),7) group by left(convert(varchar(10),A.dDate,120),7)--结果 月份 和 ---------- ----------- 2005-12 70 2006-01 100 2006-02 0 2006-03 40 2006-04 0 2006-05 0 2006-06 70(所影响的行数为 7 行)
create table t (A datetime,B int)insert t select '2006-1-2',10 union all select '2005-12-1',20 union all select '2006-1-15',30 union all select '2006-3-1',40 union all select '2005-12-12',50 union all select '2006-1-10',60 union all select '2006-6-6',70 select n.n_y as A,isnull(m.sum_b,0) as B from ( select cast(y as varchar)+'-'+b.m as n_y from( select year(a) y from t group by year(a)) a cross join ( select m='01' union all select m='02' union all select m='03' union all select m='04' union all select m='05' union all select m='06' union all select m='07' union all select m='08' union all select m='09' union all select m='10' union all select m='11' union all select m='12') b) n left join ( select * from( select convert(char(7),a,120) as m_y, sum(b) as sum_b from t group by convert(char(7),a,120)) m ) m on n.n_y=m.m_y where n.n_y between (select min(convert(char(7),a,120)) from t) and (select max(convert(char(7),a,120)) from t)drop table tA B --------------------------------- ----------- 2005-12 70 2006-01 100 2006-02 0 2006-03 40 2006-04 0 2006-05 0 2006-06 70(所影响的行数为 7 行)
total=sum(b)
from 表A
group by convert(varchar(7),a,120)
order by convert(varchar(7),a,120)
和=sum(B)
from 表
group by (rtrim(year(A))+'-'+ltrim(month(A)))
--定义一个中间表--测试环境declare @t table( a datetime,value int)
insert into @t select '2006-1-2',10
union all select '2005-12-1',20
union all select '2006-1-15',30
union all select '2006-3-1',40
union all select '2005-12-12',50
union all select '2006-1-10',60
union all select '2006-6-6',70
--测试语句
declare @StartTime datetime,@Endtime datetime
select @StartTime=min(a) from @t
select @Endtime=max(a) from @t
declare @time table(dDate datetime) --计算连续时间
while @StartTime<=@Endtime
begin
insert into @time select @StartTime
set @StartTime=Dateadd(month,1,@StartTime)
end--查询
select 月份=left(convert(varchar(10),A.dDate,120),7),
和=COALESCE(sum(B.value),0)
from @time A left join @t B
on left(convert(varchar(10),A.dDate,120),7)=left(convert(varchar(10),B.a,120),7)
group by left(convert(varchar(10),A.dDate,120),7)--结果
月份 和
---------- -----------
2005-12 70
2006-01 100
2006-02 0
2006-03 40
2006-04 0
2006-05 0
2006-06 70(所影响的行数为 7 行)
(A datetime,B int)insert t
select '2006-1-2',10 union all
select '2005-12-1',20 union all
select '2006-1-15',30 union all
select '2006-3-1',40 union all
select '2005-12-12',50 union all
select '2006-1-10',60 union all
select '2006-6-6',70
select n.n_y as A,isnull(m.sum_b,0) as B from (
select cast(y as varchar)+'-'+b.m as n_y from(
select year(a) y from t group by year(a)) a
cross join
(
select m='01' union all
select m='02' union all
select m='03' union all
select m='04' union all
select m='05' union all
select m='06' union all
select m='07' union all
select m='08' union all
select m='09' union all
select m='10' union all
select m='11' union all
select m='12') b) n
left join
(
select * from(
select convert(char(7),a,120) as m_y, sum(b) as sum_b from t group by convert(char(7),a,120)) m
) m on n.n_y=m.m_y
where n.n_y between (select min(convert(char(7),a,120)) from t) and (select max(convert(char(7),a,120)) from t)drop table tA B
--------------------------------- -----------
2005-12 70
2006-01 100
2006-02 0
2006-03 40
2006-04 0
2006-05 0
2006-06 70(所影响的行数为 7 行)