有表A
| ID | VALUE1 | VALUE2 |
|---|---|---|
| 100001 | 10 | 10 |
| 100002 | 20 | 20 |
| 100003 | 30 | 30 |
| 100004 | 40 | 40 |
| 100001 | 10 | 10 |
| 100002 | 20 | 20 |
| 100003 | 30 | 30 |
| 100004 | 40 | 40 |
希望得到表B的结果
| VALUE1 | VALUE2 |
|---|---|
| 9.9 | 10.1 |
| 20.1 | 19.9 |
| 29.9 | 30.1 |
| 40.1 | 39.9 |
| 9.8 | 10.2 |
| 20.2 | 19.8 |
| 29.8 | 30.2 |
| 40.2 | 39.8 |
SQL> with dao_t as
2 (select 1000001 id ,10 value1 ,10 value2 from dual
3 union all
4 select 1000002 id ,20 value1 ,20 value2 from dual
5 union all
6 select 1000003 id ,30 value1 ,30 value2 from dual
7 union all
8 select 1000004 id ,40 value1 ,40 value2 from dual
9 union all
10 select 1000001 id ,10 value1 ,10 value2 from dual
11 union all
12 select 1000002 id ,20 value1 ,20 value2 from dual
13 union all
14 select 1000003 id ,30 value1 ,30 value2 from dual
15 union all
16 select 1000004 id ,40 value1 ,40 value2 from dual
17 )
18 select
19 (case when mod(rownum,2) =0 then 1 else 2 end )*
20 (case when mod(value1/10,2)=1 then -1 else 1 end ) *0.1+value1 value1,
21 (case when mod(rownum,2) =0 then 1 else 2 end )*
22 (case when mod(value1/10,2)=1 then -1 else 1 end ) *-1*0.1+value2 value2
23 from ( select value1,value2 from dao_t order by value1 )
24 ;
VALUE1 VALUE2
---------- ----------
9.8 10.2
9.9 10.1
20.2 19.8
20.1 19.9
29.8 30.2
29.9 30.1
40.2 39.8
40.1 39.9
8 rows selected