mysql8.0常用语法

开窗函数

开窗函数 ，可以按组查询排列数据，且给每一行值都返回指定的聚合值，语法如下：

开窗函数 over([PARTITION BY 分组字段(多个用,链接)]  [ORDER BY 排序字段(同前)])

开窗函数分为两类：
聚合开窗函数：sum,avg,count,max,min等
专用开窗函数：Rank,Dense_Rank,Row_Number 等
注意：聚合开窗函数使用ORDER BY时，必须跟在PARTITION BY之后。不能单独使用
详细教程可参看：Mysql开窗函数

字段拆分

有如下表 friend

name	sex	friend
张三	26	丽丽,红红,娜娜

现在因为某些需求，需要把friend字段进行拆分，并且和name、sex再次组成一条新的数据进行展示，如下

name	sex	friend
张三	26	丽丽
张三	26	红红
张三	26	娜娜

此时可以使用CROSS JOIN 函数，把friend进行拆分作为一个虚拟表后进行联表查询,语法如下

SELECT name,sex,jt.value
FROM friend
CROSS JOIN JSON_TABLE(
  friend,
  '$[*]' COLUMNS(value INT PATH '$')
) AS jt where name="张三"

从名字我们可以看出，该函数是对json字符串进行拆分的，而我们的frend字段存的不是json字符串，所以需要进行处理，把friend字段拼接成json字符串，如下

CONCAT('["',replace(friend,',','","'), '"]')

递归关联查询

– 假定有如下的关联表结构

CREATE TABLE relations (
    parent_id INT,
    child_id INT,
    PRIMARY KEY (parent_id, child_id)
);

– 插入现有关系

INSERT INTO relations (parent_id, child_id) VALUES
(1, 2), -- a -> b
(2, 3), -- b -> c
(3, 4); -- c -> d

– 新增关系

INSERT INTO relations (parent_id, child_id) VALUES
(4, 5); -- d -> e

– 递归查询来找到e的所有关联节点

WITH RECURSIVE cte_relations AS (
    SELECT child_id, parent_id FROM relations WHERE child_id = 5
    UNION ALL
    SELECT p.child_id, p.parent_id FROM relations p
    INNER JOIN cte_relations c ON p.child_id = c.parent_id
) SELECT * FROM cte_relations;

在这个查询中，CTE首先选出了与e直接关联的d，然后递归地向上找到d、c、b、a的关系。

注意，以上查询可能会出现死循环
比如，当数据中加入了parent_id = 5,child_id=1时，上面的递归查询会出现死循环，此时我们只需进行如下修改即可

WITH RECURSIVE cte_relations AS (
    SELECT child_id, parent_id FROM relations WHERE child_id = 5
    UNION ALL
    SELECT p.child_id, p.parent_id FROM relations p
    INNER JOIN cte_relations c ON p.child_id = c.parent_id where c.parent_id<>5
) SELECT * FROM cte_relations;

该查询将查询出所有和5有关系的数据

当每一层的数据量过大时，查询次数将指数级增减，此时我们可以通过限制递归层级来限制查询次数，如下

WITH RECURSIVE cte_relations AS (
    SELECT child_id, parent_id,1 AS depth FROM relations WHERE child_id = 251
    UNION ALL
    SELECT p.child_id, p.parent_id, c.depth + 1 FROM relations p
    INNER JOIN cte_relations c ON p.child_id = c.parent_id where  c.depth < 1
) SELECT * FROM cte_relations