[算法]求元音字母在单词中第一次出现的位置.

zuochc · 发表于 2006-5-3 17:18

求元音字母['a', 'e', 'i', 'o', 'u']在单词中第一次出现的位置. 没有时,为'0',如:
you(= 2), by (= 0), translate (= 3), i (= 1), Other (= 1)等, 要求用SQL实现!

测试用例:

create table test (word varchar2(30));

insert into test values('A');
insert into test values('quick');
insert into test values('brown');
insert into test values('fox');
insert into test values('jumps');
insert into test values('over');
insert into test values('the');
insert into test values('laze');
insert into test values('dog');

Toms_zhang · 发表于 2006-5-3 17:26

写个函数不行吗？非要强人所难，用单个SQL实现吗?

偶知道你这方面强。你这不是让大家没事长白头发吗?

lastwinner · 发表于 2006-5-3 18:06

最初由 Toms_zhang 发布
[B]写个函数不行吗？非要强人所难，用单个SQL实现吗?

偶知道你这方面强。你这不是让大家没事长白头发吗?

[/B]

老张你太坏了，居然这样打击人

Toms_zhang · 发表于 2006-5-3 18:10

最初由 lastwinner 发布
[B]

老张你太坏了，居然这样打击人 [/B]

你们两这方面太强。偶嫉妒！

对了，还有一个这方面的高人：hmxxyy

lastwinner · 发表于 2006-5-3 18:16

zuo兄圈套设得很多啊，又有大小写，还要在没有时显示0
先来个局部的吧
[php]
SQL> SELECT WORD, MIN(POS) MP FROM
  2  (
  3  SELECT T.WORD, VW.VOWEL, INSTR(UPPER(T.WORD),VW.VOWEL) POS FROM
  4  TEST T,
  5  (       SELECT UPPER(SUBSTR(STR,ST+1,EN-ST-1)) VOWEL
  6                   FROM
  7                   (SELECT STR, DECODE(RN,1,0,INSTR(STR,SEP,1,RN-1)) ST ,
  8                            DECODE(INSTR(STR,SEP,1,RN), 0, LENGTH(STR)+1, IN
STR(STR,SEP,1,RN)) EN
  9                            FROM
10                                  (SELECT ROWNUM RN, STR, SEP FROM
11                                           (SELECT 'a/e/i/o/u' STR, '/' SEP
FROM DUAL)
12                                  CONNECT BY ROWNUM<=LENGTH(STR)-LENGTH(RE
PLACE(STR,SEP,''))+1
13                                  )
14                   ) WHERE SUBSTR(STR,ST+1,EN-ST-1) IS NOT NULL
15  ) VW
16  )
17  WHERE POS>0
18  GROUP BY WORD
19  /

WORD                                  MP
------------------------------ ----------
A                                     1
brown                                  3
dog                                  2
fox                                  2
jumps                                  2
laze                                  2
over                                  1
quick                                  2
the                                  3

已选择9行。

已用时间:  00: 00: 00.00

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]

lastwinner · 发表于 2006-5-3 18:17

最初由 Toms_zhang 发布
[B]

你们两这方面太强。偶嫉妒！

对了，还有一个这方面的高人：hmxxyy [/B]

jacky也是高高手
俺都跟他们学的

lastwinner · 发表于 2006-5-3 18:21

补充俩数据先

最初由 zuochc 发布
[B]求元音字母['a', 'e', 'i', 'o', 'u']在单词中第一次出现的位置. 没有时,为'0',如:
you(= 2), by (= 0), translate (= 3), i (= 1), Other (= 1)等, 要求用SQL实现!

测试用例:

create table test (word varchar2(30));

insert into test values('A');
insert into test values('quick');
insert into test values('brown');
insert into test values('fox');
insert into test values('jumps');
insert into test values('over');
insert into test values('the');
insert into test values('laze');
insert into test values('dog');

--补充几个数据
insert into test values('by');
insert into test values('my');
[/B]

lastwinner · 发表于 2006-5-3 18:28

多套一层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]

hmxxyy · 发表于 2006-5-3 18:35

[PHP]
select word, instr(translate(lower(word), 'aeiou', 'XXXXX'), 'X') pos from test
WORD                                                             POS
------------------------------------------------------------ ----------
A                                                                   1
quick                                                                2
brown                                                                3
fox                                                                2
jumps                                                                2
over                                                                1
the                                                                3
laze                                                                2
dog                                                                2
by                                                                   0
my                                                                   0

[/PHP]

lastwinner · 发表于 2006-5-3 18:38

最初由 hmxxyy 发布
[B][PHP]
select word, instr(translate(lower(word), 'aeiou', 'XXXXX'), 'X') pos from test
WORD                                                             POS
------------------------------------------------------------ ----------
A                                                                   1
quick                                                                2
brown                                                                3
fox                                                                2
jumps                                                                2
over                                                                1
the                                                                3
laze                                                                2
dog                                                                2
by                                                                   0
my                                                                   0

........
[/PHP] [/B]

我搞得好复杂

[算法]求元音字母在单词中第一次出现的位置.

Re: [算法]求元音字母在单词中第一次出现的位置.

浏览过的版块