SQL刷题2 | 牛客网

SQL21 浙江大学用户题目回答情况

SQL21 浙江大学用户题目回答情况
1、INNER JOIN内连接
语法：

SELECT …
FROM 表名
[INNER] JOIN 被连接表
ON 连接条件

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

select q.device_id,question_id,result
from question_practice_detail  q
join user_profile u
on q.device_id = u.device_id
where u.university = '浙江大学'

2、等值连接

select
    q.device_id,
    question_id,
    result
from
    question_practice_detail q,
    user_profile u
where
    u.university = '浙江大学'
    and q.device_id = u.device_id

3、子查询

select
    device_id,
    question_id,
    result
from
    question_practice_detail
where
    device_id in (
        select device_id
        from user_profile
        where university = '浙江大学'
    )

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

SQL22 统计每个学校的答过题的用户的平均答题数
1、按学校分组group by university
2、平均答题数量：在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量
3、表连接：学校和答题信息在不同的表，需要做连接

select
    university,
    count(question_id) / count(distinct q.device_id) as avg_answer_cnt
from
    question_practice_detail as q
    inner join user_profile as u on q.device_id = u.device_id
group by
    university

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

SQL23 统计每个学校各难度的用户平均刷题数
1、按学校、难度分组group by university,difficult_level
2、平均答题数：总答题数除以总人数
3、连接三个表，u与qpd用device_id连接，qd与qpd用question_id连接

select
    university,
    difficult_level,
    round(
        count(qpd.question_id) / count(distinct qpd.device_id),
        4
    ) as avg_answer_cnt
from
    question_practice_detail qpd
    left join user_profile u on qpd.device_id = u.device_id
    left join question_detail qd on qpd.question_id = qd.question_id
group by
    university,
    difficult_level

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

SQL24 统计每个用户的平均刷题数
1、限定条件：山东大学 up.university=‘山东大学’
2、按难度分组：group by difficult_level
3、平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)
4、连接三个表

select
    up.university,
    qd.difficult_level,
    count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from 
    question_practice_detail qpd`在这里插入代码片`
join 
    question_detail qd
on qpd.question_id = qd.question_id
join
    user_profile up
on qpd.device_id = up.device_id and up.university = '山东大学'
group by
    difficult_level

或

select
    up.university,
    qd.difficult_level,
    count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from
    question_practice_detail qpd,
    question_detail qd,
    user_profile up
where
    up.university = '山东大学' 
    and qpd.device_id = up.device_id
    and qpd.question_id = qd.question_id
group by
    qd.difficult_level

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

SQL25 查找山东大学或者性别为男生的信息
1、限定条件：山东大学或男性
2、分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重

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'

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

SQL26 计算25岁以上和以下的用户数量
CASE函数：一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。
分为：
1、简单CASE函数
语法：

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

2、搜索CASE函数
语法：

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

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

if判断

select
    if(age>=25,'25岁及以上','25岁以下') age_cut,
    count(*) number
from 
    user_profile
group by 
    age_cut

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

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

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

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

SQL28 计算用户8月每天的练题数量
1、限定条件：2021年8月
year/month函数的year(date)=2021 and month(date)=8
或者date_format函数的date_format(date, “%Y-%m”)=“202108”
2、每天：按天分组group by date
3、题目数量：count(question_id)
YEAR(date)：返回指定日期的年份值
MONTH(date)：返回指定日期的月份数值
DAY(date)：返回指定日期的天数值

select
    day(date) day,
    count(question_id) question_cnt
from 
    question_practice_detail
where
    year(date)=2021 and month(date)=8
group by day