MySQL - 订购查询并在顶部随机显示一行 [英] MySQL - Order query and display one random row at the top

问题描述

我有一张看起来像这样的表格:

I have a table that looks like:

ID  |  TICKET PRICE  |  VIP
----------------------------
 1  |     $45.00     |  1
 2  |     $40.00     |  1
 3  |     $20.00     |  0
 4  |     $65.00     |  0
 5  |     $45.00     |  1

我需要查询此表以按价格对所有行进行排序，但始终显示顶部为 VIP=1 的随机行.例如，查询应该返回:

I need to query this table to order all rows by Price, but always show one random row which has a VIP=1 at the top. So for example, the query should return:

ID  |  TICKET PRICE  |  VIP
----------------------------
2  |     $40.00     |  1 
3  |     $20.00     |  0    
1  |     $45.00     |  1
5  |     $45.00     |  1     
4  |     $65.00     |  0  

当您刷新页面时，第 5 行可能会成为第一行，因为它的 VIP=1.

And when you refresh the page, row ID 5 may then become the first row, because it has a VIP=1.

我目前的查询看起来像:

I currently have my query looking like:

(SELECT * FROM tickets WHERE VIP=1 ORDER BY rand() LIMIT 1)
UNION
(SELECT * FROM tickets WHERE VIP=0 ORDER BY ticket_price ASC)

这个问题是它只会显示一个 VIP 行.我将如何正确查询这些数据?

This issue with this is that it will only display one VIP row. How would I query this data properly?

推荐答案

使用 order by.这是一种方法:

Use order by. Here is one method:

select t.*
from (select t.*, (@rn := @rn + 1) as seqnum
      from tickets t cross join
           (select @rn := 0) params
      order by vip desc, rand()
     ) t
order by (seqnum = 1) desc, price asc;

这使用子查询来标识要保留在顶部的一行.然后它使用此信息在外部查询中进行排序.

This uses the subquery to identify the one row to keep at the top. Then it uses this information for ordering in the outer query.

如果您的行具有唯一标识符，您还可以这样做:

If your rows have a unique identifier, you could also do:

select t.*
from tickets t cross join
     (select id from tickets where vip = 1 order by rand() limit 1) as t1
order by (t.id = t1.id) desc, price asc;

这篇关于MySQL - 订购查询并在顶部随机显示一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！