在mysql数据集中随机播放一些结果

I have a table that has a column names order and I want to sort results in ascending order of order. Easy enough.

However, order can also have a value of zero which in my case is special because zeroes mean that these items get pushed to the back of the set in random order (in cases there are multiple zero value rows).

That is to say: 5 6 3 0 0 2 4 1 0 -> 1 2 3 4 5 6 0 0 0

Where the last 3 rows are shuffled every time I make the query. I'm currently solving this via PHP but I was wondering if some of the SQL pros around here could give me pointers for a MySQL solution?

Here's a working sqlfiddle:

SELECT *
FROM t
ORDER BY
  (CASE WHEN id = 0 THEN 999999999 ELSE id END) ASC,
  (SELECT RAND()) ASC

The SELECT in the ORDER BY is important. Without it the RAND() function would only be called once and it wouldn't shuffle.

Try:

SELECT      order,
            CASE
                WHEN order=0 THEN 9999999
                ELSE order
                END AS ordermeby
FROM        table
ORDER BY    ordermeby;

Does this work for you

SELECT `order`
FROM table
ORDER BY CASE WHEN `order` = 0 THEN 1 ELSE 0 END, `order`