这是一个扣分记录的表
Sno char(11)
K_Id char(3)
Date datetime
xiaoFen int
Other varchar(50)
这是一个扣分类型的表
K_Id char(3)
name nchar(10)
Score float
这是计算操行分总分的表
Sno char(11)
Source char(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