规格表:
spec_code
01
02产品
company spec_code vol
a 01 1
b 02 2想要的结果company spec_code vol
a 01 1
a 02 null
b 01 null
b 02 2
请高手帮忙,谢谢
spec_code
01
02产品
company spec_code vol
a 01 1
b 02 2想要的结果company spec_code vol
a 01 1
a 02 null
b 01 null
b 02 2
请高手帮忙,谢谢
from 规格表 a cross join 产品 b
declare @规格表 table (spec_code varchar(2))
insert into @规格表
select '01' union all
select '02'
--> 测试数据: @产品
declare @产品 table (company varchar(1),spec_code varchar(2),vol int)
insert into @产品
select 'a','01',1 union all
select 'b','02',2
select b.company,a.spec_code,vol=case a.spec_code when b.spec_code then b.vol else null end from @规格表 a, @产品 b
insert into a values('01')
insert into a values('02')
create table b(company varchar(10) , spec_code varchar(10) , vol int)
insert into b values('a' , '01' , 1 )
insert into b values('b' , '02' , 2 )
goselect t1.* , t2.vol from
(
select m.* , n.* from
(select distinct company from b) m,
(select distinct spec_code from b) n
) t1 left join b t2 on t1.company = t2.company and t1.spec_code = t2.spec_code and t1.spec_code in (select spec_code from a)drop table a , b/*
company spec_code vol
---------- ---------- -----------
a 01 1
a 02 NULL
b 01 NULL
b 02 2(所影响的行数为 4 行)
*/
insert into #1
select '01' union all
select '02'create table #(company varchar(10), spec_code varchar(10), vol int)
insert into #
select 'a', '01', 1 union all
select 'b', '02', 2 select a.company,b.spec_code,case when a.vol <>b.vol then null else a.vol end as vol
from # a,# b
where a.spec_code in(select spec_code from #1)
order by a.company
/*
company spec_code vol
---------- ---------- -----------
a 01 1
a 02 NULL
b 01 NULL
b 02 2(4 行受影响)
*/