mysql - 在MySQL中进行特殊排序，3个随机最高分，比所有随机

Question

我想要一个 MYSQL 语句，它接受三个分数(4 或 5)并将它们排序在查询结果的顶部。然后，之后的结果应该都是随机的，但不会重复前 2 个。

我尝试使用相同的随机数种子执行 UNION ALL 语句，但 NOT IN 语句不喜欢在子查询中具有“限制”。我认为我需要在 NOT IN 语句之后对子查询进行某种类型的转换。想法？

select * from (select * from mydb.mytable where score in (5,4) order by rand(3423)     limit 3) toplist1
UNION all
select *
from mydb.mytable allothers
where allothers.reviewid NOT IN (select reviewid from mydb.mytable where score in (5,4)     order by rand(3423) limit 3)
order by rand(45423)

Answer 1

也许...

Select * from (
  select A.*, 0 as OrderVal
  from mydb.mytable A
  where score in (5,4) 
  order by rand(3423)     
  limit 3) A
UNION all (
  select *, 1 as orderval
  from mydb.mytable allothers
  where allothers.reviewid NOT IN (
   select reviewid 
   from mydb.mytable 
   where score in (5,4)     
   order by rand(3423) 
   limit 3)
  ) B
ORDER BY orderVal, rand(3423)