update or insert

[dodolzc10]

有这样三个表
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分钟。

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

[dodolzc10]

[Quote=引用 10 楼 的回复:]
下次把你问题描述清楚，睡醒了还没有人回复我才回复的，还有，把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] [d……
[/Quote]
太感谢了!下次会把您提的做好了,再次感谢

[發糞塗牆]

select 不就可以咯，跟你帖子题目没关系吧？

[dodolzc10]

[Quote=引用 1 楼 的回复:]
select 不就可以咯，跟你帖子题目没关系吧？
[/Quote]
可以说没有吧!只是求一个方法插进指定的数据

[longai123]

人家求sql 语句

[發糞塗牆]

用update，吃完饭再写给你，还有下次麻烦给出建表和查数据的语句。帮你解决问题就一两分钟，给你造数据要十几分钟。

[dodolzc10]

[Quote=引用 4 楼 的回复:]
用update，吃完饭再写给你，还有下次麻烦给出建表和查数据的语句。帮你解决问题就一两分钟，给你造数据要十几分钟。
[/Quote]
OK,麻烦了!现在就发上去
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)

[發糞塗牆]

UPDATE xsb
 SET xsb.course=cjb.grades*(kcb.grade*0.01) 
 FROM xsb c INNER JOIN cjb a ON c.id=a.id 
  INNER JOIN kcb b ON a.cid=b.cid

貌似好简单

[發糞塗牆]

UPDATE c
 SET c.course=a.grades*(b.grade*0.01) 
 FROM xsb c INNER JOIN cjb a ON c.id=a.id 
  INNER JOIN kcb b ON a.cid=b.cid

用这个，你的别名和我的不一样

[dodolzc10]

嘻嘻!对于您是很简单的,我一开始也不好意思问了,哈哈!不过想不出来.还有这个计算出来的只是一科的,结果应该是三科加起来的

[dodolzc10]

[Quote=引用 7 楼 的回复:]
SQL code

UPDATE c
SET c.course=a.grades*(b.grade*0.01)
FROM xsb c INNER JOIN cjb a ON c.id=a.id
INNER JOIN kcb b ON a.cid=b.cid
用这个，你的别名和我的不一样
[/Quote]

a
---------------------------------------
82.20
49.50
26.10
68.20
35.70
56.90
64.20

(7 row(s) affected)
结果应该是这个,不过我是select 出来,不会update