SQL50题，注意47,48的时间问题

最新推荐文章于 2023-03-31 14:44:19 发布
最新推荐文章于 2023-03-31 14:44:19 发布
阅读量324 收藏 3
点赞数 2
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43347382/article/details/94391482
版权

经典sql50题

sql练习的经典习题。需要注意47，48的时间问题，不同年中的同月同日，并不一定是同一周。
自行练习完，在对比学习，效果更佳哦。

建立练题表

# 学生表
create table stu(
	s_id varchar(10) primary key,
    s_name varchar(10) not null,
    s_age date not null,
    s_sex varchar(10) default '未知'
);
# 课程表
create table co(
	c_id varchar(10) primary key,
    c_name varchar(10) not null,
    t_id varchar(102) not null
);
# 教师表 
create table te(
	t_id varchar(10) primary key,
	t_name varchar(10) not null
);
# 成绩表 
create table sc(
	s_id varchar(10) not null,
    c_id varchar(10) not null,
    score int not null
);

插入数据

# stu表数据
insert into stu values
	('01','赵雷','1990-01-01','男'),
    ('02','钱电','1990-12-21','男'),
    ('03','孙风','1990-05-20','男'),
    ('04','李云','1990-08-06','男'),
    ('05','周梅','1991-12-01','女'),
    ('06','吴兰','1992-03-01','女'),
    ('07','王菊','1990-01-20','女');
# co表数据
insert into co values
	('01','语文','02'),
	('02','数学','01'),
    ('03','英语','03');
# te表数据 
insert into te values
	('01','张三'),
	('02','李四'),
    ('03','王五');
# sc表数据 
insert into sc values
	('01','01',80),
	('01','02',90),
    ('01','03',99),
    ('02','01',70),
    ('02','02',50),
    ('02','03',80),
    ('03','01',80),
    ('03','02',80),
    ('03','03',80),
    ('04','01',50),
    ('04','02',30),
    ('04','03',20),
    ('05','01',76),
    ('05','02',87),
    ('06','01',31),
    ('06','03',34),
    ('07','02',89),
    ('07','03',98);

1~10题

# 1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
# 方法1
select s_name, s_age, s_sex, t3.C01_score, t3.C02_score 
from stu,
(select t1.s_id,t1.score as C01_score,t2.score as C02_score from 
(select s_id,score from sc
where c_id = '01') as t1
,
(select s_id, score from sc
where c_id = '02') as t2
where t1.s_id = t2.s_id and t1.score > t2.score) as t3
where stu.s_id = t3.s_id;

# 方法2
select stu.*, b.score as 01_score, c.score as 02_score from 
stu
join sc b on stu.s_id=b.s_id and b.c_id='01'
left join sc c on stu.s_id = c.s_id and c.c_id='02'
where b.score> c.score;

#2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数
# 方法1
select s_name,s_age,s_sex,t3.C01_score ,t3.C02_score 
from stu,
(select t1.s_id,t1.score as C01_score,t2.score as C02_score from 
(select s_id,score from sc
where c_id = '01') as t1
,
(select s_id,score from sc
where c_id = '02') as t2
where t1.s_id = t2.s_id and t1.score < t2.score) as t3
where stu.s_id = t3.s_id ;

# 方法2
select stu.*, b.score as 01_score, c.score as 02_score from 
stu
join sc b on stu.s_id=b.s_id and b.c_id='01'
left join sc c on stu.s_id = c.s_id and c.c_id='02'
where b.score < c.score;

#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
# 方一
select stu.s_id,s_name,round(t1.平均成绩,2)
from stu,
(select s_id, avg(score) as '平均成绩' from sc
group by s_id
having avg(score) >= 60) as t1
where stu.s_id = t1.s_id;

# 方二
select stu.s_id,s_name,round(avg(sc.score),2) from stu
right join sc on stu.s_id = sc.s_id
group by sc.s_id
having avg(sc.score) >= 60;

#4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
# 方1
select stu.s_id,s_name,t1.平均成绩 
from stu,
(select s_id,avg(score) as '平均成绩' from sc
group by s_id
having avg(score) < 60) as t1
where stu.s_id = t1.s_id;

# 方2
select stu.s_id,s_name,round(avg(sc.score),2) from stu
left join sc on stu.s_id = sc.s_id
group by sc.s_id
having avg(sc.score) < 60;

#5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select stu.s_id,s_name,count(score) as '选课总数',sum(score) as '总成绩' from stu
left join sc on stu.s_id = sc.s_id
group by stu.s_id;

#6、查询"李"姓老师的数量 
select count(t_id) as '李姓老师数量' from te
where t_name like '李%';

#7、查询学过"张三"老师授课的同学的信息 
select stu.s_id,s_name,s_age,s_sex from stu,
(select s_id from sc,
(select c_id from co
left join te on co.t_id = te.t_id
where t_name = '张三') t1 
where sc.c_id = t1.c_id) t2
where stu.s_id = t2.s_id;

#8、查询没学过"张三"老师授课的同学的信息 
select stu.s_id,s_name,s_age,s_sex from stu
where stu.s_id not in 
(select s_id from sc,
(select c_id from co
left join te on co.t_id = te.t_id
where t_name = '张三') t1 
where sc.c_id = t1.c_id);

#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
# 方1
select s_id,s_name,s_age,s_sex from stu
where s_id in
(select s_id from sc
where c_id = '02' and s_id in
(select s_id from sc
where c_id = '01'));

# 方2
select a.* from 
	stu a, sc b, sc c 
	where a.s_id = b.s_id  and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';

#10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
# 方1
select s_id,s_name,s_age,s_sex from stu
where s_id in
(select s_id from sc
where c_id = '01' and s_id not in
(select s_id from sc
where c_id = '02'));

11~20题

#11、查询没有学全所有课程的同学的信息 
select s_id,s_name,s_age,s_sex from stu
where s_id in
(select s_id from sc
group by s_id
having count(c_id) < (select count(c_id) from co));

#12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
select s_id,s_name,s_age,s_sex from stu
where s_id in 
(select distinct s_id from sc
where s_id != '01' and c_id in
(select c_id from sc
where s_id = '01'));

#13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
select s_id,s_name,s_age,s_sex from stu
where s_id in
(select s_id from 
(select s1.s_id from sc s1 join sc s2 on s1.c_id=s2.c_id where s1.s_id<>'01' and s2.s_id ='01') as t1
group by s_id having count(s_id) = (select count(c_id) from sc where s_id = '01'));

#14、查询没学过"张三"老师讲授的任一门课程的学生姓名
 select stu.s_id,s_name,s_age,s_sex from stu
where stu.s_id not in 
(select s_id from sc,
(select c_id from co
left join te on co.t_id = te.t_id
where t_name = '张三') t1 
where sc.c_id = t1.c_id);


#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
select stu.s_id,s_name,平均成绩 from stu
right join 
(select s_id,avg(score) as '平均成绩' from sc
where score < 60
group by s_id having count(s_id) >= 2) t1
on stu.s_id = t1.s_id;

#16、检索"01"课程分数小于60，按分数降序排列的学生信息
select t1.s_id,s_name,s_age,s_sex,score from stu
right join 
(select s_id,score from sc
where c_id = '01' and score < 60) t1
on stu.s_id = t1.s_id
order by score DESC;

#17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_id 学生编号, a.s_name 学生姓名,
	max(case c.c_name when '语文' then b.score else 0 end) 语文,
	max(case c.c_name when '数学' then b.score else 0 end) 数学,
	max(case c.c_name when '英语' then b.score else 0 end) 英语,
	cast(avg(b.score) as decimal(18,2)) 平均分
from stu a
left join sc b on a.s_id = b.s_id
left join co c on b.c_id = c.c_id
group by a.s_id
order by 平均分 desc;

#18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，
		#及格率，中等率，优良率，优秀率 
		# 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select co.c_id as '课程ID',c_name as '课程nam',
	max(score) as '最高分',
	min(score) as '最低分',
	cast(avg(score) as decimal(18,2)) as '平均分',
    cast(((select count(score) from sc where score >=60 and c_id = co.c_id)/count(score))as decimal(18,2)) as '及格率',
	cast(((select count(score) from sc where score >=70 and score <80 and c_id = co.c_id)/count(score))as decimal(18,2)) as '中等率',
	cast(((select count(score) from sc where score >=80 and score <90 and c_id = co.c_id)/count(score))as decimal(18,2)) as '优良率',
	cast(((select count(score) from sc where score >=90 and c_id = co.c_id)/count(score))as decimal(18,2)) as '优秀率'
from co
left join sc on co.c_id = sc.c_id
group by co.c_id;

#19、按各科成绩进行排序，并显示排名
# 方1  计算人数 分科排名 
select t1.*,(select count(score) from sc where c_id = t1.c_id and score >= t1.score) as '排名' 
from sc as t1
order by c_id,score DESC;

# 方2  会全排 
select a.s_id,a.c_id,
        @i:=@i +1 as i保留排名,
        @k:=(case when @score=a.score then @k else @i end) as rank不保留排名,
        @score:=a.score as score
from 
(select s_id,c_id,score from sc GROUP BY s_id,c_id,score ORDER BY score DESC) a,
(select @k:=0,@i:=0,@score:=0) s;


#20、查询学生的总成绩并进行排名
select s_id,s_name,s_age,s_sex,总成绩,
	@i:=@i+1 as rank
from
(select stu.s_id,s_name,s_age,s_sex,
	sum(score) as '总成绩'
from stu
left join sc as t1 on stu.s_id = t1.s_id
group by t1.s_id
order by 总成绩 desc) s,
(select @i:=0) a;

21~30题

#21、查询不同老师所教不同课程平均分从高到低显示 
select te.t_name, co.c_name, round(avg(score),2) from te
left join co on te.t_id = co.t_id
left join sc on co.c_id = sc.c_id
group by te.t_name, co.c_name
order by avg(score) desc;


#22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
create view result3 as
SELECT stu.*, co.c_name, sc.score FROM stu,sc,co WHERE stu.s_id = sc.s_id AND sc.c_id = co.c_id GROUP BY sc.c_id,sc.s_id;

(select * from result3 where c_name = '语文' order by score desc limit 1,2)
union
(select * from result3 where c_name = '数学' order by score desc limit 1,2)
union
(select * from result3 where c_name = '英语' order by score desc limit 1,2);

#23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 
select sc.c_id,co.c_name,
	(select count(c_id) from sc sc1 where sc.c_id = sc1.c_id and score>=85) as '[100-85]',
	cast(((select count(c_id) from sc sc1 where sc.c_id = sc1.c_id and score>=85)/(select count(c_id) from sc sc1 where sc.c_id = sc1.c_id)*100)as decimal(18,2))as '[100-85](%)',
    (select count(c_id) from sc sc1 where sc.c_id = sc1.c_id and score between 70 and 85) as '[85-70]',
    cast(((select count(c_id) from sc sc1 where sc.c_id = sc1.c_id and score between 70 and 85)/(select count(c_id) from sc sc1 where sc.c_id = sc1.c_id)*100)as decimal(18,2))as '[85-70](%)',
    (select count(c_id) from sc sc1 where sc.c_id = sc1.c_id and score between 60 and 70) as '[70-60]',
    cast(((select count(c_id) from sc sc1 where sc.c_id = sc1.c_id and score between 60 and 70)/(select count(c_id) from sc sc1 where sc.c_id = sc1.c_id)*100)as decimal(18,2))as '[70-60](%)',
    (select count(c_id) from sc sc1 where sc.c_id = sc1.c_id and score < 60) as '[0-60]',
    cast(((select count(c_id) from sc sc1 where sc.c_id = sc1.c_id and score < 60)/(select count(c_id) from sc sc1 where sc.c_id = sc1.c_id)*100)as decimal(18,2))as '[0-60](%)'
from sc,co
where sc.c_id = co.c_id
group by sc.c_id;

#24、查询学生平均成绩及其名次 
select b.*, @j:=@j+1 as '排名' 
from 
(SELECT stu.s_id,stu.s_name,avg(sc.score) AS score 
FROM stu,sc
WHERE stu.s_id = sc.s_id 
GROUP BY sc.s_id 
ORDER BY avg(sc.score) DESC) as b,
(SELECT @j := 0 ) sdcore;


