时间戳

首页 > 解决方案 > 找不到 MS Access SQL FROM 子句语法的错误

sql - 找不到 MS Access SQL FROM 子句语法的错误

问题描述

Select distinct [Doc Type], [Customer Number], count([Customer Number]) , [T] From (
Select distinct A.[Customer Number] & A.[Membership Number], A.[Customer Number] , B.[Doc Type ], B.[SumOpenAmount] From(
SELECT distinct [Doc Type] , [Customer Number], Sum([Open Amount]) as T  FROM Data Where [Doc Type] = 'RU')B, [Data] A
Where B.[Customer Number] = A.[Customer Number] Group by [Doc Type]) 
group by [Doc Type], [Customer Number]
having count([Customer Number]) = 1

抛出 Doc Type 可能引用 SQL 语句的 from 子句中列出的多个表的错误

标签： sqlms-access

解决方案

目前，您的查询存在许多语法和次优问题：

  1. GROUP BYSELECT ：在子句中包含非聚合列的聚合查询中，GROUP BY必须使用。一些方言允许GROUP BY省略列，但不允许 Access SQL。此外，DISTINCT对于GROUP BY.

  2. ALIASES：每当使用子查询和连接时，始终使用表别名以避免派生表和所有表达式的列别名的名称冲突。此外，请避免使用 A、B、C ... 以获取更多信息别名，包括 T。请参阅Bad Habits to Kick ：使用表别名，如 (a, b, c) 或 (t1, t2, t3)。

  3. EXPLICIT JOIN：使用显式连接的当前 ANSI SQL 标准，而不是使用过时的隐式连接WHERE。请参阅显式与隐式 SQL 连接。

因此，请考虑采用上述准则进行以下调整。

SELECT [doc type]
     , [customer number]
     , COUNT([customer number]) As CountCustomerNumber          -- ALIAS ADDED
     , SUM([SumOpenAmount]) As TotalOpenAmount                  -- AGGREGATED COLUMN
FROM   
      (SELECT d.[customer number] & d.[membership number] AS CustMemb  -- ALIAS ADDED
            , d.[customer number]
            , agg.[doc type]
            , SUM(agg.[TotalSubOpenAmount]) AS SumOpenAmount    -- AGGREGATED COLUMN
        FROM  (SELECT [doc type]
                    , [customer number]
                    , SUM([open amount]) AS TotalSubOpenAmount  -- INFORMATIVE ALIAS
               FROM   data
               WHERE  [doc type] = 'RU'
               GROUP BY [doc type]
                      , [customer number]
              ) agg                                             -- INFORMATIVE ALIAS
        INNER JOIN [data] d                                     -- INNER JOIN USED
           ON  d.[customer number] = agg.[customer number]
        GROUP  BY d.[customer number] & d.[membership number]   -- GROUP BY COLUMNS ADDED
                , d.[customer number]
                , agg.[doc type]
      ) AS sub                                                  -- ALIAS ADDED
GROUP  BY [doc type]
        , [customer number]
HAVING COUNT([customer number]) = 1

注意：由于 Access 不支持查询中的注释。运行前删除所有--消息。

推荐阅读