select
a.year,
a.month,
b.netid,
isnull(c.value,0) as value,
isnull(c.price,0) as price
from
(select distinct year,month from ta) a
cross join
tb b
left join
ta c
on
a.year=b.year and a.month=b.month and b.netid=c.netid
a.year,
a.month,
b.netid,
isnull(c.value,0) as value,
isnull(c.price,0) as price
from
(select distinct year,month from ta) a
cross join
tb b
left join
ta c
on
a.year=b.year and a.month=b.month and b.netid=c.netid
a.year,
a.month,
b.netid,
isnull(c.value,0) as value,
isnull(c.price,0) as price
from
(select distinct year,month from ta) a
cross join
tb b
left join
ta c
on
a.year=c.year and a.month=c.month and b.netid=c.netid
insert into @ta
select 2005, '01','aa', 1400 , 21 union
select 2005, '01','ab', 130 , 21 union
select 2005, '01','ac', 103 , 21 union
select 2005, '01','ad', 100 , 21 union
select 2005, '01','ae', 200 , 21 union
select 2005, '01','af', 300 , 21 union
select 2005, '02','aa', 120, 22
declare @tb table(netid char(10))
insert into @tb
select 'aa' union
select 'ab' union
select 'ad' union
select 'ac' union
select 'ae' union
select 'af' union
select 'ba' union
select 'bb' union
select 'bc' union
select 'bd' union
select 'bf'
select distinct
a.year,
a.month,
b.netid,
isnull(c.value,0) as value,
isnull(c.price,0) as price
from
@ta a
cross join
@tb b
left join
@ta c
on
a.year=c.year and a.month=c.month and b.netid=c.netid--结果year month netid value price
2005 01 aa 1400 21.0
2005 01 ab 130 21.0
2005 01 ac 103 21.0
2005 01 ad 100 21.0
2005 01 ae 200 21.0
2005 01 af 300 21.0
2005 01 ba 0 0.0
2005 01 bb 0 0.0
2005 01 bc 0 0.0
2005 01 bd 0 0.0
2005 01 bf 0 0.0
2005 02 aa 120 22.0
2005 02 ab 0 0.0
2005 02 ac 0 0.0
2005 02 ad 0 0.0
2005 02 ae 0 0.0
2005 02 af 0 0.0
2005 02 ba 0 0.0
2005 02 bb 0 0.0
2005 02 bc 0 0.0
2005 02 bd 0 0.0
2005 02 bf 0 0.0