select top3 * from (select 学生课程号,学生号,max(成绩) as 成绩 group by 学生课程号,学生号)a order by 成绩 desc
select top 3 b.课程名,a.学生号,a.成绩 from (select 学生课程号,学生号,sum(成绩) as 成绩 from sorce group by 学生课程号,学生号)a , lessoninfo b where a.学生课程号 = b.课程号 order by 成绩 desc
select top 3 a.学号,a.姓号 b.课程名, c.学生号,sum(c.成绩) 总成绩 , 优秀率=max((select count(*) from score where 成绩>85)/count(*)), 合格率=max((select count(*) from score where 成绩>60)/count(*)) from studentinfo a left join c on a.学号=c.学号 join lessoninfo b on b.课程号=c.课程号 group by a.学号,b.姓名,b.课程名,c.学生号 order by 总成绩 desc
use a create table aa (tel char(10)) drop table aa insert into aa (tel)values('') select '445525611'union select '445111'union select '4455611'union select '44555611'union select '12312323' select tel=(case when len(tel)=7 then tel else '' end) from aa 这个表的字段有课程号、学生课程号、学生号、成绩 另外对应的表是课程表lessoninfo有课程名和课程号 一个学生信息表studentinfo字段有:学号和姓名!我现在想求所有学生总成绩的前三名 需要 求出的字段要有这个学生的学号、姓名、课程名、及总成绩 还有最好是写成一句!!! 还有优秀率和合格率! create table Courseinfo (C_course varchar(10), C_id int ) create table studentinfo (S_no int, S_name varchar(10) ) create table Resultsinfo (R_no int , R_id int , R_score int ) insert into Courseinfo(C_course,C_id) select 'C#',1 union select 'math',2 union select 'English',3 union select 'Chines',4 insert into studentinfo(S_no,S_name) select 3,'cc'union select 4,'dd'union select 5,'ee'union select 6,'ff'union select 1,'aa'union select 2,'bb' insert into Resultsinfo(R_id,R_no,R_score) select 1,1,80 union select 2,1,76 union select 3,1,80 union select 4,1,90 union select 1,2,82 union select 3,2,85 union select 2,2,81 union select 4,2,98 union select 1,3,65 union select 2,3,76 union select 3 ,3,80 union select 4,3,67 union select 1,4,82 union select 3,4,85 union select 2,4,61 union select 4,4,98 union select 1,5,80 union select 2,5,76 union select 3,5,80 union select 4,5,80 union select 1,6,82 union select 3,6,65 union select 2,6,81 union select 4,6,98 drop table Resultsinfo 我现在想求所有学生总成绩的前三名 需要 求出的字段要有这个学生的学号、姓名、课程名、及总成绩 还有最好是写成一句!!! 还有优秀率和合格率! select 'C#',1 union select 'math',2 union select 'English',3 union select 'Chines',4 select top 4 s.S_name, C#=isnull(max(case when c.C_course= 'C# ' then r.R_score end),100), math=isnull(max(case when c.C_course= 'math ' then r.R_score end),100), English=isnull(max(case when c.C_course= 'English ' then r.R_score end),100), Chinese=isnull(max(case when c.C_course= 'Chines' then r.R_score end),100), 总分=sum(isnull( r.R_score,100)) from Courseinfo as c ,Resultsinfo as r,studentinfo as s where c.C_id=r.R_id and s_no=r_no group by s.S_name select 优秀率=(select convert(varchar(5), (select count(*) from Resultsinfo where R_score>=85)/count(*)))+'%', 合格率=(select convert(varchar(5),(select count(*) from Resultsinfo where R_score>=60)/count(*)))+'%'
SQL code select top 3 b.课程名,a.学生号,a.成绩 from (select 学生课程号,学生号,sum(成绩) as 成绩 from sorce group by 学生课程号,学生号)a , lessoninfo b where a.学生课程号 = b.课程号 order by 成绩 desc
select top 3 b.课程名,a.学生号,a.成绩 from (select 学生课程号,学生号,sum(成绩) as 成绩 from sorce group by 学生课程号,学生号)a , lessoninfo b where a.学生课程号 = b.课程号 order by 成绩 desc
[code=SQL][select top 3 b.课程名,a.学生号,a.成绩 from (select 学生课程号,学生号,sum(成绩) as 成绩 from sorce group by 学生课程号,学生号)a , lessoninfo b where a.学生课程号 = b.课程号 order by 成绩 desc ]
select top 3 b.课程名,a.学生号,a.成绩 from (select 学生课程号,学生号,sum(成绩) as 成绩 from sorce group by 学生课程号,学生号)a , lessoninfo b where a.学生课程号 = b.课程号 order by 成绩 desc ]
order by 成绩 desc
from (select 学生课程号,学生号,sum(成绩) as 成绩 from sorce group by 学生课程号,学生号)a ,
lessoninfo b
where a.学生课程号 = b.课程号
order by 成绩 desc
select top 3 a.学号,a.姓号 b.课程名, c.学生号,sum(c.成绩) 总成绩 ,
优秀率=max((select count(*) from score where 成绩>85)/count(*)),
合格率=max((select count(*) from score where 成绩>60)/count(*))
from studentinfo a left join c
on a.学号=c.学号
join lessoninfo b on b.课程号=c.课程号
group by a.学号,b.姓名,b.课程名,c.学生号
order by 总成绩 desc
create table aa
(tel char(10))
drop table aa
insert into aa (tel)values('')
select '445525611'union
select '445111'union
select '4455611'union
select '44555611'union
select '12312323'
select tel=(case when len(tel)=7 then tel else '' end) from aa
这个表的字段有课程号、学生课程号、学生号、成绩 另外对应的表是课程表lessoninfo有课程名和课程号
一个学生信息表studentinfo字段有:学号和姓名!我现在想求所有学生总成绩的前三名 需要
求出的字段要有这个学生的学号、姓名、课程名、及总成绩 还有最好是写成一句!!!
还有优秀率和合格率!
create table Courseinfo
(C_course varchar(10),
C_id int
)
create table studentinfo
(S_no int,
S_name varchar(10)
)
create table Resultsinfo
(R_no int ,
R_id int ,
R_score int
)
insert into Courseinfo(C_course,C_id)
select 'C#',1 union
select 'math',2 union
select 'English',3 union
select 'Chines',4
insert into studentinfo(S_no,S_name)
select 3,'cc'union
select 4,'dd'union
select 5,'ee'union
select 6,'ff'union
select 1,'aa'union
select 2,'bb'
insert into Resultsinfo(R_id,R_no,R_score)
select 1,1,80 union
select 2,1,76 union
select 3,1,80 union
select 4,1,90 union
select 1,2,82 union
select 3,2,85 union
select 2,2,81 union
select 4,2,98 union
select 1,3,65 union
select 2,3,76 union
select 3 ,3,80 union
select 4,3,67 union
select 1,4,82 union
select 3,4,85 union
select 2,4,61 union
select 4,4,98 union
select 1,5,80 union
select 2,5,76 union
select 3,5,80 union
select 4,5,80 union
select 1,6,82 union
select 3,6,65 union
select 2,6,81 union
select 4,6,98
drop table Resultsinfo
我现在想求所有学生总成绩的前三名 需要
求出的字段要有这个学生的学号、姓名、课程名、及总成绩 还有最好是写成一句!!!
还有优秀率和合格率!
select 'C#',1 union
select 'math',2 union
select 'English',3 union
select 'Chines',4
select top 4 s.S_name,
C#=isnull(max(case when c.C_course= 'C# ' then r.R_score end),100),
math=isnull(max(case when c.C_course= 'math ' then r.R_score end),100),
English=isnull(max(case when c.C_course= 'English ' then r.R_score end),100),
Chinese=isnull(max(case when c.C_course= 'Chines' then r.R_score end),100),
总分=sum(isnull( r.R_score,100))
from Courseinfo as c ,Resultsinfo as r,studentinfo as s where c.C_id=r.R_id and s_no=r_no
group by s.S_name
select 优秀率=(select convert(varchar(5), (select count(*) from Resultsinfo where R_score>=85)/count(*)))+'%',
合格率=(select convert(varchar(5),(select count(*) from Resultsinfo where R_score>=60)/count(*)))+'%'
select top 3 b.课程名,a.学生号,a.成绩
from (select 学生课程号,学生号,sum(成绩) as 成绩 from sorce group by 学生课程号,学生号)a ,
lessoninfo b
where a.学生课程号 = b.课程号
order by 成绩 desc
select top 3 b.课程名,a.学生号,a.成绩
from (select 学生课程号,学生号,sum(成绩) as 成绩 from sorce group by 学生课程号,学生号)a ,
lessoninfo b
where a.学生课程号 = b.课程号
order by 成绩 desc
from (select 学生课程号,学生号,sum(成绩) as 成绩 from sorce group by 学生课程号,学生号)a ,
lessoninfo b
where a.学生课程号 = b.课程号
order by 成绩 desc ]
from (select 学生课程号,学生号,sum(成绩) as 成绩 from sorce group by 学生课程号,学生号)a ,
lessoninfo b
where a.学生课程号 = b.课程号
order by 成绩 desc ]