17,082
社区成员
发帖
与我相关
我的任务
分享横表变竖表的问题,高手帮忙
现有航班信息表:
T_FltInfo
CARRIERID FLTNO FLTDATE IO APT
CZ 123 2008-12-31 I OAK,ANC,NRT,PEK,
现要求得到航段信息视图
V_FltInfo
CARRIERID FLTNO FLTDATE IO SAPT EAPT
CZ 123 2008-12-31 I OAK ANC
CZ 123 2008-12-31 I OAK NRT
CZ 123 2008-12-31 I OAK PEK
CZ 123 2008-12-31 I ANC NRT
CZ 123 2008-12-31 I ANC PEK
CZ 123 2008-12-31 I NRT PEK
高手请回答一下,着急呀,谢谢了!
注:我要求得到一个视图,因为这个视图还得跟别的表做关联
T_FltInfo
CARRIERID FLTNO FLTDATE IO APT
CZ 123 2008-12-31 I OAK,ANC,NRT,PEK,
现要求得到航段信息视图
V_FltInfo
CARRIERID FLTNO FLTDATE IO SAPT EAPT
CZ 123 2008-12-31 I OAK ANC
CZ 123 2008-12-31 I OAK NRT
CZ 123 2008-12-31 I OAK PEK
CZ 123 2008-12-31 I ANC NRT
CZ 123 2008-12-31 I ANC PEK
CZ 123 2008-12-31 I NRT PEK
高手请回答一下,着急呀,谢谢了!
注:我要求得到一个视图,因为这个视图还得跟别的表做关联
...全文
请发表友善的回复…
发表回复
郭大侠_ 2009-01-04
- 打赏
- 举报
to Icss_zhen:
我的数据库中APT 字段的值是 OAK,ANC,NRT,PEK,PVG,
用你的方法试了一下,还是缺一条数据阿.
我的数据库中APT 字段的值是 OAK,ANC,NRT,PEK,PVG,
用你的方法试了一下,还是缺一条数据阿.
icss_zhen 2009-01-04
- 打赏
- 举报
[Quote=引用 6 楼 gsh945 的回复:]
谢谢各位,执行通过.但现在数据有些问题,
我的设想是:如果APT字段是 "AAA,BBB,CCC,DDD,EEE,"
则应该出现的记录中因该是
AAA,BBB
AAA,CCC
AAA,DDD
AAA,EEE
BBB,CCC
BBB,DDD
BBB,EEE
CCC,DDD
CCC,EEE
DDD,EEE
当APT字段中能分割出5个值时,应该能生成 4+3+2+1条记录,以上4个值的测试结果是对的,但是当APT字段中的值增加到5个时就不对了阿,请你们再帮帮我,谢谢
[/Quote]
你这个是因为 ASCII(AAA)+ASCII(DDD)=ASCII(BBB)+ASCII(CCC)和
ASCII(BBB)+ASCII(EEE)=ASCII(DDD)+(CCC)改成下面这样就可以了
谢谢各位,执行通过.但现在数据有些问题,
我的设想是:如果APT字段是 "AAA,BBB,CCC,DDD,EEE,"
则应该出现的记录中因该是
AAA,BBB
AAA,CCC
AAA,DDD
AAA,EEE
BBB,CCC
BBB,DDD
BBB,EEE
CCC,DDD
CCC,EEE
DDD,EEE
当APT字段中能分割出5个值时,应该能生成 4+3+2+1条记录,以上4个值的测试结果是对的,但是当APT字段中的值增加到5个时就不对了阿,请你们再帮帮我,谢谢
[/Quote]
你这个是因为 ASCII(AAA)+ASCII(DDD)=ASCII(BBB)+ASCII(CCC)和
ASCII(BBB)+ASCII(EEE)=ASCII(DDD)+(CCC)改成下面这样就可以了
SELECT CARRIERID, FLTNO, FLTDATE, IO, NEW_APT_T1, NEW_APT_T2
FROM (SELECT T1.CARRIERID,
T1.FLTNO,
T1.FLTDATE,
T1.IO,
NEW_APT_T1,
NEW_APT_T2,
ROW_NUMBER() OVER(PARTITION BY T1.CARRIERID, T1.FLTNO, T1.FLTDATE, T1.IO, ASCII(NEW_APT_T1) + ASCII(NEW_APT_T2) ORDER BY NEW_APT_T1, NEW_APT_T2) RN
FROM (SELECT TT.*,
SUBSTR(',' || TRIM(',' FROM APT) || ',',
INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN) + 1,
INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN) - 1) NEW_APT_T1
FROM T_FltInfo TT,
(SELECT ROWNUM RN
FROM ALL_OBJECTS
WHERE ROWNUM <= (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
LENGTH(REPLACE(TRIM(',' FROM APT),
',',
'')))
FROM T_FltInfo TT) + 1) ZZ
WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T1,
(SELECT TT.*,
SUBSTR(',' || TRIM(',' FROM APT) || ',',
INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN) + 1,
INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN) - 1) NEW_APT_T2
FROM T_FltInfo TT,
(SELECT ROWNUM RN
FROM ALL_OBJECTS
WHERE ROWNUM <= (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
LENGTH(REPLACE(TRIM(',' FROM APT),
',',
'')))
FROM T_FltInfo TT) + 1) ZZ
WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T2
WHERE T1.CARRIERID = T2.CARRIERID
AND T1.FLTNO = T2.FLTNO
AND T1.FLTDATE = T2.FLTDATE
AND T1.IO = T2.IO
AND NEW_APT_T1 <> NEW_APT_T2) YY
WHERE ASCII(NEW_APT_T1) < ASCII(NEW_APT_T2)
order by NEW_APT_T1, NEW_APT_T2;
--运行结果：
CARRIERID FLTNO FLTDATE IO NEW_APT_T1 NEW_APT_T2
1 CZ 123 2008-12-31 I AAA BBB
2 CZ 123 2008-12-31 I AAA CCC
3 CZ 123 2008-12-31 I AAA DDD
4 CZ 123 2008-12-31 I AAA EEE
5 CZ 123 2008-12-31 I BBB CCC
6 CZ 123 2008-12-31 I BBB DDD
7 CZ 123 2008-12-31 I BBB EEE
8 CZ 123 2008-12-31 I CCC DDD
9 CZ 123 2008-12-31 I CCC EEE
10 CZ 123 2008-12-31 I DDD EEE
zmgowin 2009-01-04
- 打赏
- 举报
SQL> with tp_sql as
2 (select b.rid,
3 replace(substr('OAK,ANC,NRT,PEK,',
4 decode(b.rid,
5 1,
6 1,
7 instr('OAK,ANC,NRT,PEK,',
8 ',',
9 1,
10 b.rid - 1)),
11 instr('OAK,ANC,NRT,PEK,', ',', 1, b.rid) -
12 decode(b.rid,
13 1,
14 1,
15 instr('OAK,ANC,NRT,PEK,',
16 ',',
17 1,
18 b.rid - 1))),
19 ',',
20 '') name
21 from dual a,
22 (select rownum rid
23 from all_objects
24 where rownum <=
25 (select length('OAK,ANC,NRT,PEK,') -
26 length(replace('OAK,ANC,NRT,PEK,', ',', ''))
27 from dual)) b)
28 select a.name, b.name
29 from tp_sql a,
30 tp_sql b
31 where b.rid > a.rid;
NAME NAME
---------- ----------
OAK ANC
OAK NRT
ANC NRT
OAK PEK
ANC PEK
NRT PEK
已选择6行。
SQL> /
NAME NAME
---------- ----------
OAK ANC
OAK NRT
ANC NRT
OAK PEK
ANC PEK
NRT PEK
已选择6行。
SQL> with tp_sql as
2 (select b.rid,
3 replace(substr('AAA,BBB,CCC,DDD,EEE,',
4 decode(b.rid,
5 1,
6 1,
7 instr('AAA,BBB,CCC,DDD,EEE,',
8 ',',
9 1,
10 b.rid - 1)),
11 instr('AAA,BBB,CCC,DDD,EEE,', ',', 1, b.rid)
-
12 decode(b.rid,
13 1,
14 1,
15 instr('AAA,BBB,CCC,DDD,EEE,',
16 ',',
17 1,
18 b.rid - 1))),
19 ',',
20 '') name
21 from dual a,
22 (select rownum rid
23 from all_objects
24 where rownum <=
25 (select length('AAA,BBB,CCC,DDD,EEE,') -
26 length(replace('AAA,BBB,CCC,DDD,EEE,', ',',
''))
27 from dual)) b)
28 select a.name, b.name
29 from tp_sql a,
30 tp_sql b
31 where b.rid > a.rid;
NAME NAME
---------- ----------
AAA BBB
AAA CCC
BBB CCC
AAA DDD
BBB DDD
CCC DDD
AAA EEE
BBB EEE
CCC EEE
DDD EEE
icss_zhen 2009-01-04
- 打赏
- 举报
试过MantisXF的方法，可行，太强了！！
kqfh 2009-01-04
- 打赏
- 举报
如果你的表中io arp中城市不是固定数量，最好使用存储过程处理数据到一个中间表，再做后续处理。
郭大侠_ 2009-01-04
- 打赏
- 举报
谢谢各位,执行通过.但现在数据有些问题,
我的设想是:如果APT字段是 "AAA,BBB,CCC,DDD,EEE,"
则应该出现的记录中因该是
AAA,BBB
AAA,CCC
AAA,DDD
AAA,EEE
BBB,CCC
BBB,DDD
BBB,EEE
CCC,DDD
CCC,EEE
DDD,EEE
当APT字段中能分割出5个值时,应该能生成 4+3+2+1条记录,以上4个值的测试结果是对的,但是当APT字段中的值增加到5个时就不对了阿,请你们再帮帮我,谢谢
我的设想是:如果APT字段是 "AAA,BBB,CCC,DDD,EEE,"
则应该出现的记录中因该是
AAA,BBB
AAA,CCC
AAA,DDD
AAA,EEE
BBB,CCC
BBB,DDD
BBB,EEE
CCC,DDD
CCC,EEE
DDD,EEE
当APT字段中能分割出5个值时,应该能生成 4+3+2+1条记录,以上4个值的测试结果是对的,但是当APT字段中的值增加到5个时就不对了阿,请你们再帮帮我,谢谢
watson110 2009-01-04
- 打赏
- 举报
高人，改正这样：
SELECT CARRIERID, FLTNO, FLTDATE, IO, NEW_APT_T1, NEW_APT_T2
FROM (SELECT T1.CARRIERID,
T1.FLTNO,
T1.FLTDATE,
T1.IO,
NEW_APT_T1,
NEW_APT_T2,
ROW_NUMBER() OVER(PARTITION BY T1.CARRIERID, T1.FLTNO, T1.FLTDATE, T1.IO, ASCII(NEW_APT_T1) + ASCII(NEW_APT_T2) ORDER BY NEW_APT_T1, NEW_APT_T2) RN
FROM (SELECT TT.*,
SUBSTR(',' || TRIM(',' FROM APT) || ',',
INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN) + 1,
INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN) - 1) NEW_APT_T1
FROM T_FltInfo TT,
(SELECT ROWNUM RN
FROM ALL_OBJECTS
WHERE ROWNUM <=
(SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
LENGTH(REPLACE(TRIM(',' FROM APT),
',',
'')))
FROM T_FltInfo TT) + 1) ZZ
WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T1,
(SELECT TT.*,
SUBSTR(',' || TRIM(',' FROM APT) || ',',
INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN) + 1,
INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
',',
1,
RN) - 1) NEW_APT_T2
FROM T_FltInfo TT,
(SELECT ROWNUM RN
FROM ALL_OBJECTS
WHERE ROWNUM <=
(SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
LENGTH(REPLACE(TRIM(',' FROM APT),
',',
'')))
FROM T_FltInfo TT) + 1) ZZ
WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T2
WHERE T1.CARRIERID = T2.CARRIERID
AND T1.FLTNO = T2.FLTNO
AND T1.FLTDATE = T2.FLTDATE
AND T1.IO = T2.IO
AND NEW_APT_T1 <> NEW_APT_T2) YY
WHERE RN = 1;郭大侠_ 2009-01-04
- 打赏
- 举报
MantisXF:
你可真是大好人,但现在你的脚本从我这儿执行时出错,我将建表的脚本帖出来,你好事做到底,再帮我看一下,实在是太复杂了,我都看不懂啊.
create table T_FltInfo
(
CARRIERID VARCHAR2(2) not null,
FLTNO VARCHAR2(20) not null,
FLTDATE DATE not null,
IO CHAR(1) not null,
APT VARCHAR2(64)
)
select * from T_FltInfo;
insert into T_FltInfo Values ('CZ','123',To_date('2008-12-31','yyyy-MM-dd'),'I','OAK,ANC,NRT,PEK,PVG,ANC,');
commit;
你可真是大好人,但现在你的脚本从我这儿执行时出错,我将建表的脚本帖出来,你好事做到底,再帮我看一下,实在是太复杂了,我都看不懂啊.
create table T_FltInfo
(
CARRIERID VARCHAR2(2) not null,
FLTNO VARCHAR2(20) not null,
FLTDATE DATE not null,
IO CHAR(1) not null,
APT VARCHAR2(64)
)
select * from T_FltInfo;
insert into T_FltInfo Values ('CZ','123',To_date('2008-12-31','yyyy-MM-dd'),'I','OAK,ANC,NRT,PEK,PVG,ANC,');
commit;
郭大侠_ 2009-01-04
- 打赏
- 举报
谢谢以上各位的指点,我现在明白了些,然后总结了一下,放在我的cnblogs中了,供大家分享,附所有sql语句.
地址:
http://www.cnblogs.com/guodaxia/archive/2009/01/04/1368162.html
地址:
http://www.cnblogs.com/guodaxia/archive/2009/01/04/1368162.html
mantisXF 2008-12-31
- 打赏
- 举报
-- TRY IT ..
SQL> SELECT * FROM TABLE_NAME;
CARRIERID FLTNO FLTDATE IO APT
--------- ---------- ----------- -- ----------------
CZ 123 2008-12-31 I OAK,ANC,NRT,PEK,
SQL> SELECT CARRIERID, FLTNO, FLTDATE, IO, NEW_APT_T1, NEW_APT_T2
2 FROM (SELECT T1.CARRIERID,
3 T1.FLTNO,
4 T1.FLTDATE,
5 T1.IO,
6 NEW_APT_T1,
7 NEW_APT_T2,
8 ROW_NUMBER() OVER(PARTITION BY T1.CARRIERID, T1.FLTNO, T1.FLTDATE, T1.IO, ASCII(NEW_APT_T1) + ASCII(NEW_APT_T2) ORDER BY NEW_APT_T1, NEW_APT_T2) RN
9 FROM (SELECT TT.*,
10 SUBSTR(',' || TRIM(',' FROM APT) || ',',
11 INSTR(',' || TRIM(',' FROM APT) || ',',
12 ',',
13 1,
14 RN) + 1,
15 INSTR(',' || TRIM(',' FROM APT) || ',',
16 ',',
17 1,
18 RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
19 ',',
20 1,
21 RN) - 1) NEW_APT_T1
22 FROM TABLE_NAME TT,
23 (SELECT ROWNUM RN
24 FROM ALL_OBJECTS
25 WHERE ROWNUM <=
26 (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
27 LENGTH(REPLACE(TRIM(',' FROM APT),
28 ',',
29 '')))
30 FROM TABLE_NAME TT) + 1) ZZ
31 WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T1,
32 (SELECT TT.*,
33 SUBSTR(',' || TRIM(',' FROM APT) || ',',
34 INSTR(',' || TRIM(',' FROM APT) || ',',
35 ',',
36 1,
37 RN) + 1,
38 INSTR(',' || TRIM(',' FROM APT) || ',',
39 ',',
40 1,
41 RN + 1) - INSTR(',' || TRIM(',' FROM APT) || ',',
42 ',',
43 1,
44 RN) - 1) NEW_APT_T2
45 FROM TABLE_NAME TT,
46 (SELECT ROWNUM RN
47 FROM ALL_OBJECTS
48 WHERE ROWNUM <=
49 (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -
50 LENGTH(REPLACE(TRIM(',' FROM APT),
51 ',',
52 '')))
53 FROM TABLE_NAME TT) + 1) ZZ
54 WHERE INSTR(',' || TRIM(',' FROM APT) || ',', ',', 1, RN) > 0) T2
55 WHERE T1.CARRIERID = T2.CARRIERID
56 AND T1.FLTNO = T2.FLTNO
57 AND T1.FLTDATE = T2.FLTDATE
58 AND T1.IO = T2.IO
59 AND NEW_APT_T1 <> NEW_APT_T2) YY
60 WHERE RN = 1;
CARRIERID FLTNO FLTDATE IO NEW_APT_T1 NEW_APT_T2
--------- ---------- ----------- -- ----------------- -----------------
CZ 123 2008-12-31 I ANC NRT
CZ 123 2008-12-31 I ANC OAK
CZ 123 2008-12-31 I ANC PEK
CZ 123 2008-12-31 I NRT OAK
CZ 123 2008-12-31 I NRT PEK
CZ 123 2008-12-31 I OAK PEK
6 rows selected
现有航班信息表:
T_FltInfo
CARRIERID FLTNO FLTDATE IO APT
CZ 123 2008-12-31 I OAK,ANC,NRT,PEK,
现要求得到航段信息视图
V_FltInfo
CARRIERID FLTNO FLTDATE IO SAPT EAPT
CZ 123 2008-12-31 I OAK ANC
CZ 123 2008-12-31 I OAK NRT
CZ 123 2008-12-31 I OAK PEK
CZ 123 2008-12-31 I…
[/Quote]
sleepzzzzz 2008-12-31
- 打赏
- 举报
再看了看你的问题,还得你自己改一下.
sleepzzzzz 2008-12-31
- 打赏
- 举报
看这个链接的3楼.只要第一层查询就可以了:
http://topic.csdn.net/u/20081225/17/1d9e676a-7faf-4a49-88db-f0836b53bf28.html
http://topic.csdn.net/u/20081225/17/1d9e676a-7faf-4a49-88db-f0836b53bf28.html
