php - 如何在 MySQL 中选择 3 个相邻的行并重点关注较低的值？

Question

我试图弄清楚如何从价格 < 当前商品价格的表中选择 3 个相邻行。问题是，如果我选择表中的第一行、第二行或第三行，我需要选择当前项目周围的三个相邻行。需要强调价格较低的项目，例如，如果我从表中选择第三行，则需要选择前两行和第四行。这是我到目前为止的查询:

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 组合可以用于答案。任何帮助将不胜感激。

Answer 1

您可以将价格较低的两条记录与价格较高的记录联合:

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 行。