create table t1(编号 int,月份 varchar(6),工资 int)
create table t2(编号 int,月份 varchar(6),姓名 nvarchar(10),性别 nvarchar(10),年龄 int,收入类别 nvarchar(10))
insert into t1 select 1,'201101',100 
insert into t1 select 2,'201101',200 
insert into t1 select 3,'201101',300 
insert into t1 select 4,'201101',400 
insert into t1 select 5,'201101',500 
insert into t1 select 6,'201101',600 
insert into t2 select 1,'201101','张三','男',22,'a'
insert into t2 select 1,'201101','张三','男',22,'b'
insert into t2 select 1,'201101','张三','男',22,'c'
insert into t2 select 2,'201101','李四','男',20,'a'
insert into t2 select 2,'201101','李四','男',20,'b'
insert into t2 select 3,'201101','王五','男',19,'a'
insert into t2 select 3,'201101','王五','男',19,'b'
insert into t2 select 3,'201101','王五','男',19,'c'
insert into t2 select 4,'201101','赵六','男',25,'a'
insert into t2 select 5,'201101','陈七','男',26,'a'
insert into t2 select 6,'201101','刘八','男',25,'a'
insert into t2 select 6,'201101','刘八','男',25,'b'
insert into t2 select 6,'201101','刘八','男',25,'c'
insert into t2 select 6,'201101','刘八','男',25,'d'
go
select a.编号,a.月份,a.姓名,a.性别,a.年龄,a.收入类别,
(case when a.收入类别='a' then b.工资 else 0 end) as 工资
from t2 a inner join t1 b on a.编号=b.编号
/*
编号          月份     姓名         性别         年龄          收入类别       工资
----------- ------ ---------- ---------- ----------- ---------- -----------
1           201101 张三         男          22          a          100
1           201101 张三         男          22          b          0
1           201101 张三         男          22          c          0
2           201101 李四         男          20          a          200
2           201101 李四         男          20          b          0
3           201101 王五         男          19          a          300
3           201101 王五         男          19          b          0
3           201101 王五         男          19          c          0
4           201101 赵六         男          25          a          400
5           201101 陈七         男          26          a          500
6           201101 刘八         男          25          a          600
6           201101 刘八         男          25          b          0
6           201101 刘八         男          25          c          0
6           201101 刘八         男          25          d          0

(14 行受影响)

*/
go
drop table t1,t2

------解决方案--------------------

SQL code
----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-09-16 13:28:53
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel