常见sql面试题解析分享/inner join/group by

目录

       

前言

原题目：

解析

建表语句

---

       

前言

        这是一篇常见sql面试题解析分享。这个题目非常常见，原题干中没有表格展示，对初学者和对sql不太熟练的同学来说很不友好。我自建了三个表格作为实操演练对象，为了方便大家，建表语句都放在后面了。我在实际解题中发现，网络上现在流传的第二问解答与题目实际上并没有完全对应上，我把自己的解答贴在后面了，希望能给面试的朋友一点帮助，也希望有更好解题方法的朋友可以分享出来。

 

         

原题目

学生表(学生id，姓名，性别，分数)student（s_id，name,sex,score）

班级表（班级id，班级名称）class（c_id，c_name）

学生班级表（班级id，学生id）student_class(s_id,c_id)

问题1：查询一班得分在80分以上的学生

问题2：查询所有班级的名称，和所有班中女生人数和女生的平均分

解析

问题1：查询一班得分在80分以上的学生

方法一：

select * from student where score>80 and s_id in (select s_id from student where name in (select c_name from class where c_id = 1))

方法二：

select student.name,student.score from student inner join student_class on student.s_id = student_class.s_id where student_class.c_id = 1 and student.score >80

问题2：查询所有班级的名称，和所有班中女生人数和女生的平均分

select c.c_id,count(s.sex),avg(score) from student as s

inner join class as c on s.name = c.c_name

inner join student_class as sc on s.s_id = sc.s_id

where s.sex = '女' group by c.c_id order by c.c_id;

运行后结果如下：

网上流转的答案：

select s.s_id, s.name, AVG(s.score), count(s.sex) from student s

left join student_class sc on s.s_id = sc.s_id

left join class c on sc.c_id = c.c_id

where (s.sex= '女') group by c.c_name order by c.c_id desc

运行后结果如下：

建表语句

需要用到的语句：

创建表：

create table tablename（column_name1 column_type_1 constraints, ... ,column_name_n column_type_n constraints)

插入记录：

insert into tablename （field1，field2....） values（value1,value2...）

插入多条记录：

insert into tablename （field1，field2....） values（value1,value2...）,（value1,value2...）

创建student表：

create table student (s_id int,name varchar(10),sex char(4),score double(5,2))

插入数据：

Insert into student (s_id,name,sex,score) values(1001,’王大锤’,’男’,80), (1002,'小李子','女',60), (1003,'王乐乐','男',23), (1004,'李旭奥','男',59), (1005,'刘毅力','男',98), (1006,'陈老鹅','男',45), (1007,'刘大美','女',55), (1008,'白净','男',98), (1009,'艺名','女',78), (1010,'恩爱','女',68)

创建class表：
create table class (c_id int,c_name varchar(10))

插入数据：

Insert into class (c_id,c_name) values(1,'王大锤'),(1,'小李子'),(1,'王乐乐'),(1,'李旭奥'),(1,'刘毅力'),(1,'陈老鹅'),(2,'刘大美'),(2,'白净'),(2,'艺名'),(2,'恩爱')

创建student_class表：

create table student_class (c_id int,c_name darchar(10))

插入数据：

Insert into student_class (c_id,c_name)

 values(1001,1),(1002,1),(1003,1),(1004,1),(1005,1),(1006,1),(1007,2),(1008,2),(1009,2),(1010,2)