MySql：在同一列中以不同方式排序

I have a table Foo with a column 'FooId'

我有一个表Foo，列'FooId'

The users who choose to have the Foo attribute set, set it with a positive integer while for others the value of this column is -1.

选择设置Foo属性的用户，使用正整数设置，而对于其他用户，此列的值为-1。

Now, I wish to have the contents of the table ordered by the FooId column such that the results with positive FooId are ordered before the ones that have default value (i.e. -1). So, I have my query something like select * from Foo order by FooId desc.

现在，我希望得到FooId列所排序的表的内容，使得具有正FooId的结果在具有默认值（即-1）的结果之前排序。所以，我的查询类似于来自FooId desc的Foo order中的select *。

Now I want to randomize my results such that results with positive 'FooId' are still before the results with FooId = -1 but the results are ordered randomly. Something with rand() should also be okay, as I am not focusing on performance at the moment.

现在我想随机化我的结果，使得带有正数'FooId'的结果仍然在FooId = -1的结果之前，但结果是随机排序的。 rand（）的东西也应该没问题，因为我现在不关注性能。

What are my options to formulate the query?

我有什么选择来制定查询？

4 个解决方案

#1

SELECT * FROM Foo
ORDER BY IF(FooId>0,RAND(),-0.1) DESC;

#2

You could do a SELECT ... WHERE FooID >=0 ORDER BY RAND() and then use UNION with another select like so:

您可以执行SELECT ... WHERE FooID> = 0 ORDER BY RAND（）然后使用UNION与另一个选择如下：

SELECT * 
FROM Foo
WHERE FooID >= 0 
ORDER BY RAND() 
UNION ALL 
SELECT * 
FROM Foo
WHERE FooID == -1;

You can concatenate any tables with UNION ALL as long as they have the same columns.

您可以使用UNION ALL连接任何表，只要它们具有相同的列即可。

#3

Try this query -

试试这个查询 -

SELECT * FROM Foo ORDER BY if(FooId >= 0, 0, 1), RAND();

#4

Try Below :

试试下面：

SELECT * FROM Foo
 ORDER BY IF(FooId > 0,0,1);

#1

SELECT * FROM Foo
ORDER BY IF(FooId>0,RAND(),-0.1) DESC;

#2