关于二表连接并计算横向值的和

create table 表A  
(id int, 岗位履职 int, 加减分 int, 
 姓名 varchar(10), 年度 varchar(6), 考核年月 varchar(8))

insert into 表A
select 1, 50, 50, '张三', 2012, '2012-07' union all
select 2, 59, 60, '李四', 2012, '2012-07' union all
select 3, 70, 70, '张三', 2012, '2012-08' union all
select 4, 99, 99, '张三', 2011, '2011-01'

create table 表B
(id  int, 姓名 varchar(10), 对口考核  int,
年度 varchar(6), 信息发表 int, 考核年月 varchar(8))

insert into 表B
select 1, '张三', 10, '2012', 0, '2012-07' union all
select 2, '李四', 10, '2012', 0, '2012-08' union all
select 3, '张三', 0, '2012', 10, '2012-09' union all
select 4, '张三', 0, '2012', 10, '2012-07'


select row_number() over(order by a.年度 desc,a.姓名 desc) 'id',
a.岗位履职,a.加减分,a.姓名,a.年度 '考核年度',
isnull(b.对口考核,0) '对口考核',
a.岗位履职+a.加减分+isnull(b.对口考核,0) '总分'
from
(select 姓名,年度,
 sum(岗位履职) '岗位履职',
 sum(加减分) '加减分'
 from 表A
 group by 姓名,年度) a
left join
(select 姓名,年度,sum(对口考核) '对口考核'
 from 表B
 group by 姓名,年度) b
on a.姓名=b.姓名 and a.年度=b.年度

/*
id                   岗位履职      加减分       姓名        考核年度  对口考核       总分
-------------------- ----------- ----------- ---------- ------ ----------- -----------
1                    120         120         张三         2012     10          250
2                    59          60          李四         2012     10          129
3                    99          99          张三         2011     0           198

(3 row(s) affected)
*/


--若家条件where name=张三
select row_number() over(order by a.年度 desc,a.姓名 desc) 'id',
a.岗位履职,a.加减分,a.姓名,a.年度 '考核年度',
isnull(b.对口考核,0) '对口考核',
a.岗位履职+a.加减分+isnull(b.对口考核,0) '总分'
from
(select 姓名,年度,
 sum(岗位履职) '岗位履职',
 sum(加减分) '加减分'
 from 表A
 group by 姓名,年度) a
left join
(select 姓名,年度,sum(对口考核) '对口考核'
 from 表B
 group by 姓名,年度) b
on a.姓名=b.姓名 and a.年度=b.年度
where a.姓名='张三'

/*
id                   岗位履职        加减分      姓名        考核年度  对口考核     总分
-------------------- ----------- ----------- ---------- ------ ----------- -----------
1                    120         120         张三         2012     10          250
2                    99          99          张三         2011     0           198

(2 row(s) affected)
*/