select a.provideware,isnull(b.rate,0) as rate,t.months
from (
select 1 as months
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
) as t cross join (
select distinct provideware from 一个表
) as a left join 一个表 b
on t.months=b.months and a.provideware=b.provideware
from (
select 1 as months
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
) as t cross join (
select distinct provideware from 一个表
) as a left join 一个表 b
on t.months=b.months and a.provideware=b.provideware
from table1declare @i int
set @i=1
while @i<13
begin
insert into #temp
select '001',0,@i set @i=@i+1
end set @i=8
while @i<5
begin
insert into #temp
select '001',0,@i set @i=@i+1
end
select a.provideware,isnull(b.rate,0) as rate,t.months
into #t
from (
select 1 as months
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
) as t cross join (
select distinct provideware from 一个表
) as a left join 一个表 b
on t.months=b.months and a.provideware=b.provideware
from 表 a
,
(
select 1 as months
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
) T
where not exists (select 1 from 表 where provideware = a.provideware and months = T.months )
group by a.provideware,T.months
select * into #table from tabledeclare @cnt int
set @cnt=1
while @cnt<13
begin
if @cnt in (select m from table)
set @cnt=@cnt+1
else
insert into #bb select '001',0.0,@cnt
set @cnt=@cnt+1
end