MySQL sql 查询前 10 条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9755087/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql query for top 10 records
提问by mattew
Can anyone help me for a simple SQL query?
谁能帮我做一个简单的 SQL 查询？
This is the scenario: i need to find the runners who took part to more competitions. Competitions have been in different cities and different specialities. The following table shows better what i mean:
这是场景：我需要找到参加更多比赛的跑步者。比赛在不同的城市和不同的专业进行。下表更好地显示了我的意思：
Runner: John White
跑步者：约翰·怀特
-----------------------------------------
| NY | Rome | Madrid | Los Angeles |
------------------------------------------------
| 100m | x | | x | |
------------------------------------------------
| 200m | | x | | x |
------------------------------------------------
| 400m | | | | |
------------------------------------------------
In few words i have a database with the following fields: name, city, spec and i need to find the runner who filled more slots, the runner who participated to more different competitions. In the "spec" field data are 100m or 200m or 300m if he took part respectively to each speciality In the example the result is John White -> 4
简而言之，我有一个包含以下字段的数据库：姓名、城市、规格，我需要找到填补更多空位的跑步者，参加更多不同比赛的跑步者。在“spec”字段中，如果他分别参加了每个专业，则数据为 100m 或 200m 或 300m 在示例中，结果为 John White -> 4
So, how can i make a query to list the 1st ten runners who participated to the major number of competitions? I need a table showing the runner name and the number of competitions he took part.
那么，我如何查询以列出参加主要比赛数量的前十名选手？我需要一个表格，显示跑步者的名字和他参加的比赛数量。
Thanks in advance for your help. If you need more infos just ask.
在此先感谢您的帮助。如果您需要更多信息，请询问。
Bye, Mat
再见，垫
UPDATE: In few words i'd like to know the top 10 list of names that have the maximum number of different combinations of city-specs fields. This one i think is the best logical way to explain my answer.
更新：简而言之，我想知道具有最大城市规格字段不同组合数量的前 10 个名称列表。我认为这是解释我的答案的最佳逻辑方式。
Here is an example of the database:
下面是一个数据库的例子：
name city spec
-----------------------
john NY 100m
john Rome 200m
john Madrid 100m
john Los Ang 200m
mike Rome 200m
mike Rome 100m
The "top 10 list" i'm looking for will be the following:
我正在寻找的“前 10 名名单”如下：
john 4
mike 2
I hope that now it is all ok. Bye!
我希望现在一切都好。再见！
回答by Nikola Markovinovi?
Perhaps you are looking for something like this:
也许你正在寻找这样的东西：
select name, count(distinct city) as attended
from competitions
group by name
order by 2 desc
limit 10;
This query returns a competitor and number of events he participated in, excluding duplicates (100 and 200 meters at the same event). If you want to count all appearances remove distinctkeyword, and in case you want to show versatility list replace citywith distinct speciality. Query works by counting all rows that belong to the same name, ordering by that count descendingly and limiting output to ten rows.
此查询返回参赛者和他参加的赛事数量，不包括重复项（同一赛事的 100 米和 200 米）。如果要计算所有出现次数，请删除不同的关键字，如果要显示多功能性列表，请用不同的专业替换城市。查询的工作原理是计算属于同一名称的所有行，按该计数降序排序并将输出限制为 10 行。
UPDATE: I suppose that OP needs a list of top 10 competitors by appearance. So, each record for competitor counts as one:
更新：我想 OP 需要一份外观排名前 10 的竞争对手的列表。因此，参赛者的每条记录都算作一个：
select name, count(name) as attended
from competitions
group by name
order by 2 desc
limit 10;
Will give 4 for Mr. White, and 12 for anyone who attended all 4 competitions in all 3 disciplines.
将给怀特先生 4 分，给参加所有 3 个学科的所有 4 场比赛的任何人打 12 分。
回答by Kyle Macey
Wouldn't either the City or the Event be irrelevant? Couldn't you just take a count of its association in a single city, seeing as how it looks like every event association has a city and vise versa? I'm assuming there's another join table or two somewhere as well. But I'd select the count of how many times the person appears in that join.
城市或事件不会无关紧要吗？难道你不能只计算一个城市的协会，看看每个事件协会都有一个城市，反之亦然？我假设某处还有另一个或两个连接表。但我会选择该人在该连接中出现的次数。
SELECT COUNT(*)
FROM city_runner
WHERE runnerid = ? #The runner's id? You gave so little for me to work with...
Then use your application language to determine whose is greatest...
然后使用您的应用程序语言来确定谁是最大的...
回答by Martijn
That really very much depends on how your data is structured. I can imagine you have a table with
这在很大程度上取决于您的数据的结构。我可以想象你有一张桌子
city|name|spec
city|name|spec
In that case, your solution is trivial:
在这种情况下，您的解决方案很简单：
SELECT TOP 10 name, total_races
FROM (
SELECT COUNT(*) as total_races, name
FROM thattable
GROUP BY name
) as t1
ORDER BY total_races DESC
If your data is stored differently, the query will be different, obviously
如果你的数据存储方式不同，查询就会不同，很明显
Edit: It's really the same thing with your table. I edited the query to have nameinstead of runner, but it's still the same answer
编辑：这和你的桌子是一样的。我编辑了查询name而不是runner，但它仍然是相同的答案
回答by Faizal R
This will work To find the nth maximum number
这将工作以找到第n 个最大数字
SELECT TOP nth_largest_no * FROM Products Order by price desc
For Example
例如
SELECT TOP 10 * FROM Products Order by price desc;
