MS Access 2010 SQL按组性能查询的前N个查询 [英] MS Access 2010 SQL Top N query by group performance issue
问题描述
我有2个表,一个表带有项目量度(MASE_Table),一个带有项目属性(SKU).项目属性可以是Planner或ABCByPick或XYZ或其他属性.
I have 2 tables, one with a measure of items (MASE_Table) and one with item attributes (SKU). The item attributes can be the Planner or ABCByPick or XYZ or other attributes.
通过查询,我想通过属性ABCByPick获取该度量的前10个项目(AvgOfScaledError).
With a query I want to get the top 10 items of this measure (AvgOfScaledError) by the attribute ABCByPick.
我想使查询尽可能通用,以便我可以将其复制并用属性计划器替换ABCByPick
I would like to make the query so generic as possible so that I can copy it and replace the ABCByPick with the attribute planner
我在这里尝试了一些技巧.只要MASE_Table中的记录数少于1000,我就可以执行查询.如果我想使用MASE_Table中的8000条记录来运行它,查询将不再产生结果(没有错误消息,但是超时).
I have found tips in here which I've tried. As Long as the number of records in the MASE_Table are less 1000 I can exectute the query. If I want to run it with 8000 records in MASE_Table the query is not anymore producing a result (no error message but time-out).
这是我查询的ABCByPick的前10名:
Here is my query for the top 10 of ABCByPick:
SELECT s2.ABCByPick
,m2.DmdUnit
,ROUND(m2.AvgOfScaledError, 2) AS MASE
FROM MASE_Table AS m2
INNER JOIN SKU AS s2
ON ( m2.Loc = s2.Loc )
AND ( m2.DmdUnit = s2.Item )
WHERE m2.AvgOfScaledError IN ( SELECT TOP 10 m1.AvgOfScaledError
FROM (
SELECT m1.Loc
,m1.DmdUnit
,s1.ABCByPick
,m1.AvgOfScaledError
FROM MASE_Table AS m1
INNER JOIN SKU AS s1
ON m1.Loc = s1.Loc
AND m1.DmdUnit = s1.Item
)
WHERE s1.ABCByPick = s2.ABCByPick
ORDER BY m1.AvgOfScaledError DESC )
ORDER BY s2.ABCByPick
,m2.AvgOfScaledError DESC;
MASE_Table上的主键是Loc和DmdUnit
SKU上的主键是Loc和Item
Primary keys on MASE_Table are Loc and DmdUnit
Primary keys on SKU are Loc and Item
推荐答案
这应该有所帮助:
SELECT s2.ABCByPick,
m2.DmdUnit,
ROUND(m2.AvgOfScaledError, 2) AS MASE
FROM MASE_Table AS m2
INNER JOIN SKU AS s2 ON m2.Loc = s2.Loc
AND m2.DmdUnit = s2.Item
WHERE m2.AvgOfScaledError IN (
SELECT TOP 10 m1.AvgOfScaledError
FROM MASE_Table AS m1
INNER JOIN SKU AS s1 ON m1.Loc = s1.Loc
AND m1.DmdUnit = s1.Item
WHERE s1.ABCByPick = s2.ABCByPick
ORDER BY m1.AvgOfScaledError DESC
)
ORDER BY s2.ABCByPick,
m2.AvgOfScaledError DESC;
这篇关于MS Access 2010 SQL按组性能查询的前N个查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!