其他聚合查询中的聚合子查询

Question

我使用Microsoft和Excel，也使用ADODB连接将工作表作为数据库处理。关于使用聚合子查询的SQL聚合查询，我有一个问题。问题是我不能这样使用它，因为它正在抛出错误，而且我不知道如何改变它。

SQL查询：

Select inv.[Region], inv.[Org Name], inv.[Bill To Customer Number], inv.[Bill To Customer Name], 
SUM(inv.[AR Global Total Amount]) as "Amount Invoiced", 
COUNT(pay.[Sales Invoice Number]) as "Count Invoices", 
SUM(inv.[AR Global Total Amount]*(inv.[Payment Due Fiscal Date]-inv.[Invoiced Fiscal Date])) as "Sum of Terms Mult", 
SUM(inv.[AR Global Total Amount]*(pay.GL_Min-inv.[Invoiced Fiscal Date])) as "Sum of Pay Days Mult", 
SUM(inv.[AR Global Total Amount]*(pay.GL_Min-inv.[Payment Due Fiscal Date])) as "Sum of Days Late Mult" 
FROM 
(
    Select * 
    From [Data$] as inv 
    WHERE [AR Transaction Sub Type] IN ('Inv', 'Inv-T')
) a,
(
    Select [Region], [Org Name], [Bill To Customer Number], [Bill To Customer Name], [Sales Invoice Number], Min([GL Fiscal Date]) as GL_Min 
    FROM [Data$] as pay 
    WHERE [AR Transaction Sub Type] IN ('Cash', 'Cash-T') 
    GROUP BY [Region], [Org Name], [Bill To Customer Number], [Bill To Customer Name], [Sales Invoice Number]
) t  

WHERE inv.[Sales Invoice Number] = pay.[Sales Invoice Number] AND inv.[Org Name] = pay.[Org Name] AND inv.[AR Global Total Amount]>0 
GROUP BY inv.[Region], inv.[Org Name], inv.[Bill To Customer Number], inv.[Bill To Customer Name] 
ORDER BY SUM(inv.[AR Global Total Amount]) DESC

问题是在第二个子查询上，在这个查询中，我试图捕获min日期。

有人能告诉我一个正确的语法吗？

谢谢!

Answer 1

除了子查询别名之外，您的查询似乎是正确的。你能试试这个吗？

Select inv.[Region], inv.[Org Name], inv.[Bill To Customer Number], inv.[Bill To Customer Name], 
SUM(inv.[AR Global Total Amount]) as "Amount Invoiced", 
COUNT(pay.[Sales Invoice Number]) as "Count Invoices", 
SUM(inv.[AR Global Total Amount]*(inv.[Payment Due Fiscal Date]-inv.[Invoiced Fiscal Date])) as "Sum of Terms Mult", 
SUM(inv.[AR Global Total Amount]*(pay.GL_Min-inv.[Invoiced Fiscal Date])) as "Sum of Pay Days Mult", 
SUM(inv.[AR Global Total Amount]*(pay.GL_Min-inv.[Payment Due Fiscal Date])) as "Sum of Days Late Mult" 
FROM 
(
    Select * 
    From [Data$] as inv 
    WHERE [AR Transaction Sub Type] IN ('Inv', 'Inv-T')
) inv,
(
    Select [Region], [Org Name], [Bill To Customer Number], [Bill To Customer Name], [Sales Invoice Number], Min([GL Fiscal Date]) as GL_Min 
    FROM [Data$] as pay 
    WHERE [AR Transaction Sub Type] IN ('Cash', 'Cash-T') 
    GROUP BY [Region], [Org Name], [Bill To Customer Number], [Bill To Customer Name], [Sales Invoice Number]
) pay  

WHERE inv.[Sales Invoice Number] = pay.[Sales Invoice Number] AND inv.[Org Name] = pay.[Org Name] AND inv.[AR Global Total Amount]>0 
GROUP BY inv.[Region], inv.[Org Name], inv.[Bill To Customer Number], inv.[Bill To Customer Name] 
ORDER BY SUM(inv.[AR Global Total Amount]) DESC