50道SQL题目

地址:
https://www.cnblogs.com/Diyo/p/11424844.html 大佬地址比较全
以下是自己写的

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT
	a.s_id,
	max( CASE WHEN a.c_id = '01' THEN a.s_score END ) s01,
	max( CASE WHEN a.c_id = '02' THEN a.s_score END ) s02 
FROM
	score a 
GROUP BY
	a.s_id 
HAVING
	s01 > s02

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select s.* from student s,(
select
 a.s_id,
 max(case when a.c_id = '01' then a.s_score end) s01,
 max(case when a.c_id = '02' then a.s_score end) s02
from

score a
GROUP BY a.s_id
HAVING s01 < s02
) t
where s.s_id = t.s_id


-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select
	a.s_id,
	AVG(a.s_score) avg_s
from
	score a
GROUP BY
 a.s_id
HAVING AVG(a.s_score) >=60 


-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)
				
select
	a.*,
	ROUND(AVG(b.s_score),2)
from
	student a
	
LEFT JOIN
	score b
	
on
	a.s_id = b.s_id
GROUP BY b.s_id
HAVING AVG(b.s_score) < 60
ORDER BY a.s_id 
-- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select s.*,t.avgs from student s,(
select
 a.s_id,
 count(case when a.s_score<60 then a.s_score END),
 AVG(a.s_score) avgs
from 

score a
GROUP BY a.s_id
HAVING count(case when a.s_score<60 then a.s_score END) >=2
) t
where s.s_id = t.s_id


select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from 
    student a 
    left join score b on a.s_id = b.s_id
    where a.s_id in(
            select s_id from score where s_score<60 GROUP BY  s_id having count(1)>=2)
    GROUP BY a.s_id,a.s_name

-- 16、检索"01"课程分数小于60,按分数降序排列的学生信息

select 
b.*,
	a.s_id,
	case when a.c_id = '01' then a.s_score end s
from 
	score a ,student b
	WHERE  a.s_id = b.s_id
GROUP BY a.s_id
	HAVING s <60
	ORDER BY a.s_score DESC

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select a.s_id,(select s_score from score where s_id=a.s_id and c_id='01') as 语文,
                (select s_score from score where s_id=a.s_id and c_id='02') as 数学,
                (select s_score from score where s_id=a.s_id and c_id='03') as 英语,
            round(avg(s_score),2) as 平均分 from score a  GROUP BY a.s_id ORDER BY 平均分 DESC;
						
						
select 
	a.*,
	AVG(s_score) over(partition by a.s_id) avg_s
from 
	score a 

select 
	a.*,
	sum(s_score) over(partition by a.s_id) avg_s
from 
	score a; 
	
	
-- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
-- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90

SELECT
	a.c_id,b.c_name,
	MAX(a.s_score),
	MIN(a.s_score),
	AVG(a.s_score),
	sum(case when a.s_score>=60 then 1 else 0 end)/count(1) jige
FROM
	score a
	LEFT JOIN course b ON a.c_id = b.c_id
GROUP BY a.c_id


-- 19、按各科成绩进行排序,并显示排名(实现不完全)
-- mysql没有rank函数
select
	a.c_id,a.s_score,
	RANK() over(PARTITION by a.c_id ORDER BY a.s_score DESC)
from 
 score a
 
 -- 19如果相同名次不占用序号
 select
	a.c_id,a.s_score,
	Dense_Rank() over(PARTITION by a.c_id ORDER BY a.s_score DESC)
from 
 score a
 
 
 -- 19子查询解决排序问题
 select
 a.*,
 (select count(s_score) from score b where a.c_id = b.c_id and a.s_score<b.s_score)+1 rk
 from
 score a
 ORDER BY c_id,s_score desc
 
 -- 19.子查询解决相同分数顺序排名不占序号
 select
 a.*,
 (select count(DISTINCT s_score) from score b where a.c_id = b.c_id and a.s_score<=b.s_score) rk
 from
 score a
 ORDER BY c_id,s_score desc


-- 20、查询学生的总成绩并进行排名
SELECT
	t.*,
	RANK() over ( ORDER BY t.su desc) 
FROM
	( SELECT a.s_id, SUM( a.s_score ) su FROM score a GROUP BY a.s_id ) t


-- 创建临时表方便查看
create TABLE sums_temp AS SELECT
a.s_id,
SUM( a.s_score ) su 
FROM
	score a 
