我希望这个查询能告诉我2013年支出前十的公司2012年的支出情况!
SELECT [Company],
Sum([SPENDING])
FROM [Data]
WHERE [Company] IN (
SELECT TOP 10 [Company]
FROM [Data]
WHERE [Year] IN ("2013")
GROUP BY Company
ORDER BY Sum([SPENDING]) DESC
)
AND [Year] IN ("2012")
GROUP BY Company
;
当我尝试运行它时,我没有得到任何错误,但Access说它是"运行查询",永远不会完成。数据的大小不是问题所在。
这是我发现的最接近的例子,但它并没有真正给出答案:MS Access-WHERE IN有效,但WHERE NOT IN失败
我怀疑这只是Access优化器的一个限制。不如这样试试:
SELECT d.[Company],
Sum(d.[SPENDING])
FROM [Data] As d
INNER JOIN (
SELECT TOP 10 [Company]
FROM [Data]
WHERE [Year] IN ("2013")
GROUP BY Company
ORDER BY Sum([SPENDING]) DESC
) As t ON t.Company = d.Company
WHERE d.[Year] IN ("2012")
GROUP BY d.Company