  • 累计撰写 793 篇文章
• 累计创建 1 个标签
• 累计收到 1 条评论

### 目 录CONTENT # 成绩相关 题 2021-04-10 / 0 评论 / 0 点赞 / 201 阅读 / 396 字 1.
select * from student order by brithday
2.
select
case when course.name='语文' then score.score else 0 end as 语文
case when course.name='数学' then score.score else 0 end as 数学
case when course.name='英语' then score.score else 0 end as 英语
case when course.name='物理' then score.score else 0 end as 物理
from student
left join  score using(studentid)
left join course using(courseid)
group by student.studentid
3.

select course.name as '课程名称' concat(round(100*sum(case when score.score≥60 then 1.0 else 0.0)/count(score) ,2),'%') as '及格率'
from score join course using(courseid)
group by score.courseid
4.

SELECT 学生.班级,COUNT(学生选课.成绩<60)
FROM 学生选课
LEFT JOIN 学生
ON 学生选课.学生ID＝学生.学生ID
GROUP BY 学生.班级

select * from student
where studentid not in (select studentid from score where score>=60)
5.

select student.name,score.score from score sa join student using(studentid)
where (select count(*) from score sb where sa.courseid=sb.courseid and sb.score>sa.score)<=2
group by courseid order by courseid,socre desc

sql 第二题 select
sum(case when  证件基本数据表.永久有效='否' and 有效结束日期 ≥ now() then 1 else 0) as 已过期
sum(case when  审核状态字典.文字='审核结束' then 1 else 0) as 审核结束
sum(case when  审核状态字典.文字='审核中' then 1 else 0) as 审核中
sum(case when  审核状态字典.文字='待审核' then 1 else 0) as 待审核

from  证件表 left join 审核状态字典 on 证件表.证件编号=审核状态字典.编号
left join 证件基本数据表 using(证件编号)


0