我现在有一些记录,需要变换一下排序。
记录如下:
1 A 1 B 1
2 B 2 C 4
3 C 1 D 9
4 D 8 E 1现在要变成如下方式
1 A 1 A 1
2 B 1 B 2
3 C 4 C 1
4 D 9 D 8
5 E 1 E 1也就是上面的记录加头加尾,重新排一遍请熟悉的同学帮帮忙
记录如下:
1 A 1 B 1
2 B 2 C 4
3 C 1 D 9
4 D 8 E 1现在要变成如下方式
1 A 1 A 1
2 B 1 B 2
3 C 4 C 1
4 D 9 D 8
5 E 1 E 1也就是上面的记录加头加尾,重新排一遍请熟悉的同学帮帮忙
SQL> select * from t;
COL_1 COL_2 COL_3 COL_4 COL_5
---------- ----- ---------- ----- ----------
1 A 1 B 1
2 B 2 C 4
3 C 1 D 9
4 D 8 E 1
SQL>
SQL> select col_1,col_2,col_3,col_2,col_5
2 from t
3 union
4 select col_1+1,col_4,1,col_4,1
5 from t
6 where col_1=(
7 select max(col_1)
8 from t)
9 /
COL_1 COL_2 COL_3 COL_2 COL_5
---------- ----- ---------- ----- ----------
1 A 1 A 1
2 B 2 B 4
3 C 1 C 9
4 D 8 D 1
5 E 1 E 1
将上一行的col_4,col_5替换当前行的col_2,col_3,
当前行的col_2,col_3替换当前行的col_4,col_5
BOBO12082119
理解是对的
SQL> select * from t;
COL_1 COL_2 COL_3 COL_4 COL_5
---------- ----- ---------- ----- ----------
1 A 1 B 1
2 B 2 C 4
3 C 1 D 9
4 D 8 E 1select nvl(lag(COL_4)over(order by COL_1),COL_2) as COL_2,
nvl(lag(COL_5)over(order by COL_1),COL_3) as COL_3,
COL_2,COL_3
from t;
试试
--建表
drop table your_table purge;
create table your_table(
col_1 varchar2(5),
col_2 varchar2(5),
col_3 varchar2(5),
col_4 varchar2(5)
);--插入测试数据
insert into your_table
select 'A','1','B','1' from dual union all
select 'B','2','C','4' from dual union all
select 'C','1','D','9' from dual union all
select 'D','8','E','1' from dual;
commit;--查询
select nvl(a.rn,b.rn+1) as rn,
nvl(b.col_3,a.col_1) as col_1,
nvl(b.col_4,a.col_2) as col_2,
nvl(a.col_1,b.col_3) as col_3,
nvl(a.col_2,b.col_4) as col_4
from (select rownum as rn,col_1,col_2 from your_table) a
full join (select rownum as rn,col_3,col_4 from your_table) b on a.rn = b.rn + 1
order by nvl(a.rn,b.rn+1);--结果
RN COL_1 COL_2 COL_3 COL_4
---------- ----- ----- ----- -----
1 A 1 A 1
2 B 1 B 2
3 C 4 C 1
4 D 9 D 8
5 E 1 E 1
PRESNO COL1 COL2 COL3 COL4 POSTNO ID
A 1 B 1 3 1
A 2 C 4 5 2
1 B 4 D 5 4 3
3 D 2 E 6 4
2 C 5 G 1 5
转化以后变成
COL1 COL2 COL3 COL4 A 1 A 1
B 2 B 4
D 5 D 3
E 6 E 6 A 2 A 2
C 4 C 5
G 1 G 1
sys_connect_by_path 和 lag、lead ??
DECODE(ID, 5, COL3, LAG(COL3, 1, COL1) OVER(ORDER BY COL1)) COL_1,
DECODE(ID, 5, COL4, LAG(COL4, 1, COL2) OVER(ORDER BY COL2)) COL_2,
DECODE(ID, 5, COL3, COL1) AS COL_3,
DECODE(ID, 5, COL4, COL2) AS COL_4,
'++++++' AS FILL,
COL1,
COL2,
COL3,
COL4
FROM (SELECT ROWNUM AS ID,
CHR(64 + ROWNUM) AS COL1,
TO_CHAR(ROWNUM) AS COL2,
CHR(97 + ROWNUM) AS COL3,
TO_CHAR(ROWNUM + 10) AS COL4
FROM DUAL
CONNECT BY ROWNUM < 6)
ID COL_1 COL_2 COL_3 COL_4 FILL COL1 COL2 COL3 COL4
1 A 1 A 1 ++++++ A 1 b 11
2 b 11 B 2 ++++++ B 2 c 12
3 c 12 C 3 ++++++ C 3 d 13
4 d 13 D 4 ++++++ D 4 e 14
5 f 15 f 15 ++++++ E 5 f 15
能不能详细指点
PNO C1 C2 C3 C4 TNO ID
A 1 B 1 3 1
A 2 C 4 5 2
1 B 4 D 5 4 3
3 D 2 E 6 4
2 C 5 G 1 5
先分开成
PNO C1 C2 C3 C4 TNO ID
A 1 B 1 3 1
1 B 4 D 5 4 3
3 D 2 E 6 4PNO C1 C2 C3 C4 TNO ID
A 2 C 4 5 2
2 C 5 G 1 5然后再转化以后变成
C1 C2 C3 C4 A 1 A 1
B 2 B 4
D 5 D 3
E 6 E 6
C1 C2 C3 C4
A 2 A 2
C 4 C 5
G 1 G 1