如何在嵌套的两级子查询中使用外层表？

Question

我的查询如下：

SELECT codes.id, (SELECT SUM(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM a.end_date), EXTRACT(YEAR_MONTH FROM a.start_date))) months
  FROM (
    SELECT MIN(g.start_date) start_date, MAX(g.end_date) end_date 
      FROM (
        SELECT @group_id := @group_id + (@end_date IS NULL OR o.start_date > @end_date) group_id,
               start_date,
               @end_date := DATE(CASE 
                 WHEN (@end_date IS NULL OR o.start_date > @end_date) THEN o.end_date
                 ELSE GREATEST(o.end_date, @end_date)
               END) end_date  
          FROM dates_range o
          JOIN (SELECT @group_id := 0, @end_date := NULL) init
          WHERE o.code_id = codes.id -- THIS CLAUSE DOES NOT WORK 
      ORDER BY o.start_date ASC  
            ) g
  GROUP BY  g.group_id  
        ) a) as sum_date_ranges
FROM codes
-- A LOT OF JOINs and WHEREs OF codes.id THAT I CAN NOT MOVE INSIDE THE NESTED TWO LEVEL SUBQUERY

正如注释中所写的那样，WHERE子句不起作用，但我需要在嵌套的两级子查询中使用codes.id。我该怎么做呢？

MySQL错误：

Unknown column 'codes.id' in 'where clause'

Answer 1

从派生表访问外部表是不可能的(简化一点，这是任何需要别名的“子查询”，在您的例子中是g和a)。

您必须使用join在外部级别执行所需的操作。为此，您需要跟踪o.code_id并将其传递到外部级别：

SELECT codes.id, sum_date_ranges.sum_date_ranges
FROM codes
JOIN 
  (SELECT a.code_id, SUM( ... ) as sum_date_ranges
    (SELECT g.code_id, g.group_id, MIN( ... ), MAX( ... )
     FROM ( SELECT o.code_id, @group_id = ...
            FROM dates_range o ...
            -- WHERE o.code_id = codes.id -- not required
            ORDER BY o.code_id, o.start_date
          ) g
     GROUP BY g.code_id, g.group_id
    ) a
    GROUP BY a.code_id
  ) as sum_date_ranges
ON sum_date_ranges.code_id = codes.id
-- the rest of your joins and where-conditions

对于每一个新的@group_id = ...，您可能都需要熟练地在0重新启动，但是由于您似乎没有在任何地方使用绝对值，所以这可能并不重要。

这将评估每个code_id的完整派生表，然后丢弃它不需要的所有内容(这可能是数据的重要部分，也可能不是数据的重要部分)。为了防止这种情况，您可以实际上将外部条件放在查询中：

SELECT sum_date_ranges.code_id as id, sum_date_ranges.sum_date_ranges
-- from codes -- not required anymore, we get codes.id from derived table
FROM 
  (SELECT a.code_id, SUM( ... ) as sum_date_ranges
    (SELECT g.code_id, g.group_id, MIN( ... ), MAX( ... )
     FROM ( SELECT o.code_id, @group_id = ...
            FROM dates_range o ...
            WHERE o.code_id IN (SELECT codes.id 
                                FROM codes
                                -- your join and where-conditions
                             )
            ORDER BY o.code_id, o.start_date
          ) g
     GROUP BY g.code_id, g.group_id
  ) a
  GROUP BY a.code_id
) as sum_date_ranges
-- optionally in case you need other columns from codes
-- JOIN codes ON codes.id = sum_date_ranges.code_id  

这假设您实际上只需要来自codes的列codes(并且您的joins不会乘以您的行)，但是在这方面您的查询可能已经简化了，所以您当然仍然可以再次加入codes (和其他表)以获得所需的列(但这里不再需要where-condition )。

在没有派生表的情况下完全重写查询是可能的，但可能需要进行大量修改，而且不太可能处理变量。如果您从头开始，这可能是最简单的(如果您需要帮助，您需要提供完整的查询、示例数据、预期的输出和代码应该做的一些解释)。具体来说，如果您使用MySQL 8，您可能可以使用窗口函数而不是变量来获得这种排序、和、第一和最后的值，尽管看起来毕竟您仍然需要一个派生表。