#25、查询各科成绩前三名的记录
create view result4 as
select stu.*,co.c_name,sc.score from stu,co,sc
where stu.s_id = sc.s_id and sc.c_id = co.c_id
group by co.c_name,stu.s_id;

select t1.c_name,t1.s_name,t1.score from
result4 as t1
left join result4 as t2
on t1.c_name = t2.c_name and t2.score > t1.score
group by t1.c_name,t1.s_id
having count(t2.c_name) < 3
order by t1.c_name,t1.score desc;

#26、查询每门课程被选修的学生数 
select co.c_name,count(score) from co
left join sc on co.c_id = sc.c_id
group by co.c_name;

#27、查询出只有两门课程的全部学生的学号和姓名 
 select sc.s_id,stu.s_name from sc,stu
 where sc.s_id = stu.s_id
 group by sc.s_id
 having count(score) = 2;
 
#28、查询男生、女生人数
# 方1 
select sum(case stu.s_sex when '男' then 1 else 0 end) 男生,
	   sum(case stu.s_sex when '女' then 1 else 0 end) 女生
from stu;

# 方2
select s_sex as '性别',COUNT(1) as '人数' 
from stu
group by s_sex;

#29、查询名字中含有"风"字的学生信息
select * from stu
where s_name like '%风%';

#30、查询同名同性学生名单，并统计同名人数
select s_name,s_sex,count(s_name) 
from stu 
group by s_name,s_sex 
having count(s_name) >1;

31~40题

#31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime) 
select * from stu
where year(s_age) = 1990;

#32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号
select co.c_name,avg(score) from co
left join sc on co.c_id = sc.c_id
group by co.c_id
order by avg(score) desc,co.c_id asc;

#33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
 select stu.s_id,stu.s_name,avg(score) from stu,sc
 where stu.s_id = sc.s_id
 group by stu.s_id
 having avg(score)>=85;
 
#34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 
select co.c_name,s_name,score from co
left join sc on co.c_id = sc.c_id
left join stu on sc.s_id = stu.s_id
where c_name = '数学' and score < 60;

#35、查询所有学生的课程及分数情况；
# 方1 堆叠方式
 select s_name,c_name,score from stu
 left join sc on stu.s_id = sc.s_id
 left join co on sc.c_id = co.c_id
 order by s_name,c_name,score desc;
 
 # 方2 展开方式
 select a.s_id 学生编号, a.s_name 学生姓名,
	max(case c.c_name when '语文' then b.score else 0 end) 语文,
	max(case c.c_name when '数学' then b.score else 0 end) 数学,
	max(case c.c_name when '英语' then b.score else 0 end) 英语,
	cast(sum(b.score) as decimal(18,2)) 总分
from stu a
left join sc b on a.s_id = b.s_id
left join co c on b.c_id = c.c_id
group by a.s_id
order by 总分 desc;
 
#36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
 select s_name,c_name,score from sc
 left join stu on sc.s_id = stu.s_id
 left join co on sc.c_id = co.c_id
 where score > 70;
 
#37、查询不及格的课程
 select s_name,c_name,score from sc
 left join stu on sc.s_id = stu.s_id
 left join co on sc.c_id = co.c_id
 where score < 60;

#38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select stu.s_id,s_name,c_name,score from co
left join sc on sc.c_id = co.c_id
left join stu on sc.s_id = stu.s_id
where co.c_id = '01' and score >=80;
 
#39、求每门课程的学生人数 
 select c_name,count(sc.c_id) from co,sc
 where co.c_id = sc.c_id
 group by co.c_id;

#40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.s_id,s_name,s_age,s_sex,c_name,score from te
left join co on te.t_id = co.t_id
left join sc on co.c_id = sc.c_id
left join stu on sc.s_id = stu.s_id
where t_name = '张三 '
order by score desc
limit 1;

41~50题

#41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
select distinct sc.s_id,sc.c_id,sc.score from sc
left join sc as sc1 on sc.c_id != sc1.c_id
where sc.score = sc1.score;

#42、查询每门功成绩最好的前两名 
# 方1
create view result5 as 
select stu.s_id,s_name,c_name,score from sc
left join co on sc.c_id = co.c_id
left join stu  on sc.s_id =stu.s_id;

