在订购每个查询结果后联合多个mysql查询

Question

I have two queries on the same table that need to be joined into one mysqli result. The issue that is proving to be quite unique is ordering the individual statements, and THEN merging into one result, preserving the order, so I can iterate through each result.

Here is the simplified version of the query:

"SELECT number FROM items WHERE number > 0 ORDER BY number ASC"

"SELECT number FROM items WHERE number >= 0 ORDER BY number  DESC"

So given a table with both positive and negative values, the result should be as such:

1, 2, 3, 4, 5, 0, -1, -2, -3, -4, -5

Then the results are put through a PHP while loop.

Answer 1

Something like this should work:

SELECT number FROM (
    SELECT * FROM (
        SELECT number, number AS sort1, 'a' AS sort2 FROM items WHERE number > 0 ORDER BY number ASC
    ) s1
    UNION
        SELECT 0, 'b', 0 FROM items WHERE number = 0
    UNION SELECT * FROM (
        SELECT number, 'c' AS sort1, number AS sort2 FROM items WHERE number < 0 ORDER BY number  DESC
    ) s2
) ss1
ORDER BY ss1.sort1 ASC, ss1.sort2 ASC

This select the 3 parts of the result with different sort tags, so that you can use them to order how you want. The trick part is putting the 0 where it is.