当前位置:   article > 正文

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

mysql 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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

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)                 |
+---------+------------------------------------+
| 中国    | 北京,广州,深圳,香港,上海           |
| 日本    | 东京,大阪                          |
+---------+------------------------------------+
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

在这里插入图片描述

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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

6、限制:

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

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

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

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

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/酷酷是懒虫/article/detail/761857
推荐阅读
相关标签
  

闽ICP备14008679号