两个表的数据关联查询得到另一个表解决办法

两个表的数据关联查询得到另一个表
表a  
date mno time
120601 1 20
120602 1 30
表b
mno desp
1 lwl
2 sp
问题如下：先要求a和b联合查询得到新表C，c表字段如下：
date mno desp time
120601 1 lwl 20
120601 2 sp 0
120602 1 lwl 30
120602 2 sp 0 
请高手赐教！

[解决办法]

SQL code

if object_id('[a]') is not null drop table [a]gocreate table [a]([date] int,[mno] int,[time] int)insert [a]select 120601,1,20 union allselect 120602,1,30goif object_id('[b]') is not null drop table [b]gocreate table [b]([mno] int,[desp] varchar(3))insert [b]select 1,'lwl' union allselect 2,'sp'goselect a.date,b.mno,b.desp,isnull(time,0) as timefrom (select distinct date from a) ajoin b on 1=1left join a as c on c.mno=b.mno and a.date=c.date/**date        mno         desp time----------- ----------- ---- -----------120601      1           lwl  20120601      2           sp   0120602      1           lwl  30120602      2           sp   0(4 行受影响)**/