使排名查询高效 [英] Making the ranking query efficient
问题描述
我在ACCESS 2010中使用以下代码对表中的〜30000行进行排名.但是,排名大约需要15分钟(我必须再进行5列才能完成排名,而这很容易花费一个多小时).我需要这些列以进行进一步处理,因此尝试创建一个新表.我也尝试过追加和更新现有表.一切都需要相同的时间.我在这里遗漏了一些明显的东西可以使它更快地工作吗?
I am using the following code in ACCESS 2010 to rank ~30000 rows from a table. However it takes around 15 minutes to do the ranking (I have to do this for 5 more columns and it could easily take more than an hour). I need these columns for further processing and hence tried creating a new table. I have also tried appending and updating to existing tables. Everything takes the same time. Am I missing something obvious here that could make it work faster?
SELECT MasterTable.Sales, (SELECT Count(*)+1 as HowMany
From MasterTable AS Dupe
WHERE Dupe.Sales > MasterTable.Sales) AS SalesRank INTO tableRank
FROM MasterTable;
SELECT MasterTable.Sales, (SELECT Count(*)+1 as HowMany
From MasterTable AS Dupe
WHERE Dupe.Sales > MasterTable.Sales) AS SalesRank INTO tableRank
FROM MasterTable;
MasterTable
的详细信息:
11列. 1个主键(文本). 5个参数(Sales
SalesQty
Profit
Hits
Cost
,全数字). 5个参数的等级(例如SalesRank
等)
30,000行
Details on the MasterTable
:
11 columns. 1 primary key (Text). 5 parameters (Sales
SalesQty
Profit
Hits
Cost
, all numeric). Ranks of 5 parameters(like SalesRank
and so on)
30,000 rows
推荐答案
您尝试使用分组左联接吗?
Did you try a left join with grouping?
SELECT
m1.Sales,
COUNT(*) AS HowMany
FROM
MasterTable AS m1
LEFT JOIN MasterTable m2 ON m1.Sales <= m2.Sales
GROUP BY
m1.Sales
这篇关于使排名查询高效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!