34,587
社区成员
发帖
与我相关
我的任务
分享
select m.MonthName,m.nmonth,isnull(s.total,0) from Months m left join
(
select count(1) as total,month(kaohedate) as nMonth from sjkaohe group by month(kaohedate))s
on m.nmonth =s.nMonth
declare @t1 table(
[MonthName] varchar(10),
[nMonth] tinyint)
insert into @t1
select '一月', 1 union
select '二月', 2 union
select '三月', 3
declare @t2 table(
[kaohedate] smalldatetime,
[data] int)
insert into @t2
select '2011-01-02', 1 union
select '2011-02-01', 2
--select * from @t1 order by nMonth
--select * from @t2
select [MonthName], [nMonth], isnull([Total], 0) as [Total] from @t1 as A left join (
select kaohedate, count(1) as Total from @t2
group by kaohedate
) as B on A.nMonth=MONTH(B.kaohedate)
order by nMonth
select A.Number, * from master..spt_values A left join 表 b on A.number=Month(B.日期)
where A.type='p' and a.number<13
declare @st datetime --起始
declare @et datetime --结束
set @st = '2011-05-04'
set @et = '2011-11-11'
;with cte as
(
select convert(varchar(7),dateadd(mm,number,@st),120) as yymm
from master..spt_values
where [type] = 'p' and number between 0 and datediff(mm,@st,@et)
)
select * from cte
/*
yymm
-------
2011-05
2011-06
2011-07
2011-08
2011-09
2011-10
2011-11
*/
--可以用cte 去 left join 你查询的表,例如:
select a.yymm,sum(b.amount) as amount
from cte a left join tb b on a.yymm = convert(varchar(7),b.date,120)
group by a.yymm