排名查询中TOP和ORDER BY之间的矛盾 [英] Contradiction between TOP and ORDER BY in a ranking query

问题描述

大家好。


好​​吧，我有运动员，比赛，分数的田径数据库

桌子。


我有一个排名查询，我回到了

比赛的运动员名单和得分排除的分数。


我也想成为能够：

1）选择顶部< n>最佳分数[= SELECT TOP< n>]

2）按照

比赛发生日期的顺序查看多个等分[= ORDER BY Score，竞赛日期]

3）选择TOP< n>限制，查看超过< n>如果

（n + 1）th，（n + 2）th，...（n + m）额外记录的得分与

相同，则得分最高< n> th。


现在，实际上TOP允许我查看超过< n>运动员在案件[3]

出现，但这只是在我按ORDER BY得分时，如果我通过

竞赛日期订购，则不会出现额外的记录。 />

所以，目前，我能够返回符合要求的查询

[1]和[2]：

SELECT TOP< n> AtheteName，CompetitionDate，CompetitionPlace，Score

分数INNER JOIN运动员？ INNER JOIN比赛

按分数排序，竞赛日期


？或符合要求的人。 [1]和[3]：

SELECT TOP< n> AtheteName，CompetitionDate，CompetitionPlace，Score

分数INNER JOIN运动员？ INNER JOIN比赛

按分数排序


？或满足req [2]和[3]的那个：

SELECT AtheteName ，比赛日期，比赛地点，分数

分数INNER JOIN运动员？ INNER JOIN比赛

按分数排序，竞赛日期


有没有办法建立一个允许我满足要求的查询

[1] [2] [3]当然？！？


非常感谢。


问候，

艾琳

Hi all again,

Well, I have my Athletics database with Athletes, Competitions, Scores
tables.

I have a ranking query where I get back the list of the
competitions-athletes and scores opportunely sorted.

I also want to be able to:
1) Select just the top <n> best scores [= SELECT TOP <n>]
2) View the multiple equal-score in order of date in which the
competition occurred [=ORDER BY Score, CompetitionDate]
3) Selecting a TOP <n> limit, view more than <n> best scores if the
(n+1)th, (n+2)th,..(n+m)th extra records have the same score than the
<n>th.

Now, actually TOP allows me view more than <n> athletes in case [3]
occurs, but this works just if I ORDER BY Score only, if I order by
CompetitionDate too, extra records do not appear.

So, at the moment, I am able to return a query that meets requirements
[1] And [2]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes ? INNER JOIN Competitions
ORDER BY Score,CompetitionDate

?or one that meets req. [1] and [3]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes ? INNER JOIN Competitions
ORDER BY Score

?or one that meets req [2] and [3]:
SELECT AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes ? INNER JOIN Competitions
ORDER BY Score, CompetitionDate

Is there a way to build a query that allows me to meet requirements
[1][2][3] contemporarly ?!?

Thanks a lot.

Regards,
Irene

推荐答案

让我无法回复！


TC

"艾琳" <它************ @ hotmail.com>在消息中写道

新闻：cc ************************** @ posting.google.c om ...

Count me out of replying!

TC
"Irene" <it************@hotmail.com> wrote in message
news:cc**************************@posting.google.c om...

大家好，

好吧，我有运动员，比赛，分数表的田径数据库。

我有一个排名查询我在哪里取回了比赛名单 - 运动员和得分进行了适当分类。

我也希望能够：
1）选择顶部< N'GT;最佳分数[= SELECT TOP< n>]
2）按照比赛日期的顺序查看多个等分[= ORDER BY Score，CompetitionDate]
3）选择TOP< n>限制，查看超过< n>如果第（n + 1），第（n + 2），第（n + m）个额外记录具有与第n / n相同的分数，则得分最高。

现在，实际上TOP允许我查看超过< n>运动员如果发生[3]
，但这只是在我按ORDER BY得分的情况下工作，如果我也通过竞赛日期订购，则不会出现额外的记录。

所以，目前，我能够返回符合要求的查询
[1]和[2]：
SELECT TOP< n> AtheteName，CompetitionDate，CompetitionPlace，Score
得分INNER JOIN运动员。 INNER JOIN比赛按比例排序，比赛日期

。或者符合要求的比赛。 [1]和[3]：
SELECT TOP< n> AtheteName，CompetitionDate，CompetitionPlace，Score
得分INNER JOIN运动员。 INNER JOIN比赛按比例分数

。或者符合要求[2]和[3]：
SELECT AtheteName，CompetitionDate，CompetitionPlace，Score
分数INNER加入运动员。 INNER JOIN比赛按比例排序，竞赛日期

