为什么我不能再次使用派生表(子查询结果)的名称? [英] Why I can't use the name of the derived table(subquery result) again?

问题描述

我需要重用子查询生成的表，但是当我尝试执行这样的 SQL 时

I need to reuse the table resulting from a sub-query, But when I try to execute a SQL like this one

SELECT id 
FROM company
LEFT JOIN 
    (
    SELECT * FROM sales
    -- join
    -- join
    -- join
    -- join
    ) foo
ON foo.company_id = company.id

LEFT JOIN
    (
    SELECT company_id,balance FROM foo -- generates error
    UNION ALL
    SELECT company_id,balance FROM manual_moves
    ) moves
ON moves.company_id = company.id;
-- note: the actual sub-query has been omitted to make it focused on the error 

不幸的是我遇到了错误

表 'databaseName.foo' 不存在

Table 'databaseName.foo' doesn't exist

构建foo 表的子查询有很多连接，我不想重新编写相同 子查询(我猜测这会对性能产生影响)

The sub-query that build the foo table has lots of joins and I don't want to re write the same sub-query again (I guess this will have an impact on performance)

有什么办法可以重复使用那个子查询结果表的名称-foo-吗?

推荐答案

首先将foo"查询的内容转储到临时表中，然后在主查询中查询该临时表.

Dump the content of your "foo" query into a temp table first, then query that temp table in your main query.

这篇关于为什么我不能再次使用派生表(子查询结果)的名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！