现在数据如下:
rownum value
1 AB
2 AC
3 AD
4 BC
5 BD
6 CD希望经过排序后实现
rownum value
1 AB
6 CD
2 AC
5 BD
3 AD
4 BC请问这个order by中如何写这个算法的函数?
rownum value
1 AB
2 AC
3 AD
4 BC
5 BD
6 CD希望经过排序后实现
rownum value
1 AB
6 CD
2 AC
5 BD
3 AD
4 BC请问这个order by中如何写这个算法的函数?
WHEN row_num <= 3 THEN
row_num * 2 - 1
ELSE
END CASE else语句中的,还想不出怎么实现。在WHEN row_num <= 3 THEN语句中,
已经把1、2、3转换成了1、3、5
还需要把else中的4、5、6,转换成6、4、2
WHEN row_num <= 3 THEN
row_num * 2 - 1
ELSE
10 - row_num - mod(row_num, 4)
END CASE
with t as(
SELECT 1 id, 'AB' VAL FROM DUAL
UNION ALL
SELECT 2,'AC' FROM DUAL
UNION ALL
SELECT 3,'AD' FROM DUAL
UNION ALL
SELECT 4,'BC' FROM DUAL
UNION ALL
SELECT 5,'BD' FROM DUAL
UNION ALL
SELECT 6,'CD' FROM DUAL
)SELECT * FROM T ORDER BY LEAST(ID,7-ID);
ID VAL
---------- ---
1 AB
6 CD
2 AC
5 BD
3 AD
4 BC
6 rows selected
学习
ORDER BY 要改成ORDER BY LEAST(ID,7-ID),id才能得到最终结果
下面代码处理6行记录的情况
select emp.*,
rownum as id1,
case
when rownum - trunc(7 / 2) > 0 then
7 - rownum
else
rownum
end as id2
from emp
where rownum <= 6
order by id2, id2;