MS Access 2010排名查询比较两列的唯一排名 [英] MS Access 2010 Ranking Query comparing two columns for unique ranks

问题描述

对于新手编程，将非常感谢您提供的亲切帮助. 我试图搜索过去3天的Google世界，但没有成功.

Your gracious help on this problem will be very appreciated for programming noob. I've tried to search google world for last 3 days and no luck.

背景: 我正在构建保修数据库的综合分析，在该数据库中，我必须比较两列值来确定产品组的优先级，以便我们的团队进行调查. 这些是; 1.首先按失败次数 2.如果将失败次数"并列，其次是保修成本"

Background: I'm building a comprehensive analysis of warranty database where I have to compare two columns of values to prioritize in product groups for our team needs to investigate. These are; 1. First by Numbers of Failures 2. When Numbers of Failures are tied, then secondly by, Warranty Cost

已采取的步骤: 我使用了子查询技术来获取我想要的东西.但是使用此路由对于完成查询来说太慢了. (11,000行，实际上冻结了我的comp至少5分钟). 因此，我在Google上搜索并发现该用户在进行与我完全相同的线程对话时遇到了与我相同的挑战，幸运地使用INNER JOIN技术解决了问题，而得到了其他专家的回答.是的！

Steps Taken: I've used Subquery techniques to get what I want. But using this route was so slow for query to complete. (11,000 rows, literally freeze my comp at least 5 min). Thus I googled and found this user on following thread talks exact, same challenge as mine, luckily got resolved using INNER JOIN technique instead as answered by other expert. Yay!

请参考 www.pcreview.co.uk/forums /ranking-performance-slow-t2844316.html

好吧，除了.....我的查询与他的查询非常相似，但是我也想比较在失败发生时比较第二列，从而为同一组中的所有人获得唯一的排名. (上一个用户只比较一列，而不必担心联系，这与我的应用程序不同)

Well, except.....my query is very similar to his, but I'd also like to compare 2nd column when failures are tied, thus get unique ranks for all in same group. (Above user only compares one column, and no concerns about ties, unlike me for his/her application)

所需的输出:

表1.

Part Failures Cost Desired-Rank

A    10      $5     1
A    5       $3     3
A    5       $20    2
B    5       $10    2
B    5       $5     3
B    9       $2     1

到目前为止我的查询

SELECT Count(*) AS Rank, dupe.Part, dupe.Failures, dupe.Cost 
FROM [Table1] AS dupe
INNER JOIN [Table1] AS dupe1
ON dupe.Part = dupe1.Part and 
    dupe.Failures <= dupe1.Failures and dupe.Cost<=dupe1.Cost
Group By dupe.Part, dupe.Failures, dupe.Cost;

最近几天，我试图调整and dupe.Cost<=dupe1.Cost部分，但并没有真正了解编程的基础知识. (我主要是通过复制其他代码来学习，并在看到输出后尝试理解它)

I tried to tweak the and dupe.Cost<=dupe1.Cost part for last few days without really knowing the basics of programming. (I'm mostly learning through copying others codes and try to understand it after seeing the output)

请注意，我认为我必须坚持这种JOIN排名技术，因为它极大地减少了查询时间与子查询的时间

Please note, I think I have to stick to this JOIN ranking technique, as it extremely cut down query time vs subquery

非常感谢您的专业知识！！

Your expertise is greatly appreciated!!

推荐答案

如果尝试使用SQL严格执行此操作，则查询可能会变得庞大，因为它需要很多子查询.更好的方法是使用函数.仅当您在Access中使用查询时才有效，而仅在连接时才有效.

If you try to do this strictly using SQL, then your query can get massive as it has to lots of sub queries. A better approach is to use a function. This will only work if your using the query within Access, and not if your just connecting to it.

首先，该函数(放置在模块中):

First, the function (placed into a module):

Function RankMe(Part As String) As Long
   Static LastPart As String
   Static RankNum As Long
   If Part <> LastPart Then
      RankNum = 1
      LastPart = Part
   Else
      RankNum = RankNum + 1
   End If
   RankMe = RankNum
End Function

您将像这样使用它:

SELECT Table1.Part, Table1.Failures, Table1.Cost, RankMe([Part]) AS Rank, Table1.[Desired-Rank], *
FROM Table1
ORDER BY Table1.Part, Table1.Failures DESC , Table1.Cost DESC;

仅供参考，Static语句表示变量将保留其值，并且不会在每次调用该函数时重置.

FYI, the Static statement means that the variable will keep it's value, and not be reset each time the function is called.

这篇关于MS Access 2010排名查询比较两列的唯一排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！