有这样三个表
xsb
ID name sex birthday specialty course remark
------ -------- ----- ---------- ---------------- ----------- ------
080101 Tom 1 1990-02-02 Computer 0
080102 Jam 1 1991-01-02 Computer 0
080103 Mary 0 1989-10-02 Accounting 0
080104 Lucy 0 1991-02-19 Fashion design 0
080105 Jack 1 1990-11-02 Accounting 0
080106 Tracy 0 1990-06-02 Fashion design 0
080107 Amy 0 1992-02-02 Computer 0
kcb
Cid Cname beginsemester grade
----------- ---------------- ------------- -----------
1 computer 1 40
2 accounting 2 30
3 fashion design 3 30
cjb
ID Cid grades
------ ----------- -----------
080101 1 96
080101 2 68
080101 3 78
080102 3 99
080102 2 66
080103 2 87
080104 1 100
现在我想计算出总学分插到xsb表的course里,总学分等于cjb.grades*(kcb.grade*0.01),请问那语句怎么写
------解决方案--------------------
下次把你问题描述清楚，睡醒了还没有人回复我才回复的，还有，把insert脚本也弄出来，又花了我5分钟。
- SQL code
CREATE TABLE [dbo].[xsb]( [ID] [char](6) NOT NULL primary key, [name] [char](8) NOT NULL, [sex] [bit] NOT NULL, [birthday] [date] NULL, [specialty] [char](16) NULL, [course] [int] NULL, [remark] [varchar](1) NULL) CREATE TABLE [dbo].[kcb]( [Cid] [int] NOT NULL primary key, [Cname] [char](16) NOT NULL, [beginsemester] [int] NULL, [grade] [int] NULL) CREATE TABLE [dbo].[cjb]( [ID] [char](6) NOT NULL, [Cid] [int] NOT NULL, [grades] [int] NULL) INSERT INTO [xsb](ID, name, sex, birthday ,specialty, course) SELECT '080101', 'Tom', 1 ,'1990-02-02', 'Computer', 0 UNION ALL SELECT '080102', 'Jam', 1, '1991-01-02' , 'Computer',0 UNION ALL SELECT '080103', 'Mary', 0 ,'1989-10-02', 'Accounting' ,0 UNION ALL SELECT '080104', 'Lucy', 0 ,'1991-02-19' ,'Fashion design', 0 UNION ALL SELECT '080105', 'Jack', 1 ,'1990-11-02', 'Accounting', 0 UNION ALL SELECT '080106', 'Tracy', 0 ,'1990-06-02' ,'Fashion design', 0 UNION ALL SELECT '080107', 'Amy', 0 ,'1992-02-02' , 'Computer', 0 INSERT INTO kcb SELECT 1 ,'Computer', 1, 40 UNION ALL SELECT 2, 'accounting', 2, 30 UNION ALL SELECT 3 ,'fashion design', 3 ,30 INSERT INTO [cjb] SELECT '080101', 1, 96 UNION ALL SELECT '080101', 2, 68 UNION ALL SELECT '080101', 3 ,78 UNION ALL SELECT '080102', 3 ,99 UNION ALL SELECT '080102', 2 ,66 UNION ALL SELECT '080103', 2 ,87 UNION ALL SELECT '080104', 1 ,100 UPDATE a SET a.course=b.course FROM xsb a INNER JOIN (SELECT c.id,SUM(a.grades*(b.grade*0.01) ) course FROM xsb c INNER JOIN cjb a ON c.id=a.id INNER JOIN kcb b ON a.cid=b.cid GROUP BY c.id) b ON a.id=b.id