【SQL笔试】牛客网32道SQL常见笔试题集锦包含SQL试题常见考点——建议保存(^_^)

最新推荐文章于 2024-07-09 15:20:37 发布

量化Mike

最新推荐文章于 2024-07-09 15:20:37 发布

阅读量1.4k

点赞数 7

分类专栏： Python 基础(完整版） LeeCode 文章标签： sql 数据库 database 牛客刷题

本文链接：https://blog.csdn.net/weixin_43529465/article/details/124911379

版权

Python 基础(完整版）同时被 2 个专栏收录

37 篇文章 10 订阅

订阅专栏

LeeCode

4 篇文章 0 订阅

订阅专栏

文章目录

基本表1中数据
基本表2中数据
基本表3中数据
基本表4中数据
多表查询之子查询
- 24、题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据
多表查询之链接查询
多表查询之组合查询
- 28、题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据。
必会的常用函数之条件函数
- 29、题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量
- 30、题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）
必会的常用函数之日期函数
- 31、题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。
- 32、题目：【计算用户的平均次日留存率】现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

基本表1中数据

在这里插入图片描述

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

1|2138|male|21|北京大学|BeiJing
2|3214|male|None|复旦大学|Shanghai
3|6543|female|20|北京大学|BeiJing
4|2315|female|23|浙江大学|ZheJiang
5|5432|male|25|山东大学|Shandong

1、题目：现在运营想要查看用户信息表中所有的数据，请你取出相应结果

select * from user_profile

在这里插入图片描述

2、题目：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据

select device_id,gender,age,university from user_profile

3、题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。

select distinct university from user_profile

关键字 distinct 用于去重操作

4、题目：现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。

select device_id from user_profile where id = 1 or id = 2

SELECT  device_id FROM user_profile ORDER BY id LIMIT 2

例5.检索记录行 6-10
SELECT * FROM table LIMIT 5,5

例6.检索记录行 11-last
SELECT * FROM table LIMIT 10,-1

例7.检索前 5 个记录行
SELECT * FROM table LIMIT 5

5、题目：现在你需要查看前2个用户明细设备ID数据，并将列名改为 ‘user_infos_example’,，请你从用户信息表取出相应结果。

select device_id as user_infos_example from user_profile limit 2

可以省略掉as
select device_id user_infos_example from user_profile limit 2

6、题目：现在运营想要取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序。

select device_id, age from user_profile order by age

结尾加 ASC是升序，不写也行因为默认升序。结尾加 desc 是降序。

7、题目：现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。

SELECT device_id,gpa,age from user_profile order by gpa,age;默认以升序排列

SELECT device_id,gpa,age from user_profile order by gpa,age asc;

SELECT device_id,gpa,age from user_profile order by gpa asc,age asc;

8、题目：现在运营想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据。

SELECT device_id,gpa,age from user_profile order by gpa desc,age desc;

9、题目：现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。

在这里插入图片描述

select device_id , university from user_profile where university = '北京大学'

10、题目：现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。

在这里插入图片描述

select device_id,gender,age,university from user_profile where age > 24

11、题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄。

在这里插入图片描述

select device_id,gender,age from user_profile where age >= 20 and age <= 23

12、题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据

在这里插入图片描述

select device_id,gender,age,university from user_profile where university <> '复旦大学'

select device_id,gender,age,university from user_profile where university != '复旦大学'

select device_id,gender,age,university from user_profile where university NOT IN ("复旦大学")

13、题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。

在这里插入图片描述

select device_id,gender,age,university from user_profile where age != '';

select device_id,gender,age,university from user_profile where age is not NULL;

14、题目：现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据。

在这里插入图片描述

select device_id,gender,age,university,gpa from user_profile where gender = 'male' and gpa > 3.5

15、题目：现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据（使用OR实现）

在这里插入图片描述

select device_id,gender,age,university,gpa from user_profile 
where university = '北京大学' or gpa > 3.7

16、题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。

在这里插入图片描述

select device_id,gender,age,university,gpa from user_profile 
where university in ("北京大学","复旦大学","山东大学")

17、题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

在这里插入图片描述

select device_id,gender,age,university,gpa from user_profile 
where (university = '山东大学' and gpa > 3.5) OR
(university = '复旦大学' and gpa > 3.8)

基本表2中数据

在这里插入图片描述

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);

18、题目：现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。

使用的是表2
在这里插入图片描述
一般形式为：列名 [NOT ] LIKE
匹配串中可包含如下四种通配符：
_：匹配任意一个字符；
%：匹配0个或多个字符；
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表达 )；
[^ ]：不匹配[ ]中的任意一个字符。

