教学计划表 课程编号 学生编号 得分
tb_teachplanstudent fd_courseid_refid fd_usersid_refid fd_examgrade试卷表 课程编号 试卷编号
tb_evaluate_exam fd_courseid_refid fd_examId得分表 得分 试卷编号 学生编号
tb_userstatic_evaluateDetail fd_evaluategrade fd_firstSortCode_refcode fd_usersid_refid
我想在教学计划表新建字段fd_examgrade,该怎么把tb_userstatic_evaluateDetail表的fd_evaluategrade得分更新到教学计划表去
解决方案 »
- 在分组中,想把两条记录合成一条记录要怎样写?
- 如何让insert into 数据表 bh 的内容保持唯一
- 如何恢复备份数据
- 请问BULK INSERT语句是否可以将*.DBF的表导入到SQL SERVER2000?
- 关于查询语句中的 CASE
- 求sql语句?
- 寻找以下电子图书。。。。分数不够再给
- 表损坏如何修复?
- 高手请注意!有问题!紧急!我在使用sql server 2000 的时候。程序出错,同样的程序,在我这里不行,在别人哪里就行,请来看看,很短的程序!快快!!!
- 用VB创建了一个SQL表并填数,时间长了就没了,怎么回事(在线等)
- 用SQL如何查询大于两个工作表的平均数的记录?
- SQLServer2008中新建实例的问题!
fd_evaluategrade from tb_teachplanstudent a ,tb_userstatic_evaluateDetail b
where a.fd_usersid_refid=b.fd_usersid_refid
[fd_id] [int] IDENTITY (1, 1) NOT NULL ,
[fd_teachPlanId_refid] [int] NOT NULL ,
[fd_Usersid_refid] [int] NOT NULL ,
[fd_finishHour] [int] NULL CONSTRAINT [DF__tb_TeachP__fd_fi__46A8C1D5] DEFAULT (0),
[fd_examGrade] [int] NULL ,
[fd_passYn] [smallint] NOT NULL CONSTRAINT [DF__tb_TeachP__fd_pa__58085801] DEFAULT (0),
[fd_totalGrade] [float] NULL ,
[fd_examTime] [datetime] NULL ,
[fd_courseId_refid] [int] NULL ,
CONSTRAINT [PK__tb_TeachPlanStud__571433C8] PRIMARY KEY CLUSTERED
(
[fd_id]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [tb_userStatic_EvaluateDetail] (
[fd_EvaluateDetailId] [int] IDENTITY (1, 1) NOT NULL ,
[fd_Usersid_refid] [int] NULL ,
[fd_firstSortCode_refcode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[fd_evaluateGrade] [int] NULL ,
[fd_TestDate] [datetime] NULL CONSTRAINT [DF__tb_userSt__fd_Te__7BC10629] DEFAULT (getdate()),
[fd_answers] [varchar] (5000) COLLATE Chinese_PRC_CI_AS NULL ,
[fd_testwayId] [smallint] NULL CONSTRAINT [DF__tb_userSt__fd_te__760B5A2F] DEFAULT (1),
[fd_gradeOfObjective] [int] NULL ,
[fd_subjectiveGradeYn] [smallint] NULL CONSTRAINT [DF__tb_userSt__fd_su__76FF7E68] DEFAULT (0),
PRIMARY KEY CLUSTERED
(
[fd_EvaluateDetailId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tb_evaluate_exam] (
[fd_examId] [int] IDENTITY (1, 1) NOT NULL ,
[fd_examName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[fd_validity_begintime] [datetime] NULL ,
[fd_validity_endtime] [datetime] NULL ,
[fd_brief] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[fd_uCode_recode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF__tb_evalua__fd_uC__7DAC7BF7] DEFAULT ('joycareer'),
[fd_usingSubjectiveYn] [smallint] NULL CONSTRAINT [DF__tb_evalua__fd_us__7EA0A030] DEFAULT (0),
[fd_ofSelf] [smallint] NULL CONSTRAINT [DF__tb_evalua__fd_of__7F94C469] DEFAULT (0),
[fd_courseId_refid] [int] NULL ,
[fd_OrgCode_refid] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[fd_examId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
tb_teachplanstudent
set
a.fd_examgrade=b.fd_evaluategrade
from
tb_teachplanstudent a join tb_userstatic_evaluateDetail b
on
a.fd_usersid_refid=b.fd_usersid_refid
add fd_examgrade int;
goupdate a set
fd_examgrade = c.fd_evaluategrade
from tb_teachplanstudent as a
join tb_evaluate_exam as b
on a.fd_courseid_refid = b.fd_courseid_refid
join tb_userstatic_evaluateDetail as c
on b.fd_examid=c.fd_firstSortCode_refcode
and c.fd_userid_refid = a.fd_userid_refid;
2-3表 先做个连接 inner join 等到表4 起个新名字
再把表4 里的得分 放到表1对应学号里面