原始数据
date 类型 故障id
2011-08-09 12:34:10 6 402
2011-08-09 12:49:21 6 823,832
2011-08-09 12:51:12 6 479,499
2011-08-09 12:59:21 5 807,805
2011-08-09 13:23:16 6 832
2011-08-09 13:33:23 7 402,801
2011-08-09 13:37:27 6 826,832我想得到最后的内容为
2011-08-09 12:34:10 6 402
2011-08-09 12:49:21 6 823
2011-08-09 12:49:21 6 832
2011-08-09 12:51:12 6 479
2011-08-09 12:51:12 6 499
2011-08-09 12:59:21 5 807
2011-08-09 12:59:21 5 805
2011-08-09 13:23:16 6 832
2011-08-09 13:33:23 7 402
2011-08-09 13:33:23 7 801
2011-08-09 13:37:27 6 826
2011-08-09 13:37:27 6 832
这样sql 要怎么写呢?
date 类型 故障id
2011-08-09 12:34:10 6 402
2011-08-09 12:49:21 6 823,832
2011-08-09 12:51:12 6 479,499
2011-08-09 12:59:21 5 807,805
2011-08-09 13:23:16 6 832
2011-08-09 13:33:23 7 402,801
2011-08-09 13:37:27 6 826,832我想得到最后的内容为
2011-08-09 12:34:10 6 402
2011-08-09 12:49:21 6 823
2011-08-09 12:49:21 6 832
2011-08-09 12:51:12 6 479
2011-08-09 12:51:12 6 499
2011-08-09 12:59:21 5 807
2011-08-09 12:59:21 5 805
2011-08-09 13:23:16 6 832
2011-08-09 13:33:23 7 402
2011-08-09 13:33:23 7 801
2011-08-09 13:37:27 6 826
2011-08-09 13:37:27 6 832
这样sql 要怎么写呢?
解决方案 »
- sql 分组查询求助
- orcale透明网关问题--ora-00600:内部错误代码,参数:[HO define:Long fetch],[]
- 求助,一个奇怪的sql问题
- 调用目标relink时出现错误
- Access 区人太少,请教修改Access 一个表,为其增加一个字段的Dao 函数是什么?
- 在oracle创建数据库表的时候出了错,不知道错在那里?请指点,在线急
- 为什么我的Job,手动运行一闪而过
- internal用户不能使用?
- 请教查询最新余额的语句
- 我在Sqlplus中建立了一个表,并输入数据。Oracle中什么可视化的工具可以查看这个表的信息,简要的步骤是什么?谢谢
- 存储过程抛出异常,怎样产生日志信息???
- 在orcale 10G 增加一个服务 服务测试不通过
用charindex+master..spt_values啊
[SYS@myorcl] SQL>WITH T1 AS(
2 SELECT TO_DATE('2011-08-09 12:34:10','YYYY-MM-DD HH24:MI:SS') A,'6' B, '402' C FROM DUAL UNION ALL
3 SELECT TO_DATE('2011-08-09 12:49:21','YYYY-MM-DD HH24:MI:SS') A,'6' B, '823,832' C FROM DUAL UNION ALL
4 SELECT TO_DATE('2011-08-09 12:51:12','YYYY-MM-DD HH24:MI:SS') A,'6' B, '479,499' C FROM DUAL UNION ALL
5 SELECT TO_DATE('2011-08-09 12:59:21','YYYY-MM-DD HH24:MI:SS') A,'5' B, '807,805' C FROM DUAL UNION ALL
6 SELECT TO_DATE('2011-08-09 13:23:16','YYYY-MM-DD HH24:MI:SS') A,'6' B, '832' C FROM DUAL UNION ALL
7 SELECT TO_DATE('2011-08-09 13:33:23','YYYY-MM-DD HH24:MI:SS') A,'7' B, '402,801' C FROM DUAL UNION ALL
8 SELECT TO_DATE('2011-08-09 13:37:27','YYYY-MM-DD HH24:MI:SS') A,'6' B, '826,832' C FROM DUAL
9 )SELECT A, B, COLUMN_VALUE
10 FROM T1,
11 TABLE(CAST(MULTISET
12 (SELECT SUBSTR(C,
13 DECODE(LEVEL,
14 1,
15 1,
16 DECODE(INSTR(C, ',', 1, LEVEL - 1),
17 0,
18 LENGTH(C),
19 INSTR(C, ',', 1, LEVEL - 1) + 1)),
20 DECODE(INSTR(C, ',', 1, LEVEL),
21 0,
22 LENGTH(C),
23 INSTR(C, ',', 1, LEVEL) - 1))
24 FROM DUAL
25 CONNECT BY LEVEL <=
26 LENGTH(C) - LENGTH(REPLACE(C, ',')) + 1) AS
27 SYS.ODCIVARCHAR2LIST));A B COLUMN_VALUE
------------------- - ---------------
2011-08-09 12:34:10 6 402
2011-08-09 12:49:21 6 823
2011-08-09 12:49:21 6 832
2011-08-09 12:51:12 6 479
2011-08-09 12:51:12 6 499
2011-08-09 12:59:21 5 807
2011-08-09 12:59:21 5 805
2011-08-09 13:23:16 6 832
2011-08-09 13:33:23 7 402
2011-08-09 13:33:23 7 801
2011-08-09 13:37:27 6 826
2011-08-09 13:37:27 6 832已选择12行。[SYS@myorcl] SQL>
--有点小bug,超过1个逗号有问题,改了改
[SYS@myorcl] SQL>WITH T1 AS(
2 SELECT TO_DATE('2011-08-09 12:34:10','YYYY-MM-DD HH24:MI:SS') A,'6' B, '402,832' C FROM DUAL UNION ALL
3 SELECT TO_DATE('2011-08-09 12:49:21','YYYY-MM-DD HH24:MI:SS') A,'6' B, '823,832,aaa,234,546,sdffg' C FROM DUAL UNION ALL
4 SELECT TO_DATE('2011-08-09 12:51:12','YYYY-MM-DD HH24:MI:SS') A,'6' B, '479,499,832' C FROM DUAL UNION ALL
5 SELECT TO_DATE('2011-08-09 12:59:21','YYYY-MM-DD HH24:MI:SS') A,'5' B, '807,805,832' C FROM DUAL UNION ALL
6 SELECT TO_DATE('2011-08-09 13:23:16','YYYY-MM-DD HH24:MI:SS') A,'6' B, '832,832' C FROM DUAL UNION ALL
7 SELECT TO_DATE('2011-08-09 13:33:23','YYYY-MM-DD HH24:MI:SS') A,'7' B, '402,801,832' C FROM DUAL UNION ALL
8 SELECT TO_DATE('2011-08-09 13:37:27','YYYY-MM-DD HH24:MI:SS') A,'6' B, '826,832,832' C FROM DUAL
9 )SELECT A, B, COLUMN_VALUE
10 FROM T1,
11 TABLE(CAST(MULTISET
12 (SELECT SUBSTR(C,
13 DECODE(LEVEL,
14 1,
15 1,
16 DECODE(INSTR(C, ',', 1, LEVEL - 1),
17 0,
18 LENGTH(C),
19 INSTR(C, ',', 1, LEVEL - 1) + 1)),
20 DECODE(INSTR(C, ',', 1, LEVEL),
21 0,
22 LENGTH(C),
23 INSTR(C, ',', 1, LEVEL) - 1)
24 -
25 DECODE(LEVEL,
26 1,
27 1,
28 DECODE(INSTR(C, ',', 1, LEVEL - 1),
29 0,
30 LENGTH(C),
31 INSTR(C, ',', 1, LEVEL - 1) + 1))+1)
32 FROM DUAL
33 CONNECT BY LEVEL <=
34 LENGTH(C) - LENGTH(REPLACE(C, ',')) + 1) AS
35 SYS.ODCIVARCHAR2LIST));A B COLUMN_VALUE
------------------- - --------------------
2011-08-09 12:34:10 6 402
2011-08-09 12:34:10 6 832
2011-08-09 12:49:21 6 823
2011-08-09 12:49:21 6 832
2011-08-09 12:49:21 6 aaa
2011-08-09 12:49:21 6 234
2011-08-09 12:49:21 6 546
2011-08-09 12:49:21 6 sdffg
2011-08-09 12:51:12 6 479
2011-08-09 12:51:12 6 499
2011-08-09 12:51:12 6 832
2011-08-09 12:59:21 5 807
2011-08-09 12:59:21 5 805
2011-08-09 12:59:21 5 832
2011-08-09 13:23:16 6 832
2011-08-09 13:23:16 6 832
2011-08-09 13:33:23 7 402
2011-08-09 13:33:23 7 801
2011-08-09 13:33:23 7 832
2011-08-09 13:37:27 6 826
2011-08-09 13:37:27 6 832
2011-08-09 13:37:27 6 832已选择22行。[SYS@myorcl] SQL>
--再写一个
[SYS@orcl] SQL>WITH T1 AS(
2 SELECT TO_DATE('2011-08-09 12:34:10','YYYY-MM-DD HH24:MI:SS') A,'6' B, '402,832' C FROM DUAL UNION ALL
3 SELECT TO_DATE('2011-08-09 12:49:21','YYYY-MM-DD HH24:MI:SS') A,'6' B, '823,832,aaa,234,546,sdffg' C FROM DUAL UNION ALL
4 SELECT TO_DATE('2011-08-09 12:51:12','YYYY-MM-DD HH24:MI:SS') A,'6' B, '479,499,832' C FROM DUAL UNION ALL
5 SELECT TO_DATE('2011-08-09 12:59:21','YYYY-MM-DD HH24:MI:SS') A,'5' B, '807,805,832' C FROM DUAL UNION ALL
6 SELECT TO_DATE('2011-08-09 13:23:16','YYYY-MM-DD HH24:MI:SS') A,'6' B, '832,832' C FROM DUAL UNION ALL
7 SELECT TO_DATE('2011-08-09 13:33:23','YYYY-MM-DD HH24:MI:SS') A,'7' B, '402,801,832' C FROM DUAL UNION ALL
8 SELECT TO_DATE('2011-08-09 13:37:27','YYYY-MM-DD HH24:MI:SS') A,'6' B, '826,832,832' C FROM DUAL
9 )SELECT A, B, C, REGEXP_SUBSTR(C,'[^, ]+',1,L)
10 FROM T1,(SELECT LEVEL L FROM DUAL CONNECT BY LEVEL<=100) T2
11 WHERE LENGTH(T1.C)-LENGTH(REPLACE(T1.C,','))+1>=T2.L
12 ORDER BY C;A B C REGEXP_SUBSTR(C,'[^,]+',1,L)
---------- - ------------------------- --------------------------------------------------
2011-08-09 7 402,801,832 801
2011-08-09 7 402,801,832 402
2011-08-09 7 402,801,832 832
2011-08-09 6 402,832 832
2011-08-09 6 402,832 402
2011-08-09 6 479,499,832 499
2011-08-09 6 479,499,832 479
2011-08-09 6 479,499,832 832
2011-08-09 5 807,805,832 807
2011-08-09 5 807,805,832 805
2011-08-09 5 807,805,832 832
2011-08-09 6 823,832,aaa,234,546,sdffg 832
2011-08-09 6 823,832,aaa,234,546,sdffg sdffg
2011-08-09 6 823,832,aaa,234,546,sdffg 823
2011-08-09 6 823,832,aaa,234,546,sdffg 546
2011-08-09 6 823,832,aaa,234,546,sdffg 234
2011-08-09 6 823,832,aaa,234,546,sdffg aaa
2011-08-09 6 826,832,832 826
2011-08-09 6 826,832,832 832
2011-08-09 6 826,832,832 832
2011-08-09 6 832,832 832
2011-08-09 6 832,832 832已选择22行。[SYS@orcl] SQL>