时间戳

首页 > 解决方案 > 如何在嵌套的两级子查询中使用外部表？

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

问题描述

我的查询如下：

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'

标签： mysqlsql

解决方案

无法从派生表访问外部表（简化一点，在您的情况下，这是任何需要别名的“子查询” gand 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 = ...对于每个 new ，您可能需要熟练地在 0 处重新启动o.code_id，但是由于您似乎没有在任何地方使用绝对值，所以这可能并不重要。

这将为每个评估完整的派生表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.id来自的列codes（并且您joins不会将行相乘），但您的查询在这方面可能已经简化，因此您当然仍然可以codes再次加入（和其他表）以获得您需要的列（但您不再需要where-condition 了）。

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

推荐阅读