有没有办法建立一个允许我满足要求的查询
[1] [2] [3]同时？很感谢。

问候，艾琳

Hi all again,

Well, I have my Athletics database with Athletes, Competitions, Scores
tables.

I have a ranking query where I get back the list of the
competitions-athletes and scores opportunely sorted.

I also want to be able to:
1) Select just the top <n> best scores [= SELECT TOP <n>]
2) View the multiple equal-score in order of date in which the
competition occurred [=ORDER BY Score, CompetitionDate]
3) Selecting a TOP <n> limit, view more than <n> best scores if the
(n+1)th, (n+2)th,..(n+m)th extra records have the same score than the
<n>th.

Now, actually TOP allows me view more than <n> athletes in case [3]
occurs, but this works just if I ORDER BY Score only, if I order by
CompetitionDate too, extra records do not appear.

So, at the moment, I am able to return a query that meets requirements
[1] And [2]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score,CompetitionDate

.or one that meets req. [1] and [3]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score

.or one that meets req [2] and [3]:
SELECT AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score, CompetitionDate

Is there a way to build a query that allows me to meet requirements
[1][2][3] contemporarly ?!?

Thanks a lot.

Regards,
Irene

请不要回复您不打算回答的消息。人们

扫描新闻组以帮助其他人会认为某人已经发布了答案。


-

John Viescas，作者

" Microsoft Office Access 2003 Inside Out" （即将推出）

运行Microsoft Access 2000

对于凡人的SQL查询
http://www.viescas.com/

（自1993年以来的Microsoft Access MVP）

TC < a@b.c.d>在消息新闻中写道：1065004691.679397@teuthos ...

Please do not reply to a message that you do not intend to answer. Folks
scanning the newsgroups to help others will assume that someone has already
posted an answer.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"TC" <a@b.c.d> wrote in message news:1065004691.679397@teuthos...

算我不要回复了！

$

" Irene" <它************ @ hotmail.com>在消息中写道
新闻：cc ************************** @ posting.google.c om ...

Count me out of replying!

TC
"Irene" <it************@hotmail.com> wrote in message
news:cc**************************@posting.google.c om...

大家好，

好吧，我有运动员，比赛，分数表的田径数据库。

我有一个排名查询，我在哪里取回比赛名单 - 运动员和得分进行适当排序。

我也希望能够：
1）选择顶部< n>最佳分数[= SELECT TOP< n>]
2）按照比赛日期的顺序查看多个等分[= ORDER BY Score，CompetitionDate]
3）选择TOP< n>限制，查看超过< n>如果第（n + 1），第（n + 2），第（n + m）个额外记录具有与第n / n相同的分数，则得分最高。

现在，实际上TOP允许我查看超过< n>运动员如果发生[3]
，但这只是在我按ORDER BY得分的情况下工作，如果我也通过竞赛日期订购，则不会出现额外的记录。

所以，目前，我能够返回符合要求的查询
[1]和[2]：
SELECT TOP< n> AtheteName，CompetitionDate，CompetitionPlace，Score
得分INNER JOIN运动员。 INNER JOIN比赛按比例排序，比赛日期

。或者符合要求的比赛。 [1]和[3]：
SELECT TOP< n> AtheteName，CompetitionDate，CompetitionPlace，Score
得分INNER JOIN运动员。 INNER JOIN比赛按比例分数

。或者符合要求[2]和[3]：
SELECT AtheteName，CompetitionDate，CompetitionPlace，Score
分数INNER加入运动员。 INNER JOIN比赛按比例排序，竞赛日期

有没有办法建立一个允许我满足要求的查询
[1] [2] [3]同时？很感谢。

问候，艾琳

Hi all again,

Well, I have my Athletics database with Athletes, Competitions, Scores
tables.

I have a ranking query where I get back the list of the
competitions-athletes and scores opportunely sorted.

I also want to be able to:
1) Select just the top <n> best scores [= SELECT TOP <n>]
2) View the multiple equal-score in order of date in which the
competition occurred [=ORDER BY Score, CompetitionDate]
3) Selecting a TOP <n> limit, view more than <n> best scores if the
(n+1)th, (n+2)th,..(n+m)th extra records have the same score than the
<n>th.

Now, actually TOP allows me view more than <n> athletes in case [3]
occurs, but this works just if I ORDER BY Score only, if I order by
CompetitionDate too, extra records do not appear.

So, at the moment, I am able to return a query that meets requirements
[1] And [2]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score,CompetitionDate

