日期:2014-05-17 浏览次数:20578 次
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 张三 男 &nb