select a.c_name,a.s_id,a.s_name,a.score from result5 as a
left join result5 as b on a.c_name = b.c_name and b.score > a.score
group by a.c_name,a.s_id,a.s_name
having count(b.s_id) < 2;

# 方2
select co.c_name, stu.s_name ,a.score from sc a
left join co on a.c_id = co.c_id
left join stu on a.s_id = stu.s_id
where (select COUNT(1) from sc b where b.c_id=a.c_id and b.score>=a.score) <=2 
ORDER BY a.c_id;

#43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
select c_id,count(1) from sc
group by c_id
having count(1) > 5
order by count(1) desc, c_id;

#44、检索至少选修两门课程的学生学号 
select s_id, count(1) from sc
group by s_id 
having count(1)>=2;

#45、查询选修了全部课程的学生信息 
select stu.s_id,s_name,s_age,s_sex from stu
right join 
(select s_id from sc
group by s_id
having count(1) = (select count(1) from co)) t1
on stu.s_id = t1.s_id;

#46、查询各学生的年龄
# 判断生日是否已过
select s_name, (year(curdate())-year(s_age)- 
	(case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_age,'%m%d') then 0 else 1 end)) as age 
from stu;

#47、查询本周过生日的学生 
# 剔除年的影响，不同年的同一日不一定是同一周
select s_name s_age from stu
where weekofyear(makedate(year(curdate()),dayofyear(s_age))) = weekofyear(curdate()); 


#48、查询下周过生日的学生
# 剔除年的影响，不同年的同一日不一定是同一周
select s_name s_age from stu
where weekofyear(makedate(year(curdate()),dayofyear(s_age))) - weekofyear(curdate()) = 1;

#49、查询本月过生日的学生
select s_id,s_name,s_age from stu
where month(curdate()) = month(s_age);

