select year(inssj) + '-' + month(inssj) , count(*) from datas
group by year(inssj) + '-' + month(inssj)
order by year(inssj) + '-' + month(inssj)这样会得到2007-06 10
2007-05 300
2007-04 400
2007-03 420
2007-01 510因为我2月没有数据
如果才能得到2007-06 10
2007-05 300
2007-04 400
2007-03 420
2007-02 0
2007-01 510这样一个结果呢?
group by year(inssj) + '-' + month(inssj)
order by year(inssj) + '-' + month(inssj)这样会得到2007-06 10
2007-05 300
2007-04 400
2007-03 420
2007-01 510因为我2月没有数据
如果才能得到2007-06 10
2007-05 300
2007-04 400
2007-03 420
2007-02 0
2007-01 510这样一个结果呢?
郁闷的不行
好几天了
SQL底子不够扎实
求高手帮忙了
谢谢各位
http://community.csdn.net/Expert/topic/5560/5560882.xml?temp=.189892
from(
select ym = '2007-06' union all
select ym = '2007-05' union all
select ym = '2007-04' union all
select ym = '2007-03' union all
select ym = '2007-02' union all
select ym = '2007-01'
)a left join(
select ym = year(inssj) + '-' + month(inssj) , cnt = count(*)
from datas
group by year(inssj) + '-' + month(inssj)
)b on a.ym = b.ym
order by a.ym
或者用生成一个包含所有月份的临时表
declare @tmpDate datetime
select @tmpDate = @startDate
while(@tmpDate < @endDate)
begin
set @StartYear=year(@tmpDate)
set @StartMonth =month(@TmpDate)
insert into #YMTable(TYear,TMonth) values (@StartYear,@StartMonth)
select @tmpDate = dateAdd(month, 1, @tmpDate)
end
注意:
@startDate 和 @endDate就是你要找的月份的开始月和结束月的一个日期.在用left join就可以了
select convert(varchar(7),inssj,120),count(*) from 表
group by convert(varchar(7),inssj,120)
order by convert(varchar(7),inssj,120)