牛客sql刷题

一、非技术快速入门

https://www.nowcoder.com/exam/oj?page=1&tab=SQL篇&topicId=199

易错题目：

• 19、22、26、30

题目记录：

SQL13 Where in 和Not in

• 代码：

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

分析：in要紧挨着括号。

SQL19 分组过滤练习题

• 代码：

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

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

SQL22 统计每个学校的答过题的用户的平均答题数

• 代码：

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

分析：答过题的用户--关联的时候确保question表完整。

SQL23 统计每个学校各难度的用户平均刷题数

• 代码：

-- 分析表字段和各个表的关系
select 
    university,
    difficult_level,
    round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpd

left join user_profile as up
on up.device_id=qpd.device_id

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

group by university, difficult_level

分析：关联两个表的表作为主表，用户ID去重

SQL24 统计每个用户的平均刷题数

• 代码：

# select 
#     university,
#     difficult_level,
#     round(count(qpd.question_id) / count(distinct qpd.device_id), 4) 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 university, difficult_level 

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;

分析：用户要去重

SQL25 查找山东大学或性别为男生的信息

• 题目：

• 结果：

• 代码：

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'

分析:因为结果不去重，所以用两个表分别筛选完，再进行union all。

SQL26 计算25岁以上和以下的用户数量

• 题目：

• 结果:

• 代码：

SELECT CASE WHEN age < 25 OR age IS NULL THEN '25岁以下' 
            WHEN age >= 25 THEN '25岁及以上'
            END age_cut,COUNT(device_id) number
FROM user_profile
GROUP BY age_cut

注意：可以对这个case when生成的字段内部的内容，进行计数。

SQL27 查看不同年龄段的用户明细

• 题目:

• 结果：

• 代码：

SELECT 
    device_id,
    gender,
    CASE 
        WHEN age<20 THEN '20岁以下'
        WHEN age>=20 and age<=24 THEN '20-24岁'
        WHEN age>=25 THEN '25岁及以上'
        ELSE '其他'
    END age_cut
FROM user_profile

注意：重新赋值，

case
    when 条件表达式 then 赋值内容
	when 条件表达式 then 赋值内容
	else 赋值内容
end 别名

SQL28 计算用户8月每天的练题数量

• 题目：

• 结果：

• 代码：

select day(date) as day,count(question_id) as question_cut
from question_practice_detail
where date like '2021-08%'
group by date

注意：day(date),month(date),year(date)分别获取天，月，年

SQL30 统计每种性别的人数

• 题目：
  

• 结果：
  

• 代码：

# SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number
# FROM user_submit
# GROUP BY gender;

select if(substring_index(profile,',',-1)='male','male','female') gender,count(*) number
from user_submit
group by gender

注意：if语句，判断后重新赋值。

SQL32 截取出年龄

• 题目：

• 结果：

• 代码

select
    substring_index(substring_index(profile, ',', 3), ',', -1) as age,
    count(device_id) as number
from user_submit
group by age

注意：substring_index(FIELD, sep, n)可以将字段FIELD按照sep分隔：
(1).当n大于0时取第n个分隔符(n从1开始)之后的全部内容；
(2).当n小于0时取倒数第n个分隔符(n从-1开始)之前的全部内容；
因此，本题可以先用substring_index(profile, ',', 3)取出"180cm,75kg,27"，然后用substring_index(profile, ',', -1)取出27。
当然也可以用substring_index(substring_index(profile, ",", -2), ",", 1)取出27。

SQL33 找出每个学校GPA最低的同学

• 题目：

• 结果：

• 代码：

# # 学校和gpa必须是一对一的关系
select device_id,university,gpa
from user_profile
where (university,gpa) in (select university,min(gpa) from user_profile group by university )#min可以在select的时候就实现
order by university

注意：间接筛选，进行表嵌套

SQL34 统计复旦用户8月练题情况

• 题目
  

• 结果
  

• 代码：

SELECT 
up.device_id,
up.university,
count(qpd.question_id) question_cnt,
#计算做对题目个数
sum(if(qpd.result='right',1,0)) right_question_cnt
FROM user_profile up
left join question_practice_detail qpd on up.device_id=qpd.device_id and qpd.date like '2021-08%'
where up.university='复旦大学'
group by up.device_id  #有按照分组计算的应该要进行分组

问题：如何确定那个表是主表？--看题目中的查询结果是以哪个表为准：因为要未做过题的用户，所以要用user表为主，如果以question表为主的话，那就只有做过的题的用户了。

SQL35 浙大不同难度题目的正确率

• 题目：

• 结果：

• 代码：

select difficult_level,
    avg(if(qpd.result='right', 1, 0)) as correct_rate
#    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
#    count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from user_profile as up

inner join question_practice_detail as qpd
    on up.device_id = qpd.device_id

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

where up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate asc;

# 有三个表时，一个表的字段连接其他两个表，就用这个表当主表
SELECT 
      qd.difficult_level,
      sum(if(qpd.result='right',1,0))/count(qpd.question_id) correct_rate
FROM question_practice_detail as qpd 
left join user_profile as up on up.device_id=qpd.device_id
left join question_detail as qd on qd.question_id=qpd.question_id
where up.university='浙江大学'
group by qd.difficult_level
order by correct_rate asc

试题分析：1.先写select的部分；2.看筛选的字段与表中哪些字段相关，如果表与表之间有字段相等的关系，就使用关联；3.在where\group by\order by的部分调用各自表信息的限定条件。

注意：多张表联合查询：需要用到join，join有多种语法，因为条件限定需要是浙江大学的用户，所以需要是user_profile表的并且能统计出题目难度的记录，因此用user_profile表inner join另外两张表。

SQL39 21年8月份练题总数

• 题目：

• 结果：

• 代码：

select
     count(distinct device_id) as did_cnt,
     count(question_id) as question_cnt
from question_practice_detail
where date like '2021-08%'

注意：对用户需要进行去重