mysql - 嵌套 MySQL 查询列别名

Question

这是一个有效的 MySQL 查询吗？您可以使用嵌套查询之外的列的别名吗？ (series_id_a和series_id_b是函数输入，这应该在函数中使用)

select x1 as x1a, x2 as x2a, y1 as y1a, y2 as y2a FROM region WHERE series_id = series_id_a AND EXISTS(
                    SELECT x1 as x1b, x2 as x2b, y1 as y1b, y2 as y2b FROM region WHERE series_id = series_id_b AND x1b = x1a AND x2b = x2a AND y1a = y1b AND y2a = y2b
                )

Answer 1

不，在 WHERE 子句的条件中引用分配给外部查询中的表达式的列别名是无效的。

为了清楚起见，为了 future 读者的理智，以及这个世界上所有美好和美丽的事物......限定所有专栏引用。

作为我猜测您想要实现的目标的一个示例:

 SELECT r.x1 AS x1a
      , r.x2 AS x2a
      , r.y1 AS y1a
      , r.y2 AS y2a 
   FROM region r
  WHERE r.series_id =  ?  -- series_id_a parameter
    AND EXISTS ( SELECT 1
                   FROM region q
                  WHERE q.series_id =  ? -- series_id_b parameter
                    AND q.x1 = r.x1
                    AND q.x2 = r.x2
                    AND q.y1 = r.y1
                    AND q.y2 = r.y2
                )