各位帮我解决这个问题?
这是一个扣分记录的表

Snochar(11)
K_Idchar(3)
Datedatetime
xiaoFenint
Othervarchar(50)
这是一个扣分类型的表
K_Idchar(3)
namenchar(10)
Scorefloat

这是计算操行分总分的表
Snochar(11)
Sourcechar(4)
现在求每个同学操行分总分Source


[解决办法]

SQL code

goif OBJECT_ID('test1')is not nulldrop table test1gocreate table test1(Sno    char(11),K_Id char(3),    [Date] date,    xiaoFen    int    ,Other    varchar(50))goinsert test1select '200911076','101','2012-03-01',5,null union allselect '200911076','102','2012-04-01',4,null union allselect '200911076','103','2012-04-08',6,null union allselect '200911077','101','2012-02-27',5,null union allselect '200911077','102','2012-05-01',4,null union allselect '200911077','103','2012-05-06',3,null union allselect '200911078','101','2012-04-02',1,null union allselect '200911078','102','2012-04-23',6,null union allselect '200911078','103','2012-04-29',2,nullgoif OBJECT_ID('test2')is not nulldrop table test2gocreate table test2(K_Id char(3),name nchar(10),    Score float)goinsert test2select '101','早操',40 union allselect '102','午休',20 union allselect '103','晚睡',40goif OBJECT_ID('test3')is not nulldrop table test3gocreate table test3(Sno    char(11),[Source] char(4)    )goinsert test3select '200911076','test' union allselect '200911077','test' union allselect '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) asxiaoFenfrom test1 a inner join test2 bon a.K_Id=b.K_Idgroup by a.Sno,a.K_Id,b.Score)tgroup by Sno)mon d.Sno=m.Sno/*Sno    Source    score200911076      test    85200911077      test    88200911078      test    91*/just an example