MySQl 专栏持续更新 不说晦涩难懂的东西 尽量输出容易理解 和 使用的SQL技巧 和 初中级开发不是很常用的但很有用的知识

欢迎查看👉🏻👉🏻👉🏻SQL 专栏 查漏补缺 指教一二

前言

通过思路解析 分析SQL书写 拆分逻辑 简单易懂 跟着学习 等系列更新完 SQL编写能力 和 SQL思维都会有提升 欢迎关注专栏 如果有更简单的接替方法 可以发在评论区会补充完善

为什么 进行SQL 思维训练 真正做过实战项目的人明白 其实开发思维很重要 开发思维是大于 开发能力的 SQL思维能力也一样 因为SQL是和数据直接打交道的 需要去训练自己的编写能力和编写思路 能够最快的找到最优解

创建表

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
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,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

表结构

问题一

现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量 如果age 为空也放在 25岁以下

示例

分析

这个问题主要围绕 user_profile 进行查询 如果光看的 第一反应就是直接判断年龄 然后 group by 但是 当年龄为空的时候 也要加到 25岁下面 所以可能要进一步处理

思路一

我们第一个思路是你查出 25 以上 和 25以下的数据 然后组合到一起

union all 将两个或者两个以上的结果集组合起来 并且不去重

先看基本的SQL：

先查出 25岁以下 或者 age 为null 的数据

然后查出 25岁以上的数据

之后通过 union all 将两个数据组合在一起

select '25岁以下' as age_cut,count(device_id) as number
from user_profile
where age<25 or age is null
union all
select '25岁及以上' as age_cut,count(device_id) as number
from user_profile
where age>=25;

知识点

• 主要是练习 union all 的用法

思路二

上面这种方法 需要连续查询两次 看起来代码比较麻烦 我们还可以使用 IF 判断

**if(expr,v1,v2)函数 ** 在if(expr,v1,v2)函数中,若表达式expr是true(expr<>0 and epr<>null)返回v1，否则返回v2

SQL 如下：

SELECT
IF
	( age < 25 OR age IS NULL, "25岁以下", "25岁及以上" ) AS age_cut,
	COUNT( id ) AS num 
FROM
	user_profile 
GROUP BY
	age_cut

缺点：

这种方法因为没有where 条件所以是不走索引的

知识点

• if 判断的使用

思路三

我们可以 使用 case...when

之前使用 case...when 进行过行转列 但是这个也可以当成是 if 判断进行使用

在数据分析时经常要用到行转列，此时如果使用case when就会方便很多，case when的熟练使用程度，可以说的判断对SQL操作水平的评定方法之一。

第一种使用形式简单变量表达式：

case <表达式(变量)>
    when <表达式1(变量的值)> then <表达式1(符合变量值的结果)>
    when <表达式2(变量的值)> then <表达式2(符合变量值的结果)>
    when <表达式3(变量的值)> then <表达式3(符合变量值的结果)>
...
   else <表达式(符合变量值的结果)>
end

第二种形式搜索变量表达式：

case 
    when <求值表达式1(一般是判断)> then <表达式1(符合变量值的结果)>
    when <求值表达式2(一般是判断)> then <表达式2(符合变量值的结果)>
    when <求值表达式3(一般是判断)> then <表达式3(符合变量值的结果)>
...
   else <表达式(符合变量值的结果)>
end

通常情况下，第一种的方式会更加灵活，在工作中也推荐使用第二种的写法

我们实际使用一下看看

select 
    (case
        when age>=25 then '25岁及以上'
        else '25岁以下'
    end) as age_cut, 
    count(DISTINCT device_id)
    
    from user_profile
    group by age_cut

缺点如上 全文扫描

知识点

• case...when 的实际使用

推荐阅读相关文章：小白也能看到索引的使用和规则