#50、查询下月过生日的学生 
select s_id,s_name,s_age from stu
where month(curdate()) - month(s_age) = -1;
无尽渊源
关注
专栏目录
Hive SQL大厂面试题必考大全
CSDN 精品推荐
07-17 507
专栏内容涵盖了基于Hive SQL的各类题目，旨在通过解析经典问题和案例，帮助读者深入理解Hive SQL的原理和应用。每篇文章都将附带完整的题目代码和详细的解题思路，理论与实践相结合，帮助你更好地掌握Hive SQL的知识。
MySQL第九次作业（复合查询）
weixin_61961580的博客
04-03 963
复合查询
如何使用mysql计算成功率
mcband的博客
03-31 1354
现有以下数据,org_id代表某个项目,project_status代表是否成功,其中2代表成功,0为失败.现要计算每个项目的成功率,比如:id后四位为0386的总共有6条,成功一条,那它的成功率为1/7*100%,大概在14%
mysql基础
春风吹尽叁佰里的博客
03-13 442
mysql5728安装 卸载系统自带残留 yum remove mariadb-libs -y 初始化数据 mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data #空密码 系统管理mysql cp /data/app/my...
mysql常用sql语句
qq_42698465的博客
11-15 1076
Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号； select a.S# from (select s#,score from SC where C#=‘001’) a,(selec...
SQL 经典50题（题目+解答）（1）
Robin_Pi的博客
03-10 1万+
SQL 经典50题（题目+解答）（1）
SQL面试题:求时间差之和(有重复不计)
09-09
在SQL面试中，计算时间差之和的问题是一个常见的挑战，特别是当存在重复日期时，我们需要排除这些重复以得到准确的结果。本题目的目标是求出每个品牌的促销天数，但不考虑日期重叠的部分。以下将详细介绍如何解决这...
sqlserver试题含答案.doc
最新发布
05-04
1.2 写出一条Sql语句：取出表A中第31到第40记录（SQL Server，以自动增长的ID作为主键，注意：ID可能不是连续的）。 答：解法1：select top 10 * from A where id not in (select top 30 id from A) 解法2：select...
sql面试题(查看数据中指定几行记录)
09-10
3. 不同的数据库管理系统（DBMS）可能有不同的语法，如SQL Server的`TOP`与Oracle的`ROWNUM`，在编写SQL语句时需考虑兼容性问题。 理解这些基本概念和技巧对于任何SQL开发者来说都是至关重要的，特别是在面试或实际...
SQL中的case…when…还能这么用（计算好评率、计算出有条件的字段的数量）
12-14
文章目录SQL之CASE WHEN用法详解场景1：有分数score，score=60返回及格，score>=80返回优秀场景2：现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出结果。场景3：经典行转列，并配合聚合函数做统计，现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果场景4：CASE WHEN中使用子查询，根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。场景5：结合max聚合函数一道真实的面试题 SQL之CASE WHEN用法详解 简单CASE WH
MYSQL求百分比的几种方法
热门推荐
一个混错圈儿的小测试
11-12 3万+
一、函数介绍 1、ROUND(X,D)和ROUND(X) round函数用于数据的四舍五入，x指要处理的数，d是指保留几位小数 round(x) ，其实就是 round(x,0) d可以是负数，代表指定小数点左边的d位整数位为0,同时小数位均为0 2、FORMAT(X,D) 提供数据内容格式化功能，可以格式化数据为整数或者浮点数，能四舍五入 D为负数时，按0处理 3、LEFT(str,len) left函数是一个字符串函数，它返回具有指定长度的字符串的左边部分,str为要.
python初学——mysql练习50题
weixin_34009794的博客
05-10 450
创建表请创建如下表，并创建相关约束。如图1操作表中的内容1、自行创建测试数据；2、查询学生总人数；3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；4、查询每个年级的班级数，取出班级数最多的前三个年级；5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；6、查询每个年级的学生人数；7、查询每位学生的学号，姓名，选课数，平...
mysql计算增长率
lsqingfeng的博客
05-07 3970
好久没更新了，最近实在是太忙了，基本上天天和sql打交道。恰巧今天遇到了一个问题，在这里和大家分享一下，就是使用sql计算增长率的问题。 我数据库中的原始数据是这样的： 2016 100 2017 200 2018 140 2019 300 2020 150 2021 180 但是我们前端需要展现的是一条折现，显示的是增长率，或者叫同比增长， 这个大家应该都知道吧，公式如下: (后一年数据 - 前一年数据) / 前一年的数据。 这个一开始...
五个简单的 SQL 查询性能测试题，只有 40% 及格率。你敢来挑战吗？
weixin_44519342的博客
02-10 289
五个简单的 SQL 查询性能测试题，只有 40% 及格率。你敢来挑战吗？
sqlserver 统计及格率
web开发
02-23 1713
id sid sjname res 1 1 数学 80 2 1 数学 50 3 2 英语 61 4 2 英语 59 5 3 语文 58 6 3 语文 58 7 1 数学 81 id是学生id sid 老师 sjname 科目 res 分数 最后要求结果是 老师 总人数 及格人数 及格率 1 3 2 66.6667% 2 2 1 ...
mysql统计数学课的及格人数_SQL 查询某门课程及格的总人数以及不及格的总人数以及没成绩的人数...
weixin_42363315的博客
01-19 6732
展开全部1、创建测试表，create table test_score(class_id varchar2(20), student_id varchar2(20), score number);2、插入测试数据insert into test_score values ('C07', 1001, 50);insert into test_score values ('C07', 1002, 72...
MySQL15道经典练习题
weixin_64313980的博客
07-04 4006
前言：本期博客就不标明目录了，我们直接上题目，这一期内容主要是唤醒哪些沉睡在大家心中的之前的SqlServer与oralc的知识点，以及巩固我们的MySQL，其中较难的知识点可能就是联表了，什么内联，外联，左联右联让人头皮发麻，但是不要慌，跟着小刘的步伐慢慢理清思路首先是我们的建表，这里已经把需要的表的代码都交代上了一共四张表,学生，教师，成绩，课程 然后来看看我们的题目： 接下来就是我们的解题部分：01）查询" 01 "课程比" 02 "成绩高的学生的信息及课程分数 语句： 结果： 02）查询同
Oracle SQL 实践题与解析
"Oracle练习题提供了对Oracle数据库操作的基础练习，包括创建表、插入数据等操作。" 在Oracle SQL中，我们看到涉及了对数据库的基本操作，如创建表（CREATE TABLE）和插入数据（INSERT INTO）。以下是这些练习题中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

无尽渊源

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值