请教一个sql语句

[2000lhzh]

请教一个sql语句
有两个表结构如下：

ta

year month netid value price
2005 01 aa 1400 21.2
2005 01 ab 130 21.2
2005 01 ac 103 21.2
2005 01 ad 100 21.2
2005 01 ae 200 21.2
2005 01 af 300 21.2

2005 02 aa 1400 21.2
2005 02 ae 200 21.2
2005 02 af 300 21.2
2005 02 bb 130 21.2
2005 02 bc 103 21.2
2005 02 bd 100 21.2
......................

tb
zone netid
HH aa
HH ab
HH ad
HB ac
HB ae
HJ af
HJ ba
HJ bb
HQ bc
HQ bd
HK bf

要得到
year month netid value price
2005 01 aa 1400 21.2
2005 01 ab 130 21.2
2005 01 ac 103 21.2
2005 01 ad 100 21.2
2005 01 ae 200 21.2
2005 01 af 300 21.2

2005 01 ba 0 0
2005 01 bb 0 0
2005 01 bc 0 0
2005 01 bd 0 0
2005 01 be 0 0
2005 01 bf 0 0

2005 02 aa 1400 21.2
2005 02 ab 0 0
2005 02 ac 0 0
2005 02 ad 0 0
2005 02 ae 200 21.2
2005 02 af 300 21.2

2005 02 ba 0 0
2005 02 bb 130 21.2
2005 02 bc 103 21.2
2005 02 bd 100 21.2
2005 02 be 0 0
2005 02 bf 0 0
..................

要求：按月份得到全部的netid 的所有记录。。。

[2000lhzh]

哇 厉害。谢谢

[子陌红尘]

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

[子陌红尘]

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=c.year and a.month=c.month and b.netid=c.netid

[jxdjxd1111]

declare @ta table(year int,month varchar(10),netid char(10),value int, price real)
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

[jxdjxd1111]

把子陌的回答修改了一下