declare @year int
set @year = 2004
select top 12 id=identity(int,1,1),dt=cast('' as char(7)) into #t from sysobjects
update #t
set dt=rtrim(@year)+'-'+right('0'+rtrim(id),2)
select t.月份,isnull(t.数量,0) 数量 from #t -----在这改一下
left join (select convert(char(7),签定日期,120) as '月份',
count(*) as '数量'
from 表
where year(签定日期)=@year
group by convert(char(7),签定日期,120))t on t.月份 = #t.dt
select top 12 id=identity(int,1,1) into #t from sysobjects
select '2004-'+right('0'+cast(t.id as varchar),2) as 月份,
isnull(x.数量,0) as 数量
from #t t left join (
select convert(varchar(7),签定日期,120) as 月份,
count(*) as 数量
from 合同
where year(签定日期)=2004 --2004使输入的年份
group by convert(varchar(7),签定日期,120)
) as x
on '2004-'+right('0'+cast(t.id as varchar),2)=x.月份
select convert(char(7),签定日期,120) as '月份',
isnull(count(*),0) as '数量'
from 表
where year(签定日期)='2007'
group by convert(char(7),签定日期,120)
order by convert(char(7),签定日期,120)
declare @year int
set @year = 2004
select top 12 id=identity(int,1,1),dt=cast('' as char(7)) into #t from sysobjects
update #t
set dt=rtrim(@year)+'-'+right('0'+rtrim(id),2)
select t.月份,t.数量 from #t
left join (select convert(char(7),签定日期,120) as '月份',
count(*) as '数量'
from 表
where year(签定日期)=@year
group by convert(char(7),签定日期,120))t on t.月份 = #t.dt
select convert(char(7),签定日期,120) as '月份',
count(*) as '数量'
from 表
where year(签定日期)='2007'
group by convert(char(7),签定日期,120)
order by convert(char(7),签定日期,120)