在 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 提供了更强大和灵活的查询功能，特别是在处理涉及复杂逻辑和聚合的查询时。