查询学生表中姓‘张’的学生的详细信息。
SELECT * FROM 学生表 WHERE 姓名 LIKE ‘张%’

查询姓“张”且名字是3个字的学生姓名。
SELECT * FROM 学生表 WHERE 姓名 LIKE '张__‘

用rtrim()去掉右空格
SELECT * FROM 学生表 WHERE rtrim(姓名) LIKE '张__'

查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。
SELECT * FROM 学生表 WHERE 姓名 LIKE '[张李刘]%’

查询学生表表中名字的第2个字为“小”或“大”的学生的姓名和学号。
SELECT 姓名,学号 FROM 学生表 WHERE 姓名 LIKE '_[小大]%'

查询学生表中所有不姓“刘”的学生。
SELECT 姓名 FROM 学生 WHERE 姓名 NOT LIKE '刘%’

从学生表表中查询学号的最后一位不是2、3、5的学生信息。
SELECT * FROM 学生表 WHERE 学号 LIKE '%[^235]'

解答结果：

select device_id,age,university from user_profile 
where university like "北京%"

19、题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据

使用的是表1
在这里插入图片描述

select max(gpa) from user_profile where university = '复旦大学'

20、题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。

在这里插入图片描述

select count(gender),avg(gpa) from user_profile where gender = 'male'

基本表3中数据

在这里插入图片描述

21、题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

在这里插入图片描述
分组语句的一般形式：
[GROUP BY ]
[HAVING ]
GROUP BY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的结果集列的别名。
带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列或统计函数，因为分组后每个组只返回一行结果。

1、统计每门课程的选课人数，列出课程号和人数。
SELECT 课程号, COUNT(课程号) AS 选课人数 FROM 成绩表 GROUP BY 课程号
该语句首先对查询结果按课程号的值分组，所有具有相同课程号值的元组归为一组，然后再对每一组使用COUNT函数进行计算，求得每组的学生人数。

2、查询每名学生的选课门数和平均成绩。
SELECT 学号, COUNT(*) 选课门数, AVG(成绩) 平均成绩FROM 成绩表 GROUP BY 学号

在这里插入图片描述

解答结果：

select gender , university , count(gender) as user_num , 
avg(active_days_within_30) as avg_active_day, avg(question_cnt) as avg_question_cnt from user_profile
group by gender,university

22、题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

在这里插入图片描述
限定条件：

平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university。

注意顺序：
having语句的位置与 group by语句的顺序。

select university,avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt from user_profile group by university
having avg(question_cnt) < 5 or avg(answer_cnt) < 20

23、题目：现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。

在这里插入图片描述
问题分解：
限定条件：无；
不同大学：按学校分组group by university
平均发帖数：avg(question_cnt)
升序排序：order by avg_question_cnt

select university, avg(question_cnt) as avg_question_cnt 
from user_profile 
group by university 
order by avg(question_cnt) asc

基本表4中数据

表question_practice_detail
在这里插入图片描述
表 user_profile

多表查询之子查询

24、题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

在这里插入图片描述
若一个查询同时涉及两个或两个以上的表，则称之为连接查询。

连接查询是关系数据库中最主要的查询。
连接查询包括内连接、外连接和交叉连接等。
连接查询中用于连接两个表的条件称为连接条件或连接谓词。

内连接语法如下：
SELECT 字段名 FROM 表名 [INNER] JOIN 被连接表 ON 连接条件

1、查询每个学生及其班级的详细信息。
SELECT * FROM 学生表 INNER JOIN 班级表 ON 学生表.班号=班级表.班号
结果中有重复的列：班号。

2、去掉上面例中的重复列。
SELECT 学号,姓名,班级表.班号,班名 FROM 学生表 JOIN 班级表 ON 学生表.班号=班级表.班号

3、查询重修学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。
SELECT 姓名, 课程号, 成绩 FROM 学生表 JOIN 成绩表 ON 学生表.学号 = 成绩表.学号 WHERE 状态 = '重修'

