ORACLE 正负数分开排序 SQL
#Oracle#SQL
表结构
create table TEST_ORDER
(
t_name VARCHAR2(5),
t_value NUMBER(3)
);
表数据
insert into TEST_ORDER (t_name, t_value) values ('A', 10);
insert into TEST_ORDER (t_name, t_value) values ('B', 30);
insert into TEST_ORDER (t_name, t_value) values ('C', 20);
insert into TEST_ORDER (t_name, t_value) values ('D', 40);
insert into TEST_ORDER (t_name, t_value) values ('E', -10);
insert into TEST_ORDER (t_name, t_value) values ('F', -30);
insert into TEST_ORDER (t_name, t_value) values ('G', -20);
| T_NAME | T_VALUE |
|---|---|
| A | 10 |
| B | 30 |
| C | 20 |
| D | 40 |
| E | -10 |
| F | -30 |
| G | -20 |
先正后负 正数升序 负数降序
SELECT T_NAME, T_VALUE
FROM TEST_ORDER
ORDER BY SIGN(T_VALUE) DESC, ABS(T_VALUE) ASC;
SELECT T_NAME,
T_VALUE,
RANK() OVER(ORDER BY SIGN(T_VALUE) DESC, ABS(T_VALUE) ASC) AS RANKING
FROM TEST_ORDER;
| T_NAME | T_VALUE | RANKING |
|---|---|---|
| A | 10 | 1 |
| C | 20 | 2 |
| B | 30 | 3 |
| D | 40 | 4 |
| E | -10 | 5 |
| G | -20 | 6 |
| F | -30 | 7 |
先正后负 正数降序 负数升序
SELECT T_NAME, T_VALUE
FROM TEST_ORDER
ORDER BY SIGN(T_VALUE) DESC, ABS(T_VALUE) DESC;
SELECT T_NAME,
T_VALUE,
RANK() OVER(ORDER BY SIGN(T_VALUE) DESC, ABS(T_VALUE) DESC) AS RANKING
FROM TEST_ORDER;
| T_NAME | T_VALUE | RANKING |
|---|---|---|
| D | 40 | 1 |
| B | 30 | 2 |
| C | 20 | 3 |
| A | 10 | 4 |
| F | -30 | 5 |
| G | -20 | 6 |
| E | -10 | 7 |