在子查询中引用查询结果别名

本文介绍了在子查询中引用查询结果别名的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这主要是一个SQL语法/SQL功能问题.为什么以下查询不起作用:

This is mostly a SQL syntax / SQL capability question. Why does the following query NOT work:

SELECT * from ( select m.*, p.type, from multipliers m inner join pushes p on m.push_id = p.id where p.type = 'CONSTANT' ) AS res1 where res1.push_id = ( select max(push_id) from res1 );

以下内容顺利完成后:

SELECT * from ( select m.*, p.type, from multipliers m inner join pushes p on m.push_id = p.id where p.type = 'CONSTANT' ) AS res1 where res1.push_id = ( select max(push_id) from ( select m.push_id from multipliers m inner join pushes p on m.push_id = p.id where p.type = 'CONSTANT' ) AS res2 );

推荐答案

根据MySQL 5.7文档:

派生表不能是相关子查询，也不能包含外部引用或对同一SELECT的其他表的引用.

Derived tables cannot be correlated subqueries, or contain outer references or references to other tables of the same SELECT.

换句话说，您不能在子查询中引用派生表.文档中没有说明这一点，但是由于OOO问题，它可能以这种方式起作用，因为在子查询之前不一定要处理派生表.在MySQL 8.0中，您将可以使用公用表表达式或CTE ，基本上可以让您在查询之前定义一个可重用的派生表，但是在此之前，请使用第二种方法.

In other words, you can't reference a derived table in a subquery. The documentation doesn't state this, but it likely acts this way because of an OOO issue since the derived table isn't necessarily processed before the subquery. In MySQL 8.0 you will be able to use a Common Table Expression or CTE, which basically lets you define a reusable derived table before your query, but until then use your second approach.