根据老师讲的，自己整理思路（除了老师提供的，也可以是你真实遇到的面试题），将问题或答案提交到柠檬班论坛
-- 创建成绩表
drop table if EXISTS tb_lemon_score;
create table tb_lemon_score(
sname varchar(20),
course varchar(20),
score tinyint
);
INSERT tb_lemon_score VALUES
('张三','语文',71),
('张三','数学',75),
('李四','语文',76),
('李四','数学',90),
('王五','语文',81),
('王五','数学',100),
('王五','英语',90);
回复形式：
1、问题+答案 或者 问题求助
2、本次系列课的心得体会，收获、建议以及评价都可以哦~~
注意：老师会评选出优秀回复+作业全勤+到课全勤，将会有大奖等着优秀的你
#1、用一条SQL语句查询出每门课都大于80分的学生姓名。
-- 每门课就要按学生进行分组，每门课都大于80意思就是所有课中最小的分数要大于80
-- 解决思路：这里需要用到聚合函数min(score)，查询出最低分数大于80分的学生即可。使用了聚合函数，条件就需要使用having语句。
SELECT name from TestScores GROUP BY name having MIN(score)>80;
-- 子查询的结果作为外部查询的条件
select DISTINCT name from TestScores where name not in (select name from TestScores where score <= 80);
-- 子查询的结果作为表
select * from(SELECT name,MIN(score) 最小成绩 from TestScores GROUP BY name) s where s.最小成绩 > 80;
#子查询的关联查询
SELECT * FROM TestScores t1 where not exists (select * from TestScores t2 where t1.name=t2.nameand score <= 80);
-- 2、查出所有购入商品为两种或两种以上的购物人记录
select 购物人 from 购物单 GROUP BY 购物人 HAVING COUNT() >= 2;
SELECT * from 购物单 where 购物人 in (select 购物人 from 购物单 GROUP BY 购物人 HAVING COUNT() >= 2);
老师讲的第四种答案
select * from tb_lemon_score t1 where not exist (select * from tb_lemon_score t2 where t1.sname=t2.sname and t2.score <=80)
可以改成 select * from tb_lemon_score t1 where t1.sname not exist (select * from tb_lemon_score t2 where t2.score <=80) 吗？课堂上发不出去。。
#用一条sql语句查询每门课成绩都大于80分的学生
-- 分组查询
SELECT sname,MIN(score) 最小成绩 FROM tb_lemon_score
GROUP BY sname HAVING MIN(score > 80);
-- 非关联子查询
SELECT * FROM tb_lemon_score WHERE sname NOT IN
(SELECT sname FROM tb_lemon_score WHERE score <= 80);
-- 关联子查询
SELECT * FROM tb_lemon_score t1 WHERE NOT EXISTS
(SELECT * FROM tb_lemon_score t2 WHERE t1.sname=t2.sname and t2.score<=80);
#用sql语句查询所有购入商品为两种或两种以上的购物人
-- 非关联子查询
SELECT sname FROM (SELECT sname,COUNT(merch) FROM shopping_list GROUP BY sname HAVING COUNT(merch)>=2) as list;
-- 关联子查询
SELECT DISTINCT sname FROM shopping_list s1 WHERE EXISTS
(SELECT * FROM shopping_list s2 WHERE s2.sname=s1.sname GROUP BY s2.sname HAVING COUNT(s2.merch)>=2)
欢迎来到testingpai.com！
注册 关于