php - MySql - 获取最小、最大和 3 个随机行

Question

我想要一个 MySql 查询来获取 5 行，其中包括最低价格行、最高价格行和其他 3 个随机行。

表:

ID  Product Price
1   data    2
2   data    20
3   data    55
4   data    24
5   data    2
6   data    15
7   data    10
8   data    33
9   data    3
10  data    30

预期结果(随机 3 行)

ID  Product Price
1   data    2
3   data    55
4   data    24
6   data    15
7   data    10

任何帮助将不胜感激!

Answer 1

SELECT table.*
  FROM table
     , ( SELECT @minPrice := ( SELECT min(Price) FROM table ) minPrice
              , @minId    := ( SELECT id FROM table WHERE Price = @minPrice ORDER BY rand() LIMIT 1 )
              , @maxPrice := ( SELECT max(Price) FROM table ) maxPrice
              , @maxId    := ( SELECT id FROM table WHERE Price = @maxPrice ORDER BY rand() LIMIT 1 )
       ) tmp
  WHERE table.id in (@minId,@maxId)
UNION
(SELECT *
   FROM table
   WHERE Price not in (@minPrice,@maxPrice)
   ORDER BY rand()
   LIMIT 3
)