在子查询中引用查询结果别名 [英] Referencing a query result alias in a subquery

问题描述

这主要是一个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
);

推荐答案

派生表不能是相关子查询，也不能包含外部引用或对同一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.

这篇关于在子查询中引用查询结果别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！