MariaDB 10.6中LATERAL JOIN能否嵌套透视表子查询？

需要将表A（日期频率更高）与表B按最近的最早日期关联，表B需先完成透视再进行关联。单独运行表A查询和表B透视逻辑均正常，但将透视子查询嵌入LATERAL JOIN时出现ER_PARSE_ERROR语法错误，要求用纯SQL实现，不使用Python/Pandas/Spark等工具。

表结构与数据 #

表A #

表B #

尝试的SQL语句 #

SELECT 
  A.*, C.*
FROM ( 
   SELECT
         `j`.`id`,
         `j`.`date`,
         `k`.`key`,
         `k`.`value`
   FROM `j`
       INNER JOIN `k` ON `j`.`id` = `k`.`id`
       WHERE `j`.`guid` = '<filter>'
) AS A
LEFT JOIN LATERAL (
   SELECT `B`.*
   FROM (
         SELECT
         'rate'.`date`,
         MIN( CASE WHEN `rate`.`item` = 'itemA' THEN `rate`.`cost` END) 'itemA',
         MIN( CASE WHEN `rate`.`item` = 'itemB' THEN `rate`.`cost` END) 'itemB',
         MIN( CASE WHEN `rate`.`item` = 'itemC' THEN `rate`.`cost` END) 'itemC'
         FROM `rate`
         GROUP BY `rate`.`date`) AS B
   WHERE `B`.`date` <= `A`.`date`
   ORDER BY `B`.`date` DESC LIMIT 1) AS `C` ON true
);

报错信息 #

Schema Error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( SELECT B.* FROM ( SELECT 'rate'.date, MIN'

期望结果示例 #

解决方法 #

错误分析 #

1. 语法错误：透视子查询中'rate'.date使用了单引号，会将`rate`识别为字符串而非表名，正确写法应为`rate.`date或直接rate.date。
2. 数据匹配错误：表B的Item值为'A'/'B'/'C'，但CASE语句中写的是'itemA'/'itemB'/'itemC'，导致透视后列值为空。
3. 表名不匹配：尝试的SQL中表A子查询用了j/k表，但实际表A结构为date/code/multiplier，属于笔误。

修正后的SQL #

SELECT 
    A.*,
    C.A,
    C.B,
    C.C,
    C.date AS date2
FROM 
    表A AS A
LEFT JOIN LATERAL (
    SELECT 
        p.date,
        p.A,
        p.B,
        p.C
    FROM (
        -- 先对表B做透视处理
        SELECT 
            `date`,
            MIN(CASE WHEN Item = 'A' THEN cost END) AS A,
            MIN(CASE WHEN Item = 'B' THEN cost END) AS B,
            MIN(CASE WHEN Item = 'C' THEN cost END) AS C
        FROM 表B
        GROUP BY `date`
    ) AS p
    -- 筛选表A日期之前的最近一条透视记录
    WHERE p.date <= A.date
    ORDER BY p.date DESC
    LIMIT 1
) AS C ON TRUE;

说明 #

• 先对表B按date分组完成透视，将行转列得到A/B/C三列的成本值（因每个date+Item唯一，MIN/MAX效果一致）。
• 通过LATERAL JOIN为表A的每一行匹配表B透视结果中小于等于当前日期的最大日期记录，实现"最近的最早日期"关联。
• 修正了所有语法错误与数据匹配问题，可直接运行得到期望结果。

内容的提问来源于stack exchange，提问作者AKA_Tom