MySQL8.0新特性join lateral 派生子查询关联

在 MySQL 8.0 及更高版本中，LATERAL 是一个用于派生表（derived tables）的关键字，它允许派生表中的查询引用包含该派生表的 FROM 子句中的表。这在执行某些复杂的查询时特别有用，尤其是当需要在子查询中引用外部查询的列时。

以下是关于 MySQL 8.0 中 LATERAL 的几个关键点：

1、定义与用途：

• LATERAL 允许派生表（子查询）引用外部查询的列。
• 在 MySQL 8.0 之前，这通常需要使用其他技术（如变量或临时表）来实现。

2、基本语法：

SELECT ...  
FROM outer_table  
JOIN LATERAL (  
    SELECT ...  
    FROM inner_table  
    WHERE inner_table.column = outer_table.column  
    -- 可以使用更多条件和逻辑  
) AS subquery_alias  
ON some_condition;

3、使用场景：

• 当你需要在子查询中使用外部查询的列作为过滤条件时。
• 当你需要在子查询中计算每个外部查询行的某些聚合或窗口函数时。

4、示例

4.1、测试表及数据准备

CREATE TABLE t_city_list(
id bigint auto_increment primary key,
country  varchar(64),
city varchar(64)
);
INSERT INTO t_city_list(country,city) VALUES ('中国','北京'),('中国','广州'),('中国','深圳'),('中国','香港'),('中国','上海'),('日本','东京'),('日本','大阪');

--查询数据
select * from t_city_list;
mysql> select * from t_city_list;
+----+---------+--------+
| id | country | city   |
+----+---------+--------+
|  1 | 中国    | 北京   |
|  2 | 中国    | 广州   |
|  3 | 中国    | 深圳   |
|  4 | 中国    | 香港   |
|  5 | 中国    | 上海   |
|  6 | 日本    | 东京   |
|  7 | 日本    | 大阪   |
+----+---------+--------+
7 rows in set (0.00 sec)

4.2、Group_concat（MySQL分组函数）

语法：

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
分组后，把合并字段的值，合并于一个字段中，默认,分隔 通过设置SEPARATOR修改分隔符

select country,group_concat(city) from t_city_list group by country;

+---------+------------------------------------+
| country | group_concat(city)                 |
+---------+------------------------------------+
| 中国    | 北京,广州,深圳,香港,上海           |
| 日本    | 东京,大阪                          |
+---------+------------------------------------+

4.3、join lateral 派生子查询关联t_city_list 显示完成信息

-- on some_condition
select * from t_city_list t1
join lateral (select country,
                       group_concat(city separator'-') as city_list 
                     from t_city_list 
                     where country=t1.country) t2
on t2.country=t1.country;

-- on true
select * from t_city_list t1
join lateral (select country,
                       group_concat(city separator'-') as city_list 
                     from t_city_list 
                     where country=t1.country) t2
on true;

5、 MySQL5.7版本实现相同的需求

在MySQL5.7版本实现相同的需求，则需要子查询分组Group_concat，之后依据关联字段进行join

mysql> select t1.* ,t2.group_concat_city
    -> from t_city_list t1
    -> join(select country,group_concat(city) as group_concat_city
    ->      from t_city_list
    ->      group by country) t2
    -> on t2.country=t1.country;
+----+---------+--------+------------------------------------+
| id | country | city   | group_concat_city                  |
+----+---------+--------+------------------------------------+
|  1 | 中国    | 北京   | 北京,广州,深圳,香港,上海           |
|  2 | 中国    | 广州   | 北京,广州,深圳,香港,上海           |
|  3 | 中国    | 深圳   | 北京,广州,深圳,香港,上海           |
|  4 | 中国    | 香港   | 北京,广州,深圳,香港,上海           |
|  5 | 中国    | 上海   | 北京,广州,深圳,香港,上海           |
|  6 | 日本    | 东京   | 东京,大阪                          |
|  7 | 日本    | 大阪   | 东京,大阪                          |
+----+---------+--------+------------------------------------+
7 rows in set (0.00 sec)

6、限制：

• LATERAL 只能在 FROM 子句中使用。
• 如果 LATERAL 派生表引用聚合函数，则聚合查询不能是包含 LATERAL 派生表的查询的 FROM 子句的一部分。
• 如果 LATERAL 派生表位于联接子句的右操作数中，并且包含对左操作数的引用，则联接操作必须是 INNER JOIN、CROSS JOIN 或 LEFT JOIN。

7、与其他数据库系统的兼容性：

• 并非所有数据库系统都支持 LATERAL 关键字。但在 PostgreSQL 和 SQL 标准中，它已被广泛使用。

通过 LATERAL，MySQL 8.0 提供了更强大和灵活的查询功能，特别是在处理涉及复杂逻辑和聚合的查询时。