主页 / dba / 问题 / 73659
Accepted
granady
Asked: 2014-08-12 04:45:57 +0800 CST

Microsoft Access 派生表上的 JOIN 不起作用

  • 772

你能帮我解释一下为什么我的下面的 SQL 在 Access 2010 中不起作用吗？

SELECT
  Table2.Product, 
  Table2.Color, 
  SEQMAX,
  Table1.Attribute
FROM 
( 
  Table2

  LEFT JOIN
  (
    SELECT
      Table1.Product, 
      MAX (Table1.Seq) as SEQMAX
    FROM Table1
    GROUP BY Table1.Product
  ) AS MAXTABLE   
  ON  Table2.Product = MAXTABLE.Product
)
LEFT JOIN  Table1
  ON MAXTABLE.SEQMAX = Table1.Seq 
  AND Table2.Product = Table1.Product ;

在我添加最后一行之前很好，AND Table2.Product = Table1.Product这两个部分分别AND工作正常，但是我无法弄清楚为什么它们在组合时不起作用。

我收到一个错误：JOIN expression not supported。

ms-access ms-access-2010

1 个回答

  1. Best Answer

    也许重写语句，以便 Microsoft Access 可以更容易地理解要求。

    就像是：

    SELECT
  T1.Product, 
  T1.Color, 
  T1.SEQMAX,
  Table1.Attribute
FROM (
    SELECT Table2.Product   
        , Table2.Color
        , SEQMAX
    FROM Table2
        LEFT JOIN
        (
            SELECT
              Table1.Product, 
              MAX (Table1.Seq) as SEQMAX
            FROM Table1
            GROUP BY Table1.Product
        ) AS MAXTABLE ON  Table2.Product = MAXTABLE.Product
    ) AS T1
    LEFT JOIN Table1
      ON T1.SEQMAX = Table1.Seq 
        AND T1.Product = Table1.Product;
    • 1

相关问题