MySQL精题易混题

秦矜

已于 2022-04-28 19:52:38 修改

阅读量1k

点赞数 1

分类专栏： MySQL 文章标签： mysql

于 2022-04-28 19:51:34 首次发布

本文链接：https://blog.csdn.net/hyf24/article/details/124460628

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

题目一（计算25岁以上和以下的用户数量）：

题目一（计算25岁以上和以下的用户数量）：

代码实现方法一：

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

代码实现方法二：

select
    if(age<25 or age is null,'25岁以下','25岁及以上') as age_cut,
    count(*) as number
from user_profile
group by age_cut;

MySQL中关键字的执行顺序：

       1. from
       2. where
       3. group by
       4. having
       5. select
       6. order by

在group by执行的时候，select还没执行，它怎么会知道age_cut是指什么呢？

因为MySQL对查询做了增强没有严格遵循SQL的执行顺序，where后面不能用select中的别名，但是group by ，order by都是可以的，Oracle数据库严格遵循了SQL执行顺序在Oracle里group by是不能引用select里的别名的。

count(具体字段)：表示统计分组后该字段下所有不为NULL的元素的总数。

count(*)：统计分组后一个组的总行数。（包含某一字段下为NULL的元素）

count函数中，重复的数据会多次计算，如果要去重，就得搭配 distinct 使用，即count(distinct xxxxxx)。

代码思路：

我们要按 '25以下' 和 '25及以上' 分组，因此就要先用if语句或case语句将用户划分为 '25以下' 和 '25及以上' 两个年龄段。

题目指路：计算25岁以上和以下的用户数量_牛客题霸_牛客网 (nowcoder.com)

题目二（查看不同年龄段的用户明细）：

代码实现方法：

select device_id,gender,
    case 
        when (age>=20 and age<=24)
            then '20-24岁' 
        when age>=25 
            then '25岁及以上'
        when age<20
            then '20及以下'
        else '其他' end age_cut
from user_profile;
# 这里没用到分组函数不需要分组 group by age_cut;

题目指路：查看不同年龄段的用户明细_牛客题霸_牛客网 (nowcoder.com)

题目三（计算用户的平均次日留存率）：

代码实现：

SELECT 
    COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret 
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1    # q1是包含全部成员的
LEFT JOIN
    (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2   # q2是符合on条件的
    # 如果不加lert，那么得到的全是满足on条件的数据
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day);

为什么要用左连接：

没用左连接：

