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

Question

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

抛出一个错误，文档类型可能引用SQL语句的from子句中列出的多个表

Answer 1

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

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

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不支持查询中的注释。运行前删除所有--消息}

Answer 2

子查询中的B.[DOC TYPE]在字段名称中似乎有一个额外的空格。此外，子查询不引用内部子查询的[T]字段，因此它将不可用于主查询，除非它在Data表中
最后，外部子查询的分组依据没有指定【单据类型】来自哪个数据源进行分组。

试试这个

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.[T] 
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 B.[Doc Type]
) 
group by [Doc Type], [Customer Number]
having count([Customer Number]) = 1

Answer 3

因此，这是进行混叠的一个很好的理由。我认为发生的情况是，您最内部的(data(子查询返回doctype(作为外部子查询的一部分变为b(，而a也有一个doc type。您还可以删除内部的Group By子句，因为它是在最外层的查询上完成的；结果应该是相同的。

我还注意到您这样做：A.[Customer Number] & A.[Membership Number]，然后不对该列执行任何操作。如果你想用它做点什么，你应该命名列。我在下面把它命名为CMN，你可以选择你想要的任何东西。

你也在用) as B, [Data] A行做一个隐含的JOIN，我说得对吗？如果是这样的话，你应该考虑将其明确化，否则你可能会得到不想要的匹配。

如果这是你想要的，那么就这样做：

-- as B, [Data] A
++ as B LEFT JOIN [Data] as A on a.[Customer Number] = b.[Customer Number]

通过这种方式，您可以去掉Where B.[Customer Number] = A.[Customer Number]行(当然是在测试之后(，最终得到一个更明确定义的JOIN。看看底部是什么样子。

第一个Group by [Doc Type]是让你绊倒的东西。

在引用字段时，我个人倾向于始终添加别名，除非我只使用一个简单的oneliner，一个表/视图，即使没有任何名称相似的字段，因为我将来通常会使用重复的名称。即使在那时，我也会尝试添加别名，因为以后如果我决定添加更多的字段/表，这不会让我重新考虑整个事情。

试试这个(如果您没有执行隐式JOIN(：

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

如果您想要一个明确的JOIN(推荐(，请执行此操作：

Select distinct c.[Doc Type], c.[Customer Number], c.CMN, count(c.[Customer Number]) , c.[T] 
From (
Select distinct (A.[Customer Number] & A.[Membership Number]) as CMN, A.[Customer Number] , B.[Doc Type], B.[SumOpenAmount] 
From(
SELECT distinct d.[Doc Type] , d.[Customer Number], Sum(d.[Open Amount]) as T  
FROM Data as d
Where d.[Doc Type] = 'RU'
) as B 
LEFT JOIN [Data] as A on a.[Customer Number] = b.[Customer Number]
) as C
group by C.[Doc Type], C.[Customer Number], C.CMN
having count(C.[Customer Number]) = 1

(删除多余空间(