//创建表
create table Credits
(
SNO int primary key,
NoPass int,
SumCredit float
)
//找到学号插入
insert into
Credits (SNO)
select distinct SNO
from SC
//找到总学分插入
insert into
Credits (SumCredit,SumCredit)
select SC.SNO, SUM(c2.CREDIT) as SumCredit
from SC, Courses c1, Courses c2
group by SNO
//找到挂科数目插入
insert into
Credits (SNO,NoPass)
select distinct one.SNO, COUNT(one.GRADE) as NoPass
from Courses,SC one ,SC two
where one.SNO = two.SNO and Courses.CNO = one.CNO
group by one.SNO,one.GRADE
having one.GRADE<60
你前面只指明一个字段
而后面却有两个字段
你让sqlserver该如何去做
Students SNO Courses 的結構貼出來,樓主的意思應該不是每個表單獨查詢再插入,
應該是用連接吧
表结构是这样子的Students(SNO)
Courses(CNO,Credit)//Credits表示学分
SC(SNO,CNO,GRADE)//GRADE表示分数,CNO表示课程号,用以和SC连接
感谢楼上指出的指明字段数的不一样
重新想了一下//插入学号
insert into
Credits (SNO)
select distinct SNO
from Students
//插入总学分
insert into
Credits (SumCredit)
select SUM(c2.CREDIT) as SumCredit
from SC, Courses c1, Courses c2
group by SNO
//插入挂科数目
insert into
Credits (NoPass)
select COUNT(one.GRADE) as NoPass
from Courses,SC one ,SC two
where one.SNO = two.SNO and Courses.CNO = one.CNO
group by one.SNO,one.GRADE
having one.GRADE<60