sql - strip 化 PostgreSQL 结果集的顺序

Question

假设我有下表:

create temp table test (id serial, number integer);
insert into test (number) 
values (5), (4), (3), (2), (1), (0);

如果我按数字降序排序，我会得到:

select * from test order by number desc;
id | number
---+--------
 1 | 5
 2 | 4
 3 | 3
 4 | 2
 5 | 1
 6 | 0

如果我按数字升序排序，我会得到:

select * from test order by number asc;

6 | 0
5 | 1
4 | 2
3 | 3
2 | 4
1 | 5

如何对顺序进行 strip 化，使其每行在升序和降序之间交替？例如:

6 | 0   or   1 | 5
1 | 5        6 | 0
5 | 1        2 | 4
2 | 4        5 | 1
4 | 2        3 | 3
3 | 3        4 | 2

Answer 1

更新

WITH x AS (
    SELECT *
         , row_number() OVER (ORDER BY number) rn_up
         , row_number() OVER (ORDER BY number DESC) rn_down
    FROM   test
    )
SELECT id, number
FROM   x
ORDER  BY LEAST(rn_up, rn_down), number;

或者:

...
ORDER  BY LEAST(rn_up, rn_down), number  DESC;

从较大的数字开始。

一开始我有两个 CTE，但一个就足够了 - 更简单、更快。