ACCESS 2010 SQL——在 SELECT TOP 子查询字段上使用 WHERE IN [英] ACCESS 2010 SQL-- using WHERE IN on SELECT TOP subquery field
本文介绍了ACCESS 2010 SQL——在 SELECT TOP 子查询字段上使用 WHERE IN的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想让这个查询告诉我 2013 年支出前 10 名的公司在 2012 年的支出!
I want this query to tell me the spending in 2012 of the companies who were the top 10 spenders in 2013!
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 说它正在运行查询"并且永远不会完成.数据的大小不是问题.
When I try to run it, I get no errors, but Access says it is "running query" and never finishes. The size of the data is not the problem.
这是我找到的最接近的示例,但它并没有真正说明答案:MS Access - WHERE IN 工作,但 WHERE NOT IN 失败
This is the closest example I found, but it doesn't really give light to the answer: MS Access - WHERE IN works, but WHERE NOT IN fails
推荐答案
我怀疑这只是Access的优化器的一个限制.试试这样吧:
I suspect that this is just a limitation of Access's optimizer. Try it like this instead:
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
这篇关于ACCESS 2010 SQL——在 SELECT TOP 子查询字段上使用 WHERE IN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文