多套一层SQL,搞定
[php]
SQL> SELECT WORD, DECODE(POS, LEN+1, 0, POS) MP FROM
2 (SELECT WORD, VOWEL, MIN(DECODE(POS, 0, LEN+1, POS)) POS, LEN FROM
3 (
4 SELECT T.WORD, VW.VOWEL, INSTR(UPPER(T.WORD),VW.VOWEL) POS, LENGTH(T.WORD)
LEN FROM
5 TEST T,
6 ( SELECT UPPER(SUBSTR(STR,ST+1,EN-ST-1)) VOWEL
7 FROM
8 (SELECT STR, DECODE(RN,1,0,INSTR(STR,SEP,1,RN-1)) ST ,
9 DECODE(INSTR(STR,SEP,1,RN), 0, LENGTH(STR)+1, IN
STR(STR,SEP,1,RN)) EN
10 FROM
11 (SELECT ROWNUM RN, STR, SEP FROM
12 (SELECT 'a/e/i/o/u' STR, '/' SEP
FROM DUAL)
13 CONNECT BY ROWNUM<=LENGTH(STR)-LENGTH(RE
PLACE(STR,SEP,''))+1
14 )
15 ) WHERE SUBSTR(STR,ST+1,EN-ST-1) IS NOT NULL
16 ) VW
17 )
18 GROUP BY WORD
19 )
20 /
WORD MP
------------------------------ ----------
A 1
brown 3
by 0
dog 2
fox 2
jumps 2
laze 2
me 2
my 0
over 1
quick 2
the 3
已选择12行。
已用时间: 00: 00: 00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 NESTED LOOPS
3 2 VIEW
4 3 COUNT (STOPKEY)
5 4 CONNECT BY (WITH FILTERING)
6 5 NESTED LOOPS
7 6 TABLE ACCESS (FULL) OF 'DUAL'
8 6 TABLE ACCESS (BY USER ROWID) OF 'DUAL'
9 5 NESTED LOOPS
10 9 BUFFER (SORT)
11 10 CONNECT BY PUMP
12 9 TABLE ACCESS (FULL) OF 'DUAL'
13 2 TABLE ACCESS (FULL) OF 'TEST'
...................
[/php]