执行连接操作的过程，首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，
找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。 表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2,...重复这个过程，直到表1中的全部元组都处理完毕为止。

设置表别名
可以为表提供别名，其格式如下：
<源表名> [ AS ] <表别名>

4、使用别名时例3可写为如下形式：
SELECT 姓名, 课程号, 成绩 FROM 学生表 S JOIN 成绩表 g ON S.学号 = g.学号 WHERE 状态 = ‘重修’
注：如果为表指定了别名，则查询语句中其他所有用到表名的地方都要使用别名

5、查询所有学生的姓名、班名和系名。
SELECT 姓名,班名,系名 FROM 学生表 s 
JOIN 班级表 bjb ON s.班号 = bjb.班号 
JOIN 系表 xb ON bjb.系号 = xb.系号

6、查询软件工程系所有学生的情况，要求列出学生姓名和所在的系。
SELECT 姓名, 系名 FROM 学生表 s JOIN 班级表 bjb ON s.班号 = bjb.班号
JOIN 系表 xb ON bjb.系号 = xb.系号 WHERE 系名= '软件工程系

7、有分组的多表连接查询。查询每个班的学生的考试平均成绩。
SELECT 班号, AVG(成绩) AS 班平均 FROM 学生表 S JOIN 成绩表 g ON S.学号 = g.学号 GROUP BY 班号

8、有分组和行过滤的多表连接查询。查询21226P班每门课程的选课人数、平均成绩、最高成绩和最低成绩。
SELECT 课程号, COUNT(*) AS Total,
AVG(成绩) AS Avg成绩,
MAX(成绩) AS Max成绩,
MIN(成绩) AS Min成绩
FROM 学生表 S JOIN 成绩表 g
ON S.学号 = g.学号
WHERE 班号 = '21226P'
GROUP BY 课程号

方法1：
select ta.device_id, ta.question_id, ta.result
from question_practice_detail as ta, user_profile as tb
where tb.university = '浙江大学' and tb.device_id = ta.device_id

方法2：
select ta.device_id, question_id, result
from question_practice_detail ta
join user_profile tb
on tb.device_id = ta.device_id
where tb.university = '浙江大学'
order by question_id asc

方法3:
select ta.device_id, question_id, result
from question_practice_detail ta
join user_profile tb
on tb.device_id = ta.device_id and tb.university = '浙江大学'
order by question_id asc

多表查询之链接查询

25、题目：运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

在这里插入图片描述

写法一：
select university, (count(university)/ count(distinct ta.device_id)) avg_answer_cnt 
from user_profile ta
join question_practice_detail tb 
on ta.device_id = tb.device_id
group by university
order by university asc

