假设我有下表：
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
如何对订单进行条带化以使其在每行的升序和降序之间交替？
例如：
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
更新
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,number DESC;
从更大的数字开始.
我一开始有两个CTE,但一个就够了 – 更简单,更快.
