字节跳动SQL题目
字节跳动SQL面经，求大佬给题解
给出一个表T，有三列，分别为SNO，SUBJECT，SCORE
SNO SUBJECT SCORE
1001 语文 90
1001 数学 40
1002 语文 80
请写出这样的一个SQL，查询出语文成绩及格，数学成绩不及格的学生的SNO
2021-03-05 14:17 已编辑 Harvard University Java
关注
全部评论
kevin_lai
越秀地产_数智发展中心_数据分析师
这种题目的话我觉得面试官一般是想考我们行列转换的内容，我面了一些都要考这个考点，所以我们可以先进行行列转换，再筛选就清晰很多了 select SNO from ( select SNO, case SUBJECT when "语文" then SCORE end as chinese_score, case SUBJECT when "数学" then SCORE end as maths_score from table1 ) where chinese_score >= 60 and maths_score <60
小小
浙江大学 Java
https://www.nowcoder.com/ta/sql
杭椒牛柳
New York University Java
select unique SNO from T where subject = '数学&(8804)#39; and score < 60 intersect select SNO from T where subject = '语文' and score >= 60;
lidou
门头沟学院 Java
select a.sno from (select sno from t where subject='语文&(8803)#39; and score>=60) a inner join (select sno from t where subject='数学&(8804)#39; and score<60) b on a.sno=b.sno
牛客匿名用户202212
西南交通大学 算法工程师
select SNO from (select SNO, sum(case subject when'语文&(8803)#39; then score else null end) as chinese_score, sum(case subject when'数学&(8804)#39; then score else null end) as math_score from t group by SNO ) as tt where tt.chinese_score>=60 and tt.math_score<60 ===================================== 如果是case subject ……else 0的话结果就会多了一个1002
Blue723Blue
Wake Forest University 产品经理
Select a.sno from (Select sno from t Where subject = '语文&(8803)#39; and score >= 60) a Inner join (select sno from t Where subject = '数学&(8804)#39; and score < 60) b On a.sno = b.sno
牛客690588823号
南昌大学 数据分析师
SELECT SNO FROM T WHERE SUBJECT='语文&(8803)#39; AND SCORE>=60 AND SNO IN (SELECT SNO FROM T WHERE SUBJECT='数学&(8804)#39; AND SCORE<60)
王小意
山东大学 数据分析师
select y.SNO from ( select * from T where subject='语文&(8803)#39; ) y join ( select * from T where subject='数学&(8804)#39; ) s on y.SNO =s.SNO where y.score>=60 and s.score <60; 这样可以吗？
select SNO from T where (SUBJECT='语文&(8803)#39; and SCORE>=60) and (SUBJECT='数学&(8804)#39; and SCORE<60)
牛客709873233号
河北工业大学 数据分析师
select sno from T group by sno having sum(case subject when '语文&(8803)#39; then score else null end) >= 60 and sum(case subject when '数学&(8804)#39; then score else null end) < 60
牛客709873233号
河北工业大学 数据分析师
select a.sno from (select * from sqltest where subject = '语文&(8803)#39; and score >= 60) as a inner join (select * from sqltest where subject = '数学&(8804)#39; and score <60) as b on a.sno = b.sno select a.sno from (select sno, sum(case subject when '语文&(8803)#39; then score else null end) as chinese_score, sum(case subject when '数学&(8804)#39; then score else null end) as match_score from sqltest group by sno) as a where a.chinese_score >= 60 and a.match_score < 60
数据女工
暨南大学 数据分析师
select distinct SNO from T where SNO in (select SNO from T where subject='语文&(8803)#39; and score>=60) and SNO in (select SNO from T where subject='数学&(8804)#39; and score<60)
ChenxiLiu
复旦大学 算法工程师
select stu_id, sum(case course when "语文" then SCORE else 0 end) as chinese_score, sum(case course when "数学" then SCORE else 0 end) as maths_score from exam group by stu_id having chinese_score >= 60 and maths_score <60
ChenxiLiu
复旦大学 算法工程师
select stu_id from exam where course = '语文&(8803)#39; and score >= 60 and stu_id in ( select stu_id from exam where course = '数学&(8804)#39; and score < 60)
superapril
The University of Leeds 产品经理
select SNO from T where subject='语文&(8803)#39; and score >=60 union slect SNO from T where subject='数学' and score <60; 这样可以嘛？
相关推荐
查看24道真题和解析 点赞 评论 收藏
分享
08-20 12:50
西北工业大学 Java 点赞 评论 收藏
分享