SQL错题笔记—牛客网

IS用法/MySQL查询语句的正确执行顺序

• is 只能在null的时候用 正常情况要用=‘ ’
• 聚合函数不能用在WHERE子句后面，注意查询的执行顺序；
• MySQL查询语句的正确执行顺序：FROM(including JOINs) ---> WHERE ---> GROUP BY ---> HAVING ---> SELECT ---> DISTINCT ---> ORDER BY ---> LIMIT/OFFSET

过滤空值的三种方法

(1) Where 列名 is not null

(2) Where 列名 != 'null'

(3) Where 列名 <> 'null'

 字符匹配

一般形式为：列名 [NOT ] LIKE

匹配串中可包含如下四种通配符：
_：匹配任意一个字符；
%：匹配0个或多个字符；
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；
[^ ]：不匹配[ ]中的任意一个字符。

 SQL执行顺序

1. from 
2. join 
3. on 
4. where 
5. group by(开始使用select中的别名，后面的语句中都可以使用)
6.  avg,sum.... 
7. having 
8. select 
9. distinct 
10. order by
11. limit 

CASE函数

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

简单 CASE函数

1 2 3 4 5 6

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

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

搜索CASE函数

复制代码

1 2 3 4 5 6

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

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

case when的例子

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

题意明确：用户在某天刷题后第二天再来刷题的平均概率

---

问题分解：

• 限定条件：第二天再来。
  • 解法1：表里的数据可以看作是全部第一天来刷题了的，那么我们需要构造出第二天来了的字段，因此可以考虑用left join把第二天来了的拼起来，限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选，并用device_id限定是同一个用户。
  • 解法2：用lead函数将同一用户连续两天的记录拼接起来。先按用户分组partition by device_id，再按日期升序排序order by date，再两两拼接（最后一个默认和null拼接），即lead(date) over (partition by device_id order by date)
• 平均概率：
  • 解法1：可以count(date1)得到左表全部的date记录数作为分母，count(date2)得到右表关联上了的date记录数作为分子，相除即可得到平均概率
  • 解法2：检查date2和date1的日期差是不是为1，是则为1（次日留存了），否则为0（次日未留存），取avg即可得平均概率。
• 附：lead用法，date_add用法，datediff用法，date函数

---

细节问题：

• 表头重命名：as
• 去重：需要按照devece_id,date去重，因为一个人一天可能来多次
• 子查询必须全部有重命名

完整代码：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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：

复制代码

1 2 3 4 5 6 7 8 9 10 11

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

首先大脑一片空白 ,其次写个错的还是没思路就看答案了

SELECT
    CASE
        WHEN profile like '%,male' THEN 'male'
        WHEN profile like '%,female' THEN 'female'
    END AS gender,
    COUNT(*) as number
FROM
    user_submit
GROUP BY
    gender;

select device_id,university,gpa
from(
SELECT device_id, university, gpa, 
RANK() OVER (PARTITION BY university ORDER BY gpa DESC) AS rank
FROM user_profile
)AS ranked_profiles
where rank=1