Skip to content

mysql中的复杂查询

group by...having 和 where 的区别

mysql 先对数据按照 where 子句过滤，然后在对返回的结果分组，最后对分组后的数据按照 having 子句过滤。

where 是对行过滤，而 having 是对分组过滤，所以可以在 having 子句后面使用聚集函数，而在 where 子句后面使用聚集函数是没有意义的。

join...on 和 where 的区别

在联结查询中，join...on...子句和 where 子句是可以一起出现的，要想弄清楚它们的区别，首先要清楚联结查询的过程。联结查询时要先根据联结条件对两张表做笛卡尔积，生成一个大表，然后再对这个大表做过滤，最终得到查询结果。

join...on 和 where 的区别在于，join...on...子句是在联结时生效，而 where 子句是在联结后生效。

因此，当联结是外部联结（联结子句是 left join 或 right join）时，由于外部联结会将左表或右表中没有建立联结的行都返回，所以 on 后面的条件不会去掉那些不符合条件的行，而 where 子句是严格的，会将不等值的行都去掉。

而当联结是内部联结（等值联结）的时候，联结子句（join...on...）只会返回等值的行，这时写在 on 后面的条件和 where 子句中的条件是等价的，因此，使用内部联结的时候，完全可以用 where 子句代替 join...on...

窗口函数

参考mysql-8.0-documentation-window-functions

介绍

窗口函数是 mysql 8.0 加入的功能，目的是为了满足分组统计场景的查询需求。

我们使用 SQL 语句查数据时通常会有一些统计分析的需求（例如聚合和排序），一般有以下三类统计场景：

  1. 简单统计
    按条件查出数据后，对返回数据整体做统计操作，这种统计使用select...from...where...order by配合聚合函数即可完成。
  2. 分组聚合
    按条件查出数据后，对数据分组，然后对每个分组做聚合操作，每个分组返回一条数据即可，这种统计使用group by即可完成。
  3. 分组统计
    很多时候我们既想分组，又想对分组聚合，又想把分组内部的数据全部返回，而不是每个分组只返回一个数据，这时光用group by是不能完成任务的，为了解决问题，我们经常要写一堆复杂的自联结，不仅 SQL 难以读懂，而且查询效率也很低下。

窗口函数的引入就是为了方便解决上述第三种查询场景，我们可以使用窗口函数对分组内部的数据做统计，然后把统计后的数据全部返回，并且可以按排序为每行计算出一个序号，方便我们使用序号对数据做二次过滤

对于窗口函数来说，“窗口”的概念非常重要。窗口函数会对组内的每条记录执行统计，这需要给窗口函数规定一个统计范围（统计范围不一定是整个分组，也可能只对分组的一部分做统计），我们把这个统计范围称为窗口，有的窗口函数的窗口是固定的，有的是滑动的，需要用户指定

用法

以下是窗口函数的用法

-- 简单写法（把窗口函数返回的行序号作为新的一列new_col）
select
window_function() over(partition by xxx order by xxx) as new_col from xxx

-- 上面也可以把分组条件拆成单独的window语句

select
window_function() over w as new_col
from xxx
window w as (partition by xxx order by xxx)

窗口函数的完整语法如下

window_function_name(expression)
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    )

window_function_name(expression)是窗口函数，over()为窗口函数指定了分组规则，如果里面什么都不写就是在整个表上运行窗口函数

partition子句定义了按哪些列分组，order by子句定义了按哪些列排序，frame子句定义了一个滑动窗口，分为按行定义和按范围定义

窗口函数只能用在 select 列表和 order by 子句里。mysql 是对 where、group by、having 子句返回的结果上进行窗口函数的处理，处理顺序在 order by、limit、select distinct 之前

frame语法

-- frame_units是窗口类型，frame_extent是窗口范围
frame_units frame_extent

-- frame_units有两种，ROWS和RANGE，分别是按行的位置指定范围（行的位置就是指“第几行”）和按行的值指定范围（对RANGE模式不太理解，大概是值相同的行在统计上算作同一行）
frame_units:
    {ROWS | RANGE}
-- frame_extent也有两种，只指定开始行（结尾行默认是当前行），和同时指定开始和结束行
frame_extent:
    {frame_start | frame_between}

frame_between:
    BETWEEN frame_start AND frame_end

frame_start, frame_end: {
    CURRENT ROW  -- 当前行
  | UNBOUNDED PRECEDING -- 分组第一行
  | UNBOUNDED FOLLOWING -- 分组最后一行
  | expr PRECEDING -- 当前行之前的expr行，例如10 PRECEDING的意思是当前行之前的10行
  | expr FOLLOWING -- 当前行之后的expr行
}

常用窗口函数

可以当作窗口函数的聚合函数：sum、min、max、avg、count

以下是专用窗口函数

函数 功能
row_number() 返回分组内排序后的行号
rank() 返回分组内排序后的序号，如果有并列的行，它们行号相同，行号是并列组前面的总记录数+1
dense_rank() 返回分组内排序后的序号，如果有并列的行，它们行号相同，行号是上一个并列组的序号+1
first_value(expr)/last_value(expr) 返回窗口中的第一个行或最后一个行，并对其做表达式计算
lag(expr,N,default) 返回分组内位于当前行之前N行（这里是指上方）的行，default是指如果没有之前N行返回的值，N和default的默认值分别为1和null
lead(expr,N,default) 返回分组内位于当前行之后N行（下方）的行。（这里的之前之后是指遍历顺序）
nth_value(expr,N) 返回窗口内的第n行
ntile(n) 将分组内行平均分成n个桶，返回每个行所属的桶号

以下是row_number、rank和dense_rank的区别

+------+------------+------+------------+
| val  | row_number | rank | dense_rank |
+------+------------+------+------------+
|    1 |          1 |    1 |          1 |
|    1 |          2 |    1 |          1 |
|    2 |          3 |    3 |          2 |
|    3 |          4 |    4 |          3 |
|    3 |          5 |    4 |          3 |
|    3 |          6 |    4 |          3 |
|    4 |          7 |    7 |          4 |
|    4 |          8 |    7 |          4 |
|    5 |          9 |    9 |          5 |
+------+------------+------+------------+

其他

if函数

if函数可以用在select子句中

用法：if(expr1,v1,v2)，当expr1的值为真时，返回v1，否则返回v2

case 函数

case 函数可以用在 select 子句里，并将其结果命名为一个字段，还可以按这个字段分组，比如以下 sql

select case
when age>=25 then "25岁及以上"
else "25岁以下"
end as age_cut,count(id) as number
from user_profile
group by age_cut;

这个 sql 返回的结果如下所示

age_cut,number
25岁以下,4
25岁及以上,3

case 函数有两种写法，第一种，

CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END CASE;

第二种（有点类似于 golang 的 switch 语句），

CASE
    WHEN <条件1> THEN <操作>
    WHEN <条件2> THEN <操作>
    ...
    ELSE <操作>
END CASE;