select substring(cast(riqi as varchar(10)),1,6) as nianyue ,sum([sum]) as [sum] from tb group by substring(cast(riqi as varchar(10)),1,6)
nianyue当然应该是字符型了! try: select nianyue=convert(varchar(7),riqi,120),[sum]=sum([sum]) from yourtable group by convert(varchar(7),riqi,120)
select convert(char(7),riqi,120) as nianyue,sum(sum) from 表 group by year(riqi),month(riqi)
declare @ table(riqi datetime,[sum] int) insert into @ select '2003-01-01', 123 union all select '2003-01-01', 234 union all select '2003-01-03' , 120 union all select '2003-02-01' , 100 select substring(convert(varchar(10),riqi, 120),1,7) as nianyue ,sum([sum]) as [sum] from @ group by substring(convert(varchar(10),riqi, 120),1,7)--结果 nianyue sum ------- ----------- 2003-01 477 2003-02 100(2 row(s) affected)
from tb
group by substring(cast(riqi as varchar(10)),1,6)
try:
select nianyue=convert(varchar(7),riqi,120),[sum]=sum([sum])
from yourtable group by convert(varchar(7),riqi,120)
group by year(riqi),month(riqi)
insert into @ select '2003-01-01', 123 union all select
'2003-01-01', 234 union all select
'2003-01-03' , 120 union all select
'2003-02-01' , 100 select substring(convert(varchar(10),riqi, 120),1,7) as nianyue ,sum([sum]) as [sum]
from @
group by substring(convert(varchar(10),riqi, 120),1,7)--结果
nianyue sum
------- -----------
2003-01 477
2003-02 100(2 row(s) affected)