sql - 是否可以在使用 access 的 from 子句中使用子查询而不是表？

Question

在 MS ACCESS 中，是否可以:

在 from 子句中使用子查询而不是第一个表？

(下面的代码是根据帮助过我的好人的建议编辑的)

例子:

Part 1 (now working):
    FROM
    (SELECT [Distributor2].PRODUCTCODE
      FROM [Distributor2]

      UNION

     SELECT [DISTRIBUTOR3].PRODUCTCODE
      FROM [DISTRIBUTOR3]

      UNION

     SELECT [DISTRIBUTOR1].PRODUCTCODE
      FROM [DISTRIBUTOR1]
    ) AS [ALLPRODUCTCODES]

包括第 2 部分(现在工作!甚至我的多个连接条件!!!)。我已将下面的 ALLPRODUCTSCODES 表重命名为“表 1”，以便更清楚:

  FROM ((((subqueryabove) AS [TABLE1])
LEFT JOIN [TABLE2] on (Table2.productcode = Table1.productcode AND Table2.year=2013))
LEFT JOIN [TABLE3] on (Table3.productcode = Table1.productcode AND Table3.year=2013))
LEFT JOIN [TABLE4] on (Table4.productcode = Table1.productcode AND Table4.year=2013)

(根据前两个回复的建议编辑的代码 - 感谢您对 MSAccess 括号疯狂的帮助!!!)

Answer 1

此查询触发“JOIN 操作中的语法错误”，这可能令人费解，因为没有涉及显式JOIN。

SELECT sub.*
FROM
    (
        (SELECT 'a' AS fld1 FROM Dual)
        UNION ALL
        (SELECT 'b' AS fld1 FROM Dual)
        UNION ALL
        (SELECT 'c' AS fld1 FROM Dual)
    ) AS sub;

如下所示，消除包含每个合并的 SELECT 语句的括号可以使查询无错误地运行。我怀疑您可能正在处理同样的问题 --- 所以请丢弃您的那些麻烦的括号。

SELECT sub.*
FROM
    (
        SELECT 'a' AS fld1 FROM Dual
        UNION ALL
        SELECT 'b' AS fld1 FROM Dual
        UNION ALL
        SELECT 'c' AS fld1 FROM Dual
    ) AS sub;

当您 LEFT JOIN 子表时，它仍然可以工作。我在 Access 2007 中创建并测试了这个查询 ...

SELECT sub.*, tblFoo.id, tblFoo.some_text
FROM
    (
        SELECT 'a' AS fld1, 1 AS fld2 FROM Dual
        UNION ALL
        SELECT 'b' AS fld1, 2 AS fld2 FROM Dual
        UNION ALL
        SELECT 'c' AS fld1, 3 AS fld2 FROM Dual
    ) AS sub
    LEFT JOIN tblFoo
    ON sub.fld2 = tblFoo.id;