go
if OBJECT_ID('test1')is not null
drop table test1
go
create table test1(
Sno    char(11),
K_Id char(3),    
[Date] date,    
xiaoFen    int    ,
Other    varchar(50)
)
go
insert test1
select '200911076','101','2012-03-01',5,null union all
select '200911076','102','2012-04-01',4,null union all
select '200911076','103','2012-04-08',6,null union all
select '200911077','101','2012-02-27',5,null union all
select '200911077','102','2012-05-01',4,null union all
select '200911077','103','2012-05-06',3,null union all
select '200911078','101','2012-04-02',1,null union all
select '200911078','102','2012-04-23',6,null union all
select '200911078','103','2012-04-29',2,null
go
if OBJECT_ID('test2')is not null
drop table test2
go
create table test2(
K_Id char(3),
name nchar(10),    
Score float
)
go
insert test2
select '101','早操',40 union all
select '102','午休',20 union all
select '103','晚睡',40

go
if OBJECT_ID('test3')is not null
drop table test3
go
create table test3(
Sno    char(11),
[Source] char(4)    
)
go
insert test3
select '200911076','test' union all
select '200911077','test' union all
select '200911078','test'


select d.*,m.score from test3 d inner join(
select Sno,sum(asxiaoFen) as score 
from(select 
a.Sno,a.K_Id,b.Score-sum(a.xiaoFen) asxiaoFen
from test1 a inner join test2 b
on a.K_Id=b.K_Id
group by a.Sno,a.K_Id,b.Score)t
group by Sno)m
on d.Sno=m.Sno

/*
Sno    Source    score
200911076      test    85
200911077      test    88
200911078      test    91
*/


just an example