SQL - 没有顺序的顶级结果的子查询 [英] SQL - subqueries for top result without order by

问题描述

(对不起标题，想不出怎么解释)

(Sorry about the title, couldn't think of how to explain it)

所以我有一个奥林匹克数据库，基本布局是有一个包含competitionnum、givenname和familyname的竞争对手表(其他列不是必需的)还有一个包含competitionnum和place的结果表(介于1和8).

So I have an Olympic database, the basic layout is that there's a competitors table with competitornum, givenname, and familyname (other columns aren't necessary for this) There's also a results table with competitornum, and place (between 1 and 8).

我正在尝试获取名字和姓氏以及金牌、银牌和铜牌的总数(位置 = 1、2 或 3)

I'm trying to get the givenname and familyname and total number of gold, silver, and bronze medals (place = 1, 2 or 3)

它还需要只显示奖牌数最多的结果，而且所有这些都不需要使用 Order By 子句...

我之前问过这个问题，但发现我忘了说一些事情，但在加粗部分之前的上一个答案是:

I asked this question before but realised I forgot to say some things, but the previous answer before the bold part was added was:

SELECT c.Givenname, c.Familyname, COUNT(r.places) AS TotalPlaces
FROM Competitors c INNER JOIN Results r
    ON r.Competitornum = c.Competitornum
WHERE r.place IN (1,2,3)
GROUP BY c.Givenname, c.Familyname

我认为它需要另一个像

AND TotalPlaces = (SELECT MAX(TotalPlaces))

但是当别名位于子查询之上时，我不确定如何在子查询中使用别名...

but I'm not sure how to use an alias in a subquery when it's above the subquery...

感谢所有帮助，谢谢！

关于我的作业的官方问题(我想不出答案，我真的试过了，这就是我在这里的原因):

The official question on my assignment (I can't figure out the answer, I've really tried, that's why I'm here):

哪位选手获得的奖牌数最多(包括金牌、银牌和铜牌在内)?列出他们的姓氏和他们的奖牌总数(仅).

Which competitor(s) got the largest number of medals (counting gold, silver and bronze all together)? List their given and family names and the total number of their medals (only).

警告:您的解决方案不能假设竞争对手的名字总是不同的请勿在此查询的任何部分使用 ORDER BY 子句.

Warning: your solution must not assume that competitor names are always different Do not use an ORDER BY clause, in any part of this query.

推荐答案

最终答案(感谢 John Woo 和 lc.)为将来遇到此问题的任何人粘贴此内容:

The final answer (thanks to John Woo and lc.) Pasted this here for anyone that comes across this question in the future:

SELECT c.Givenname, c.Familyname, COUNT(r.place) AS TotalPlaces
FROM Competitors c 
INNER JOIN Results r ON r.Competitornum = c.Competitornum
WHERE r.place IN (1,2,3)
GROUP BY c.competitornum, c.Givenname, c.Familyname
HAVING COUNT(r.place) = 
            (
                SELECT MAX(TotalPlaces)
                FROM
                (
                    SELECT COUNT(r.place) AS TotalPlaces
                    FROM Competitors c 
                    INNER JOIN Results r ON r.Competitornum = c.Competitornum
                    WHERE r.place IN (1,2,3)
                    GROUP BY c.competitornum, c.Givenname, c.Familyname
                )
            )

这篇关于SQL - 没有顺序的顶级结果的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！