27,579
社区成员
发帖
与我相关
我的任务
分享
declare @test table (colum1 varchar(255),colum2 varchar(255), colum3 int );
--数据
insert into @test values('团队1','2013-1',1);
insert into @test values('团队1','2013-2',1);
insert into @test values('团队2','2013-1',1);
insert into @test values('团队2','2013-2',5);
insert into @test values('团队3','2013-3',3);
select m.colum1 ,sub.colum2,isnull(num.colum3,0) colum3 from
(select distinct colum1 from @test m) m
cross join
(select distinct colum2 from @test) sub
left join @test num on num.colum1=m.colum1 and num.colum2=sub.colum2
drop table test
--已知:表有两个字段
create table test(colum1 varchar(255),colum2 varchar(255), colum3 int );
--数据
insert into test values('团队1','2013-1',1);
insert into test values('团队1','2013-2',1);
insert into test values('团队2','2013-1',1);
insert into test values('团队2','2013-2',5);
insert into test values('团队3','2013-3',3);
DECLARE @count INT
SET @count = 3 --补全至几行
SELECT a.colum1, b.number, colum3=ISNULL(c.colum3,0)
FROM (SELECT DISTINCT colum1 FROM test) a
CROSS JOIN
(SELECT number='2013-'+LTRIM(number+1) FROM master..spt_values
WHERE type = 'p' AND number < @count) b
LEFT JOIN test c
ON a.colum1=c.colum1
AND b.number = c.colum2
/*
colum1 number colum3
团队1 2013-1 1
团队1 2013-2 1
团队1 2013-3 0
团队2 2013-1 1
团队2 2013-2 5
团队2 2013-3 0
团队3 2013-1 0
团队3 2013-2 0
团队3 2013-3 3
*/