高分求条SQL语句

发布网友发布时间：2023-12-12 13:40

共2个回答

热心网友时间：2024-02-19 16:49

select ls.studentid,c.courseid,sum(ls.lessonscoresum) from course c,lesson l,lessonscore ls where c.courseid=l.courseid and ls.lessonid=l.lessonid group by c.courseid,ls.studentid order by ls.studentid

sql 给你，分数给我。谢谢

补充你的回答：
如果是mysql，把StudentID,CourseID做成双主键或者unique key
把insert inot 换成replace into
还有一种办法：
因为这个是统计表，所以可以把这个表清空(delete/truncate)，再insert进去，其实这种性能比replace更好些

热心网友时间：2024-02-19 16:49

create table Course (CourseID varchar(50) primary key)
insert Course values ('1')
insert Course values ('2')
create table Lesson (LessonID varchar(50) primary key,CourseID varchar(50))
insert Lesson values ('1','1')
insert Lesson values ('2','1')
insert Lesson values ('3','2')
insert Lesson values ('4','2')
create table LessonScore (studentid varchar(50),lessonid varchar(50),LessonScoreSum int)
insert LessonScore values ('0001','1','2')
insert LessonScore values ('0001','2','8')
insert LessonScore values ('0001','3','13')
insert LessonScore values ('0001','4','7')
insert LessonScore values ('0002','1','18')

select ls.studentid,l.CourseID,sum(ls.LessonScoreSum) from Course c,Lesson l,LessonScore ls
where c.CourseID=l.CourseID and l.LessonID=ls.lessonid group by l.CourseID,ls.studentid order by ls.studentid
楼上的也是正解,晕,出去了一躺,比你慢了一步
就是删除表单在insert select的结果集