sql题
云养猫v2
这个作者很懒,什么都没留下…
展开
-
行转列 列传行
转成叫做行转列:select name,sum(case when course='语文' then score else 0 end) as '语文',sum(case when course='数学' then score else 0 end) as '数学',sum(case when course='英语' then score else 0 end) as '英语'from student_scoregroup by name反过来叫行转列:select name,.原创 2020-08-21 11:14:38 · 176 阅读 · 0 评论 -
查询各科成绩前三名
select a.course, a.score, a.name from student_score awhere (select count(*) from student_score where course = a.course and score > a.score) < 3order by course, score desc;原创 2020-08-20 13:52:32 · 934 阅读 · 0 评论 -
sql语句执行, having
having是分组之后,筛选符合条件的组。不是组内筛选。FROM <left_table>ON <join_condition><join_type> JOIN <right_table>WHERE <where_condition>GROUP BY <group_by_list>HAVING <having_condition>SELECTDISTINCT <select_list>ORD.原创 2020-08-20 09:28:41 · 148 阅读 · 0 评论 -
统计每天每个直播间的访客数、每天最大访客数的直播间
1、统计每天每个直播间的访客数、每天最大访客数的直播间2、查找至少连续观看3天的用户ID 及出现直播间tableLiveID UserID Date1 1 202006011 2 202006011 3 202006011 1 202006021 1 202006031 2 202006031 2 202006042 4 20200601select dateId, liveId, count(userId) from live group by dateId, liveId;原创 2020-08-19 22:00:30 · 1125 阅读 · 0 评论 -
表student_score 字段:name, course, score 求 每个人考的最好的一门,每科考的最好的同学
select a.course,a.namefrom student_score a join (select max(score) max_score, name from student_score group by name) bon a.name = b.name and a.score = b.max_score;原创 2020-08-19 18:57:49 · 549 阅读 · 0 评论 -
student(id, sex, name, class_id)和class(id, name),写一个SQL查询女同学人数最多的班级的名称
select c.name from class c join student s on c.id = s.class_id where s.sex = 1 group by s.class_id order by count(s.id) desc limit 1;原创 2020-08-12 14:15:13 · 1336 阅读 · 0 评论 -
name subject score,找出所有科目分数大于60的人
SELECT a.name FROM (select name, count(*) num1 from stu where score > 60 group by name ) ainner join(select name, count(*) num1 from stu group by name ) bwhere a.name = b.name and a.num1 = b.num1;原创 2020-08-12 14:08:55 · 613 阅读 · 0 评论