mysql> SELECT
    ->     q1.device_id,q2.device_id     # COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
    -> FROM
    ->     (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1    # q1是包含全部成员的
    -> JOIN
    ->     (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2   # q2是符合on条件的
    ->     # 如果不加lert，那么得到的全是满足on条件的数据
    -> ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day);
+-----------+-----------+
| device_id | device_id |
+-----------+-----------+
|      2315 |      2315 |
|      2315 |      2315 |
|      3214 |      3214 |
+-----------+-----------+
3 rows in set (0.00 sec)

用了左连接：

mysql> SELECT
    ->     q1.device_id,q2.device_id     # COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
    -> FROM
    ->     (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1    # q1是包含全部成员的
    -> LEFT JOIN
    ->     (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2   # q2是符合on条件的
    ->     # 如果不加lert，那么得到的全是满足on条件的数据
    -> ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day);
+-----------+-----------+
| device_id | device_id |
+-----------+-----------+
|      2138 |      NULL |
|      3214 |      NULL |
|      3214 |      NULL |
|      6543 |      NULL |
|      2315 |      2315 |
|      2315 |      2315 |
|      2315 |      NULL |
|      3214 |      3214 |
|      3214 |      NULL |
|      3214 |      NULL |
+-----------+-----------+
10 rows in set (0.00 sec)

如果没用左连接的话，COUNT(q2.device_id) / COUNT(q1.device_id) 永远等于1。

题目指路：计算用户的平均次日留存率_牛客题霸_牛客网 (nowcoder.com)

题目四（统计每种性别的人数）：

代码实现方法一：

select case when profile like '%,male'  then 'male' when profile like '%,female'  then 'female' end gender
    ,count(*)
from user_submit
group by gender;

代码实现方法二：

select case substr(profile,15) when 'male'  then 'male' when 'female'  then 'female' end gender
    ,count(*)
from user_submit
group by gender;

代码实现方法三：

select substr(profile,15) gender,count(*) number
from user_submit
where substr(profile,15)='male'
union
select substr(profile,15) gender,count(*) number
from user_submit
where substr(profile,15)='female';

代码实现方法四：

select "male" as gender,count(profile) from user_submit where substr(profile,15)='male'
union
select "female" as gender,count(profile) from user_submit where substr(profile,15)='female';

题目指路：统计每种性别的人数_牛客题霸_牛客网 (nowcoder.com)

题目五（找出每个学校GPA最低的同学）：

错误的实现方法一：

select device_id,university,round(min(gpa),4) gpa
from user_profile
group by university;

错误，该SQL语句块左右列的长度不对。

按学校分类后，查询device_id显示的是该校所有学生的id

而按学校分类后，查询round(min(gpa),4)显示的仅仅是最小的那一个gpa

错误的实现方法二：

select device_id,university,gpa
from user_profile
where gpa in (select min(gpa) from user_profile group by university)
order by university;

在in子查询中没有加上university，如果不加university的话，

假如存在以下情况：

1237 浙江大学 3.600 （浙江大学最低）

1239 清华大学 3.200 （清华大学最低）

1240 清华大学 3.600

那么会输出两个清华大学，因为浙江大学最低是3.6而清华大学正好有3.6这个gpa。

代码实现方法一：

select device_id,university,gpa
from user_profile
where (university,gpa) in (select university,min(gpa) from user_profile group by university)
order by university;

这样先用where筛选出所有最小的gpa及其对应的同学再进行打印结果，则打印的只会是gpa最小的那个同学的id。

一定要加university，如果不加university的话，会输出两个清华大学！！！

in的基本语法是：

where value in (value1,value2,value3,…)

但除此之外，in后面的括号内还可以跟查询语句，如：

where (gpa) in (select min(gpa) from user_profile)

这被称为in的子查询。

in的子查询还可以查询多个语句，但多个语句必须一一对应，不能多，不能少，顺序不能换。

where (university,gpa) in (select university,min(gpa) from user_profile)

代码实现方法二：

select a.device_id,a.university,a.gpa 
from user_profile a
join (select university,min(gpa) gpa 
      from user_profile 
      group by university) b
on a.university=b.university and a.gpa=b.gpa
order by university;

使用内连接将分数最低人连接起来在一张表中，这样查询device_id查询的就是内循环得到的那张表，就不会把所有的device_id都查询出来。

注意，on语句中的a.university=b.university不可省，因为如果那样的话就会把不同学校相同gpa看作是匹配的了。

题目指路：找出每个学校GPA最低的同学_牛客题霸_牛客网 (nowcoder.com)

题目六：（统计复旦用户8月练题情况）：

代码实现方法：

select u.device_id,u.university,
    count(question_id) question_cnt, sum(if(q.result='right', 1, 0)) right_question_cnt
from user_profile u
left join question_practice_detail q
on u.device_id=q.device_id
where university='复旦大学' and substr(date,7,1)=8
group by device_id;
# 练习的总题目数指的是回答的总次数，不需要去重

注意：

分组函数里不能加条件，即不可以通过 count(result='right') 来统计出答题正确的数量。

分组函数不能用在where中。

分组函数不能嵌套使用。

启示：

想通过 count(某一字段=xxxx) 这种方式来获取个数的话可以使用 sum(if(q.result='right', 1, 0)) 的形式获取。

题目指路：统计复旦用户8月练题情况_牛客题霸_牛客网 (nowcoder.com)