php - 如何选择3个相邻的行，重点放在MySQL中的较低值上？

我试图弄清楚如何从价格

SELECT *
FROM (SELECT *
      FROM temp_db_cart
      WHERE airport='$airport'
        AND people='$people'
        AND price < '$price'
      ORDER BY price DESC LIMIT 3
     )
ORDER BY price ASC

样本数据：

+---------------+---------+--------+-------+
|  hotel_name   | airport | people | price |
+---------------+---------+--------+-------+
| Days Inn      | MLB     |      1 |   109 |
| Holiday Inn   | MCO     |      2 |   149 |
| Americas Best | MLB     |      2 |   199 |
| Econo Lodge   | SFB     |      1 |   209 |
+---------------+---------+--------+-------+

预期成绩：

选择的酒店：美洲最佳

+---------------+-------+---------+--------+-------+
|  hotel_name   | order | airport | people | price |
+---------------+-------+---------+--------+-------+
| Days Inn      | 1     | ..      | ..     | ..    |
| Holiday Inn   | 2     | ..      | ..     | ..    |
| Americas Best | Skip  | ..      | ..     | ..    |
| Econo Lodge   | 3     | ..      | ..     | ..    |
+---------------+-------+---------+--------+-------+

PHP / MySQL组合可以用作答案。任何帮助，将不胜感激。

最佳答案

您可以union较低价格的两个记录和较高价格的记录：

select * from
(
(SELECT *
  FROM temp_db_cart
  WHERE airport='MCO'
    AND people='3'
    AND price < '245'
  ORDER BY price DESC LIMIT 3)
 UNION
(SELECT *
  FROM temp_db_cart
  WHERE airport='MCO'
    AND people='3'
    AND price > '245'
  ORDER BY price LIMIT 1)
)
order by price desc limit 3

在第一个查询中，选择3行，在第二个查询中，选择1行（如果存在）。最后，在这些3+1（或3+0）行中，仅选择价格最高的3行。