TableAid jf addTime
1 10 2010-1-10
2 11 2010-1-11
3 8 2010-2-12
4 100 2011-6-1
5 -1 2011-6-2希望能用最方便的语句统计出
2010年1月 21
2010年2月 8
2011年6月 99
1 10 2010-1-10
2 11 2010-1-11
3 8 2010-2-12
4 100 2011-6-1
5 -1 2011-6-2希望能用最方便的语句统计出
2010年1月 21
2010年2月 8
2011年6月 99
,sum(jf) as jf
from tableA
group by replace(convert(varchar(7),addTime,120),'-','年')+'月'
from tableA group by ltrim(year(addTime))+'年'+ltrim(month(addTime))+'月'
from tb group by stuff(stuff(addTime,5,1,'年'),7,3,'月')
create table #TableA
(id int,jf int,addTime datetime)
insert #TableA
select 1 ,10 ,'2010-1-10' union all
select 2 ,11 ,'2010-1-11' union all
select 3 ,8 ,'2010-2-12' union all
select 4 ,100 ,'2011-6-1' union all
select 5 ,-1 ,'2011-6-2' select ltrim(year(addTime))+'年'+ltrim(month(addTime))+'月' as '月份' ,sum(jf) as [sum]
from #TableA group by ltrim(year(addTime))+'年'+ltrim(month(addTime))+'月'
replace(convert(varchar(7),addTime,120),'-','年')+'月' as ny,sum(jf) as jf
from
tableA
group by
replace(convert(varchar(7),addTime,120),'-','年')+'月'
use demo
go
create table #tableA
(
id int,
jf int,
addtime datetime
)
insert #tableA
select 1,10,'2010-1-10' union all
select 2,11,'2010-1-11' union all
select 3,8,'2010-2-12' union all
select 4,100,'2011-6-1' union all
select 5,-1,'2011-6-2'
print '正在插入数据......'select REPLACE(convert(varchar(7),addtime,120),'-','年')+'月' as ny,SUM(jf) as jf
from #tableA
group by REPLACE(convert(varchar(7),addtime,120),'-','年')+'月'