.or one that meets req. [1] and [3]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score

.or one that meets req [2] and [3]:
SELECT AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score, CompetitionDate

Is there a way to build a query that allows me to meet requirements
[1][2][3] contemporarly ?!?

Thanks a lot.

Regards,
Irene

Irene-


所以，你想要TOP< n>得分，然后列出所有那些得分为

的记录，对吧？首先，找到不同的分数：


qryDistinctScore：

SELECT DISTINCT分数得分


现在，找到顶部< n>：


qryTopScores：

SELECT TOP< n>分数

来自qryDistinctScore

ORDER BY得分描述


现在，解决您的问题：


SELECT AtheteName，CompetitionDate，CompetitionPlace，Score

分数INNER JOIN运动员。 INNER JOIN比赛

分数在哪里
（从qryTopScores中选择分数）

按分数排序，竞赛日期


-

John Viescas，作者

" Microsoft Office Access 2003 Inside Out" （即将推出）

运行Microsoft Access 2000

对于凡人的SQL查询
http://www.viescas.com/

（自1993年以来的Microsoft Access MVP）

艾琳 <它************ @ hotmail.com>在消息中写道

新闻：cc ************************** @ posting.google.c om ...

Irene-

So, you want the TOP <n> scores, and then list all the records that have
that score, right? First, find the distinct scores:

qryDistinctScore:
SELECT DISTINCT Score FROM Scores

Now, find the top <n>:

qryTopScores:
SELECT TOP <n> Score
FROM qryDistinctScore
ORDER BY Score Desc

Now, solve your problem:

SELECT AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
WHERE Score IN
(SELECT Score FROM qryTopScores)
ORDER BY Score, CompetitionDate

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"Irene" <it************@hotmail.com> wrote in message
news:cc**************************@posting.google.c om...

大家好，

好吧，我有运动员，比赛，分数表的田径数据库。

我有一个排名查询我在哪里取回了比赛名单 - 运动员和得分进行了适当分类。

我也希望能够：
1）选择顶部< N'GT;最佳分数[= SELECT TOP< n>]
2）按照比赛日期的顺序查看多个等分[= ORDER BY Score，CompetitionDate]
3）选择TOP< n>限制，查看超过< n>如果第（n + 1），第（n + 2），第（n + m）个额外记录具有与第n / n相同的分数，则得分最高。

现在，实际上TOP允许我查看超过< n>运动员如果发生[3]
，但这只是在我按ORDER BY得分的情况下工作，如果我也通过竞赛日期订购，则不会出现额外的记录。

所以，目前，我能够返回符合要求的查询
[1]和[2]：
SELECT TOP< n> AtheteName，CompetitionDate，CompetitionPlace，Score
得分INNER JOIN运动员。 INNER JOIN比赛按比例排序，比赛日期

。或者符合要求的比赛。 [1]和[3]：
SELECT TOP< n> AtheteName，CompetitionDate，CompetitionPlace，Score
得分INNER JOIN运动员。 INNER JOIN比赛按比例分数

。或者符合要求[2]和[3]：
SELECT AtheteName，CompetitionDate，CompetitionPlace，Score
分数INNER加入运动员。 INNER JOIN比赛按比例排序，竞赛日期

有没有办法建立一个允许我满足要求的查询
[1] [2] [3]同时？很感谢。

问候，艾琳

Hi all again,

Well, I have my Athletics database with Athletes, Competitions, Scores
tables.

I have a ranking query where I get back the list of the
competitions-athletes and scores opportunely sorted.

I also want to be able to:
1) Select just the top <n> best scores [= SELECT TOP <n>]
2) View the multiple equal-score in order of date in which the
competition occurred [=ORDER BY Score, CompetitionDate]
3) Selecting a TOP <n> limit, view more than <n> best scores if the
(n+1)th, (n+2)th,..(n+m)th extra records have the same score than the
<n>th.

Now, actually TOP allows me view more than <n> athletes in case [3]
occurs, but this works just if I ORDER BY Score only, if I order by
CompetitionDate too, extra records do not appear.

So, at the moment, I am able to return a query that meets requirements
[1] And [2]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score,CompetitionDate

.or one that meets req. [1] and [3]:
SELECT TOP <n> AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score

.or one that meets req [2] and [3]:
SELECT AtheteName, CompetitionDate, CompetitionPlace, Score
Scores INNER JOIN Athletes . INNER JOIN Competitions
ORDER BY Score, CompetitionDate

Is there a way to build a query that allows me to meet requirements
[1][2][3] contemporarly ?!?

Thanks a lot.

Regards,
Irene

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