如何从表中选择最大5个值?

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.

谢谢。

8 个解决方案

#1

This should do it

这个应该怎么做

SELECT id, num1 + num2 AS total FROM table1 ORDER BY num1 + num2 DESC LIMIT 5

#2

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列(这是操作符需要的)。

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 ORDER BY num1+num2 DESC LIMIT 5)

#3

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.

这将帮助你。

#4

You can Select Top 5 Records As

您可以选择Top 5 Records As

Select Top 5 RestaurantID, RestaurantName,Address,ProfileImage from Restaurant_Details order by DisplayRating Desc

#5

Depending on your sql dialect:

根据您的sql方言:

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

#6

Use join instead

使用连接

select * from  table1 as t1 , 
table2 as t2 
where t1.id = t2.id 
order by t2.id desc limit 5 ;

#7

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

#8

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个值(表2可以更小):

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 )

#1