WITH t AS (SELECT 1 a, 'a' b, 'b' c, 'c' d FROM DUAL UNION ALL SELECT 2, 'd', 'e', 'f' FROM DUAL UNION ALL SELECT 3, 'g', 'h', 'i' FROM DUAL )SELECT t.a e, DECODE (ROW_NUMBER () OVER (PARTITION BY t.a ORDER BY t.b), 1, t.b, 2, t.c, t.d ) f FROM t, (SELECT MOD (ROWNUM, c.t_num) + 1 a FROM (SELECT COUNT (1) t_num FROM t) c CONNECT BY ROWNUM <= c.t_num * 3) b WHERE t.a = b.a结果Row# E F1 1 a 2 1 b 3 1 c 4 2 d 5 2 e 6 2 f 7 3 g 8 3 h 9 3 i
增加条纪录测试 WITH t AS (SELECT 1 a, 'a' b, 'b' c, 'c' d FROM DUAL UNION ALL SELECT 2, 'd', 'e', 'f' FROM DUAL UNION ALL SELECT 3, 'g', 'h', 'i' FROM DUAL union all SELECT 4, 'j', 'k', null FROM DUAL )结果 Row# E F1 1 a 2 1 b 3 1 c 4 2 d 5 2 e 6 2 f 7 3 g 8 3 h 9 3 i 10 4 j 11 4 k 12 4
1:创建表 TB_FROM 和 TB_TO CREATE TABLE TB_FROM ( A VARCHAR2(10), B VARCHAR2(10), C VARCHAR2(10), D VARCHAR2(10) ); CREATE TABLE TB_TO ( E VARCHAR2(10), F VARCHAR2(10) ); 2:将 TB_FROM 数据导入TB_TO INSERT ALL INTO TB_TO VALUES(A,B) INTO TB_TO VALUES(A,C) INTO TB_TO VALUES(A,D) SELECT * FROM TB_FROM; 3:检索TB_TO(注意顺序) SELECT * FROM TB_TO ORDER BY E;
SQL> SELECT * FROM 2 (select B.A,decode(A.column_name,'A',B.A ,'B',B.B,'C',B.C,'D',B.D) VALUE 3 from All_Tab_Columns A,TABLE2 B 4 WHERE A.TABLE_NAME='TABLE2' ) 5 WHERE A<>VALUE 6 ;
A VALUE - ----- 1 a 1 b 1 c 2 d 2 e 2 f 3 g 3 h 3 i
(SELECT 1 a, 'a' b, 'b' c, 'c' d
FROM DUAL
UNION ALL
SELECT 2, 'd', 'e', 'f'
FROM DUAL
UNION ALL
SELECT 3, 'g', 'h', 'i'
FROM DUAL
)SELECT t.a e,
DECODE (ROW_NUMBER () OVER (PARTITION BY t.a ORDER BY t.b),
1, t.b,
2, t.c,
t.d
) f
FROM t,
(SELECT MOD (ROWNUM, c.t_num) + 1 a
FROM
(SELECT COUNT (1) t_num
FROM t) c
CONNECT BY ROWNUM <= c.t_num * 3) b
WHERE t.a = b.a结果Row# E F1 1 a
2 1 b
3 1 c
4 2 d
5 2 e
6 2 f
7 3 g
8 3 h
9 3 i
WITH t AS
(SELECT 1 a, 'a' b, 'b' c, 'c' d
FROM DUAL
UNION ALL
SELECT 2, 'd', 'e', 'f'
FROM DUAL
UNION ALL
SELECT 3, 'g', 'h', 'i'
FROM DUAL
union all
SELECT 4, 'j', 'k', null
FROM DUAL
)结果
Row# E F1 1 a
2 1 b
3 1 c
4 2 d
5 2 e
6 2 f
7 3 g
8 3 h
9 3 i
10 4 j
11 4 k
12 4
CREATE TABLE TB_FROM
(
A VARCHAR2(10),
B VARCHAR2(10),
C VARCHAR2(10),
D VARCHAR2(10)
);
CREATE TABLE TB_TO
(
E VARCHAR2(10),
F VARCHAR2(10)
);
2:将 TB_FROM 数据导入TB_TO
INSERT ALL
INTO TB_TO VALUES(A,B)
INTO TB_TO VALUES(A,C)
INTO TB_TO VALUES(A,D)
SELECT * FROM TB_FROM;
3:检索TB_TO(注意顺序)
SELECT * FROM TB_TO ORDER BY E;
2 (select B.A,decode(A.column_name,'A',B.A ,'B',B.B,'C',B.C,'D',B.D) VALUE
3 from All_Tab_Columns A,TABLE2 B
4 WHERE A.TABLE_NAME='TABLE2' )
5 WHERE A<>VALUE
6 ;
A VALUE
- -----
1 a
1 b
1 c
2 d
2 e
2 f
3 g
3 h
3 i
结果和楼主要求的有一点不同,但是经过排序就可以了。
呵呵,楼主看看行不行?
也请2楼的分析一下我这么做性能如何?
共勉!
1楼的方法是最好的...想法很妙,学习了...
3楼的性能不清楚...
4楼要用到All_Tab_Columns..我只说这个T表有1亿数据...