侧边栏壁纸
  • 累计撰写 781 篇文章
  • 累计创建 1 个标签
  • 累计收到 1 条评论
标签搜索

成绩相关 题

Dettan
2021-04-10 / 0 评论 / 0 点赞 / 120 阅读 / 1,170 字
温馨提示:
本文最后更新于 2022-04-30,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

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.
均为及格, 不能用in ,
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

评论区