MySQL SQL 如何从表中选择最多 5 个值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26354221/
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 How to select Max 5 values from table?
提问by gzml
In my database I have two tables and want to select highest 5 values from table but I only get first highest value cannot get more than one value.
在我的数据库中，我有两个表，想从表中选择最高的 5 个值，但我只获得第一个最高值，不能获得多个值。
Here is my SQL,
这是我的 SQL，
SELECT * FROM table1 WHERE id=(SELECT id, MAX(num1+num2) FROM table2 limit 5)
How can I get first top 5 highest values?
如何获得前 5 个最高值？
Thanks.
谢谢。
回答by fallenland
This should do it
这应该做
SELECT id, num1 + num2 AS total FROM table1 ORDER BY num1 + num2 DESC LIMIT 5
回答by Tushar Gite
You can write like this.
你可以这样写。
SELECT top 5 * FROM table1 WHERE id IN (SELECT id,MAX(num1+num2) FROM table2) ORDER BY id DESC
This will help you.
这会帮助你。
回答by mithlesh kaushik
Use joininstead
使用join替代
select * from table1 as t1 ,
table2 as t2
where t1.id = t2.id
order by t2.id desc limit 5 ;
回答by Vaibhav Chopade
You can Select Top 5 Records As
您可以选择前 5 条记录作为
Select Top 5 RestaurantID, RestaurantName,Address,ProfileImage from Restaurant_Details order by DisplayRating Desc
回答by mochalygin
You need to use IN operator in your query. So, the subquery must return only 1 column (this is need for IN operator working).
您需要在查询中使用 IN 运算符。因此，子查询必须仅返回 1 列（这是 IN 运算符工作所必需的）。
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 ORDER BY num1+num2 DESC LIMIT 5)
回答by realbart
Depending on your sql dialect:
根据您的 sql 方言：
MySQL:
MySQL：
SELECT id, num1 + num2 FROM table1 ORDER BY num1 + num2 DESC LIMIT 5
SQL Server:
SQL 服务器：
SELECT TOP 5 id, num1 + num2 FROM table1 ORDER BY num1 + num2 DESC
Oracle:
甲骨文：
SELECT id, num1 + num2 FROM table1 WHERE ROWNUM <= 5 ORDER BY num1 + num2
回答by Hara Prasad
You need to use IN operator in your query. So, the subquery must return only 1 column
您需要在查询中使用 IN 运算符。因此，子查询必须只返回 1 列
SELECT * FROM table1 WHERE id IN (SELECT MAX(num1+num2) FROM table2) ORDER BY id DESC limit 5
回答by ManuelJE
Keep in mind that MAX() is an aggregation function, you'll always get only one row in response when you put it in the field list.
请记住，MAX() 是一个聚合函数，当您将其放入字段列表时，您将始终只得到一行响应。
You use ORDER BY and LIMIT in the inner query to fetch 5 values at most (table2 could be smaller):
您在内部查询中使用 ORDER BY 和 LIMIT 最多获取 5 个值（table2 可以更小）：
SELECT id FROM table2 ORDER BY num1+num2 DESC LIMIT 5
Then you pick the other information wrapping that query with an IN (...)
然后你选择用 IN (...)
SELECT * FROM table1 WHERE id IN ( SELECT id, num1+num2 FROM table2 ORDER BY num1+num2 LIMIT 5 )
