ACCESS 2010 SQL-在SELECT TOP子查询字段上使用WHERE IN [英] ACCESS 2010 SQL-- using WHERE IN on SELECT TOP subquery field

问题描述

我想通过此查询告诉我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.

这是我找到的最接近的示例，但实际上并没有给出答案:

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屋！