写法二：
select university,
    count(question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id=up.device_id
group by university

写法三：
SELECT ue.university,
       COUNT(ql.question_id)/count(distinct ql.device_id) as avg_answer_cnt
FROM question_practice_detail as ql JOIN user_profile as ue
ON ql.device_id=ue.device_id
GROUP BY ue.university;

26、题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据。

请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：
在这里插入图片描述
在合并表格的过程中，存在几种合并方式，下面简单介绍一下这些方式（此处以两张表格的情况为例解释）：
( 合并的过程中，第一张表格在最左边，之后的饿表格依次添加在右边 )

inner join 最终结果为在两张表格中都匹配上的数据项, 只写一个join 时默认为inner join模式
left join 最终结果为inner join结果加上左侧表格（此处为第一张表）未匹配上的数据
right join 最终结果为inner join结果加上右侧表格（此处为第一张表）未匹配上的数据
full join 最终结果为inner join加上左侧和右侧两张表中未匹配上的数据

解题方法一：
# 第一段：select选择字段，含原生字段及构造字段，其中构造的过程中常用sum count round +-*/ 等函数及运算表达式
select
    university,
    difficult_level,
    round(count(qpd.question_id) / count(distinct qpd.device_id),4) as avg_answer_cnt

# 第二段：from选择表格，含合并表格操作
from question_practice_detail as qpd
    join user_profile as up
        on up.device_id=qpd.device_id
    join question_detail as qd
        on qd.question_id=qpd.question_id

# 第三段：数据操作段，含 分组/排序/筛选/取指定行数等操作
group by university,difficult_level;

27、题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据。

请你写一个SQL查询，计算山东、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：
在这里插入图片描述

解题方法1：
select  ta.university, 
        tc.difficult_level, 
        round(count(tb.question_id)/ count(distinct tb.device_id),4) as avg_answer_cnt
    from question_detail as tc
    join question_practice_detail as tb
        on tc.question_id = tb.question_id 
    join user_profile as ta
        on ta.device_id = tb.device_id and ta.university = "山东大学"
    group by university, difficult_level

解题方法2：
select 
    t1.university,
    t3.difficult_level,
    count(t2.question_id) / count(distinct t2.device_id ) as avg_answer_cnt
from 
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
where 
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
group by
    t3.difficult_level;

解题方法3：
select 
    "山东大学" as university,
    difficult_level,
    count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from question_practice_detail as qpd

inner join user_profile as up
on up.device_id=qpd.device_id and up.university="山东大学"

inner join question_detail as qd
on qd.question_id=qpd.question_id

group by difficult_level

解题方法4：
select  ta.university, 
        tc.difficult_level, 
        round(count(tb.question_id)/ count(distinct tb.device_id),4) as avg_answer_cnt
    from question_detail as tc 
    join question_practice_detail as tb
        on tc.question_id = tb.question_id  
    join user_profile as ta
        on ta.device_id = tb.device_id 
    where ta.university = "山东大学"
    group by university, difficult_level

多表查询之组合查询

28、题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据。

根据示例，你的查询应返回以下结果（注意输出的顺序，先输出学校为山东大学再输出性别为男生的信息）：
在这里插入图片描述
UNION（并）
使用 UNION可以实现将多个查询结果集合并为一个结果集。

所有查询语句中列的个数和列的顺序必须相同。
所有查询语句中对应列的数据类型必须兼容。
ORDER BY语句要放在最后一个查询语句的后边。

1、查询系号是1和2的班级的班号、班名、系号，系号是1 的记录在前，2在后。
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 1
UNION
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 2

等价于：
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号 IN (1,2) ORDER BY 系号

2、查询要求同例46，但将查询结果按系号从大到小排序。
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 1
UNION
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 2
ORDER BY 系号 DESC

解题答案：

select device_id, gender, age, gpa from user_profile
where university = "山东大学"
union all
select device_id, gender, age, gpa from user_profile
where gender = "male"

必会的常用函数之条件函数

29、题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

在这里插入图片描述
CASE函数 是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。
可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。
分为：简单CASE函数、搜索CASE函数

1、简单CASE函数：《《《《《《《——————————》》》》》》》》

CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。
如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。
如果所有简单表达式的值与测试表达式的值都不相等，若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；
若没有指定ELSE子句，则返回NULL。

1、查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：
当系号为1时，显示 “计算机系”；
当系号为2时，显示 “软件工程系”；
当系号为3时，显示 “物联网系”。

SELECT 班号 ,班名,
CASE 系号
WHEN 1 THEN '软件工程系'
WHEN 2 THEN '计算机系'
WHEN 3 THEN '物联网系'
END 
AS 系号,班主任号
FROM 班级表

2、搜索CASE函数：《《《《《《《——————————》》》》》》》》

CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2 …
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

按从上到下的书写顺序计算每个WHEN子句的布尔表达式。
返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。
如果没有取值为TRUE的布尔表达式，则当指定了ELSE子句时,返回ELSE子句中指定的结果；
如果没有指定ELSE子句，则返回NULL。

2、用搜索CASE来做：

SELECT 班号 ,班名,
CASE
WHEN 系号=1 THEN '软件工程系'
WHEN 系号=2 THEN '计算机系'
WHEN 系号=3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

3、查询“M01F011”号课程的考试情况，列出学号、课程号和成绩，同时将百分制成绩显示为等级。

SELECT 学号,课程号,
CASE
WHEN 成绩 >= 90 THEN '优'
WHEN 成绩 BETWEEN 80 AND 89 THEN '良'
WHEN 成绩 BETWEEN 70 AND 79 THEN '中'
WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'
WHEN 成绩 <60 THEN '不及格'
END 成绩
FROM 成绩表
WHERE 课程号 = 'M01F011'

4、统计每个班男生和女生的数量各是多少，统计结果的表头为，班号，男生数量，女生数量。
SELECT 班号,
COUNT(CASE WHEN 性别=‘男’ THEN ‘男’ END) 男生数,
COUNT(CASE WHEN 性别=‘女’ THEN ‘女’ END) 女生数
FROM 学生表 GROUP BY 班号

5、判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。
SELECT
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END 等级, COUNT(*) 人数
FROM SC
GROUP BY
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END

解决方案

select case when age < 25 or age is null then '25岁以下'
            when age >= 25 then '25岁及以上'
            end age_cut, count(*) number
from user_profile
group by age_cut

30、题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）

在这里插入图片描述

select device_id,gender,
case 
when age < 20 then '20岁以下'
when age >= 20 and age <= 24 then '20-24岁'
when age >= 25 then '25岁及以上'
when age is null then '其他'
end age_cut
from user_profile

必会的常用函数之日期函数

31、题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

在这里插入图片描述
日期函数：

1、DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。
mysql> select DAYOFWEEK('1998-02-03')
-> 3

2、WEEKDAY(date)
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
mysql> select WEEKDAY('1997-10-04 22:23:00');
-> 5

3、DAYOFMONTH(date)
返回date的月份中日期，在1到31范围内。
mysql> select DAYOFMONTH('1998-02-03');
-> 3

4、DAYOFYEAR(date)
返回date在一年中的日数, 在1到366范围内。
mysql> select DAYOFYEAR('1998-02-03');
-> 34

5、MONTH(date)
返回date的月份，范围1到12。
mysql> select MONTH('1998-02-03');
-> 2

6、DAYNAME(date)
返回date的星期名字。
mysql> select DAYNAME("1998-02-05");
-> 'Thursday'

7、MONTHNAME(date)
返回date的月份名字。
mysql> select MONTHNAME("1998-02-05");
-> 'February'

8、QUARTER(date)
返回date一年中的季度，范围1到4。
mysql> select QUARTER('98-04-01');
-> 2

9、WEEK(date)
对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。
mysql> select WEEK('1998-02-20');
-> 7

10、WEEK(date,first)
2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。
如果第二个参数是0，星期从星期天开始，
如果第二个参数是1，从星期一开始。
mysql> select WEEK('1998-02-20',0);
-> 7
mysql> select WEEK('1998-02-20',1);
-> 8

11、YEAR(date)
返回date的年份，范围在1000到9999。
mysql> select YEAR('98-02-03');
-> 1998

12、HOUR(time)
返回time的小时，范围是0到23。
mysql> select HOUR('10:05:03');
-> 10

13、MINUTE(time)
返回time的分钟，范围是0到59。
mysql> select MINUTE('98-02-03 10:05:03');
-> 5

14、SECOND(time)
回来time的秒数，范围是0到59。
mysql> select SECOND('10:05:03');
-> 3

15、PERIOD_ADD(P,N)
增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。
mysql> select PERIOD_ADD(9801,2);
-> 199803

16、PERIOD_DIFF(P1,P2)
返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。
mysql> select PERIOD_DIFF(9802,199703);
-> 11

解题结果：

select DAY(date) day, count(question_id) question_cnt
from question_practice_detail 
where MONTH(date) = "8" and YEAR(date) = "2021"
group by day

32、题目：【计算用户的平均次日留存率】现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

在这里插入图片描述


解决方法1:
select count(date2) / count(date1) as avg_ret
from (
    select
        distinct qpd.device_id,
        qpd.date as date1,
        uniq_id_date.date as date2
    from question_practice_detail as qpd
    left join(
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
    on qpd.device_id=uniq_id_date.device_id
        and date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_date


解决方法2:
select avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (
    select
        distinct device_id,
        date as date1,
        lead(date) over (partition by device_id order by date) as date2
    from (
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
) as id_last_next_date

量化Mike

关注

7
点赞
踩
16

收藏

觉得还不错? 一键收藏
2
评论
【SQL笔试】牛客网32道SQL常见笔试题集锦包含SQL试题常见考点——建议保存(*^_^*)

文章目录基本表1中数据1、题目：现在运营想要查看用户信息表中所有的数据，请你取出相应结果2、题目：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据3、题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。4、题目：现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。5、题目：现在你需要查看前2个用户明细设备ID数据，并将列名改为 'user_infos_example',，请你从用户信息表取出相应结果。
复制链接

扫一扫