Access中的子查询排名 [英] Ranking by Subquery in Access

问题描述

嗨


我写了一个查询来获取我的分数排名，但我想将这些分数排在所有分数的子集中。这是我的例子


Class_ID Dog_Handler_ID俱乐部得分排名

1 12 Wallingford 32 4

1 13 Andover 95 8
1 14 Wallingford 40 5

2 12 Wallingford 93 7

2 14 Wallingford 21 3

3 12 Wallingford 92 6

3 13 Andover 14 2

3 14 Wallingford 14 2


我想要的是这个

Class_ID Dog_Handler_ID分数得分排名

1 12 Wallingford 32 1

1 13 Andover 95 3

1 14 Wallingford 40 2

2 12 Wallingford 93 2

2 14 Wallingford 21 1

3 12 Wallingford 92 3

3 13 Andover 14 1

3 14 Wallingford 14 1


如果我可以按class_ID订购然后排名t帽子也是工厂。


到目前为止我的sql是


SELECT Res1.Class_ID，Res1.Dog_Handler_ID，Res1.Club，Res1。分数，（从Q_Results中选择Count（*）Where [Score]< [Res1]。[Score] +1;）AS Rank

来自Q_Results AS Res1;


非常感谢

Hi

I have written a query in access to rank my scores, but I would like to rank these scores within a subset of all the scores. Here is my example

Class_ID Dog_Handler_ID Club Score Rank
1 12 Wallingford 32 4
1 13 Andover 95 8
1 14 Wallingford 40 5
2 12 Wallingford 93 7
2 14 Wallingford 21 3
3 12 Wallingford 92 6
3 13 Andover 14 2
3 14 Wallingford 14 2

What I would like is this

Class_ID Dog_Handler_ID Club Score Rank
1 12 Wallingford 32 1
1 13 Andover 95 3
1 14 Wallingford 40 2
2 12 Wallingford 93 2
2 14 Wallingford 21 1
3 12 Wallingford 92 3
3 13 Andover 14 1
3 14 Wallingford 14 1


If I could order by class_ID and then Rank that would be fab too.

My sql so far is

SELECT Res1.Class_ID, Res1.Dog_Handler_ID, Res1.Club, Res1.Score, (Select Count(*) from Q_Results Where [Score] < [Res1].[Score]+1;) AS Rank
FROM Q_Results AS Res1;

Many thanks

推荐答案

这不行吗？

Wouldn''t this work?

展开 | 选择 | Wrap | 行号

no - 当我使用该查询并运行时我得到一个对话框要求输入参数值等级

no - when I use that query and run it I get a dialogue box asking to Enter Parameter Value Rank

你能发布你的Q_Results的SQL吗？


编辑：另外，怎么样您的示例查询的Rank字段在每个查询中都有不同的值？


再次编辑：我想我如果每个班级的最低分数排名最高，请跟随吗？

Can you post your SQL for Q_Results please?

Also, how come the Rank field of your example queries has different values in each query?

EDIT AGAIN: I think I follow, if the lowest score in each class is ranked highest?

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