从MySQL的表的一部分中选择最小值和最大值

If I want to select min and max values from a whole table I can use this:

如果我想从整个表中选择最小值和最大值,我可以使用:

SELECT min(price) as min_price, max(price) as max_price FROM `prices`

But how to select min and max values from just a part of a table? For example, I have 30 rows in a table. I want to select min and max values from first ten rows, then from second ten rows and then form the last 10.

但是如何从表的一部分中选择最小值和最大值?例如,我在表中有30行。我想从前十行,然后从后十行中选择最小值和最大值,然后形成最后10行。

I've tried something like

我尝试过类似的东西

SELECT min(price) as min_price, max(price) as max_price FROM `prices` LIMIT 0,10

but this did not work.

但这没用。

How can I solve this problem with minimum queries?

如何以最少的查询解决此问题?

2 个解决方案

#1

SELECT MIN(PRICE) AS MinPrice, MAX(PRICE) AS MaxPrice FROM (SELECT PRICE FROM PRICES LIMIT 10) tmp;

moreover, MySQL have a cool feature that will let you return an arbitrary range of rows (eg return rows 10-20). This is very handy for displaying pages of records:

此外,MySQL有一个很酷的功能,可以让你返回任意范围的行(例如返回行10-20)。这对于显示记录页面非常方便:

SELECT column FROM table
LIMIT 10 OFFSET 20

The above query will return rows 20-30.

上面的查询将返回20-30行。

So in short, to return rows from 20 to 30 in case of your query, you use:

简而言之,要在查询的情况下将行返回20到30,您可以使用:

SELECT MIN(PRICE) AS MinPrice, MAX(PRICE) AS MaxPrice 
FROM (SELECT PRICE FROM PRICES LIMIT 10 OFFSET 20);

YOU need to change the offset value to specify the start point of your range.

您需要更改偏移值以指定范围的起点。

#2

Have you tried :

你有没有尝试过 :

SELECT min(price) as min_price, max(price) as max_price FROM 
    (SELECT price FROM `prices` LIMIT 0,10);

#1

SELECT MIN(PRICE) AS MinPrice, MAX(PRICE) AS MaxPrice FROM (SELECT PRICE FROM PRICES LIMIT 10) tmp;