GROUP BY
	a.s_id;


-- 子查询解决
select 
	a.*,
	(select count(su) from sums_temp b where a.su<b.su) +1
 from sums_temp a
 order by a.su desc

 -- 21、查询不同老师所教不同课程平均分从高到低显示 
 select
	b.c_name,avg(s_score) avg_score,c.t_name
 from
 score a
 left JOIN
 course b
 on a.c_id = b.c_id
  left join teacher c on b.t_id=c.t_id
	group by a.c_id
	ORDER BY avg_score DESC;
	
	
 select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score from course a
        left join score b on a.c_id=b.c_id 
        left join teacher c on a.t_id=c.t_id
        GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC;
 
 -- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
 select b.*,t.s_id from student b,
 (SELECT
  a.s_id,
	a.s_score,
	RANK() over(order by a.s_score desc) rk
 from 
 score a) t
 where
 b.s_id = t.s_id
 and rk in(2,3);
 
 -- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
 
select
	a.c_id,a.c_name,
	round(sum(case when b.s_score>=85 then 1 else 0 end)/COUNT(1),2),
	round(sum(case when b.s_score>=70 and b.s_score<85 then 1 else 0 end)/COUNT(1),2),
	round(sum(case when b.s_score>=60 and b.s_score<70 then 1 else 0 end)/COUNT(1),2),
	round(sum(case when b.s_score>0 and b.s_score<60 then 1 else 0 end)/COUNT(1),2)
from
 course a
LEFT JOIN
	score b
on a.c_id = b.c_id
GROUP BY a.c_id,a.c_name;

 
-- 24、查询学生平均成绩及其名次
 
 select
 a.s_id,
 AVG(a.s_score) pj,
 RANK() over(ORDER BY a.s_score desc)
 
 from
 
 score a
 GROUP BY a.s_id;
 
 
 -- 25、查询各科成绩前三名的记录
            -- 1.选出b表比a表成绩大的所有组
            -- 2.选出比当前id成绩大的 小于三个的



select a.s_id,a.c_id,a.s_score from score a 
            left join score b on a.c_id = b.c_id and a.s_score<b.s_score
            group by a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3
            ORDER BY a.c_id,a.s_score DESC;

-- 26、查询每门课程被选修的学生数 

 select c_id,count(s_id) from score a GROUP BY c_id;
 
-- 27、查询出只有两门课程的全部学生的学号和姓名

select s.* from student s where s.s_id in (
select
a.s_id
from 
score a
 GROUP BY a.s_id
 HAVING COUNT(a.c_id) =2
 );
 

-- 28、查询男生、女生人数

select
COUNT(a.s_sex)
from
student a
GROUP BY a.s_sex;

-- 30、查询同名同性学生名单,并统计同名人数 


select
 a.*,count(1)
from

student a
GROUP BY a.s_name,a.s_sex
HAVING COUNT(a.s_name)=2;


-- 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列 

select 
a.c_id,
AVG(a.s_score) avg_score
from
score a
GROUP BY a.c_id
ORDER BY avg_score DESC,c_id ASC;



-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
select s.* from student s where s.s_id in(
select
a.s_id
from
score a
GROUP BY a.s_id
HAVING AVG(a.s_score)>= 80;
) ;

-- 34、查询课程名称为"数学",且分数低于60的学生姓名和分数 
select s.* from student s where s.s_id in(
select 
 a.s_id
from
score a
left join
course b
on
a.c_id = b.c_id
WHERE b.c_name = '数学'
and a.s_score>60
);
 
 -- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
 select
	 b.*,a.c_id,a.s_score
 from 
 
 score a
 left join
 student b
 on a.s_id = b.s_id
 where
 a.s_score >70
 GROUP BY b.s_name;
 

-- 46、查询各学生的年龄
 select
		s.*,YEAR(now()) -year(s.s_brith) age 
 FROM
	student s;
	

 -- 本周过生日
 
	-- 生日在周内
	select 
	* from 
	student s
	where 
	WEEKOFYEAR(s.s_brith) = (select	WEEKOFYEAR( STR_TO_DATE(concat(YEAR(NOW()),date_format('2021-05-07','%m%d')),'%Y%m%d')))+1
 
	
 select STR_TO_DATE(concat(YEAR(NOW()),date_format('2021-05-07','%m%d')),'%Y%m%d')
 
 
 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值