ITPub博客

首页 > 数据库 > Oracle > 趣味SQL连载1

趣味SQL连载1

原创 Oracle 作者:lvqf 时间:2011-05-02 21:44:07 0 删除 编辑

今天在PUB里看到了newkid的一个精华帖,挺有趣的,记录学习一下,顺便复习了一下函数的用法。

1 1.1分,5,10,25,50分硬币凑成一元,总共有几种组合办法?

知 知识点:CONNECT BY的序列构造

[@more@]

答案:SELECT '1*' ||c1 .cnt
||'+5*' ||c5 .cnt
||'+10*'||c10.cnt
||'+25*'||c25.cnt
||'+50*'||c50.cnt AS result
FROM (SELECT ROWNUM-1 cnt FROM DUAL CONNECT BY ROWNUM-1<=100) c1 ----
利用CONNECT BY构造出0100作为1分硬币的个数
,(SELECT ROWNUM-1 cnt FROM DUAL CONNECT BY ROWNUM-1<=20 ) c5
,(SELECT ROWNUM-1 cnt FROM DUAL CONNECT BY ROWNUM-1<=10 ) c10
,(SELECT ROWNUM-1 cnt FROM DUAL CONNECT BY ROWNUM-1<=4 ) c25
,(SELECT ROWNUM-1 cnt FROM DUAL CONNECT BY ROWNUM-1<=2 ) c50
WHERE 1 *c1 .cnt
+5 *c5 .cnt
+10*c10.cnt
+25*c25.cnt
+50*c50.cnt=100;

2. 2. Baker, Cooper, Fletcher, Miller and Smith住在一座房子的不同楼层。
Baker
不住顶层。Cooper不住底层。
Fletcher
既不住顶层也不住底层。Miller住得比Cooper高。
Smith
住的楼层和Fletcher不相邻。
Fletcher
住的楼层和Cooper不相邻。(其实质就是概率与统计里面的排列计算

知识点:

1INSTR函数:instr( string1, string2 [, start_position [, nth_appearance ] ] )

string1 源字符串,要在此字符串中查找。

string2 要在string1中查找的字符串.

start_position 代表string1 的哪个位置开始查找。此参数可选,如果省略默认为1. 字符串索引从1开始。如果此参数为正,从左到右开始检索,如果此参数为负,从右到左检索,返回要查找的字符串在源字符串中的开始索引。

nth_appearance 代表要查找第几次出现的string2. 此参数可选,如果省略,默认为 1.如果为负数系统会报错。

例子:

SQL> SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)

2 "Instring" FROM DUAL;

Instring

----------

14

SQL> SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)

2 "Reversed Instring" FROM DUAL;

Reversed Instring

-----------------

2

2REPLACE函数:replace(string,serch_string[,replacement_string])

String:源字符串,要在此字符串中查找serch_string

replacement_string:省略时,表示移除serch_string

例子:

SQL> SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;

Changes

--------------

BLACK and BLUE

SQL> select replace('JACK AND JUE','J') FROM DUAL ;

REPLACE('JACKANDJUE','J')

-------------------------

ACK AND UE

3LEVEL:伪列,只能用于CONNECT BY 中。

CONNECT BY NOCYCLE:避免循环产生,输出不循环的数据。

SYS_CONNECT_BY_PATH(字段名,两字段间的连接符号):可理解为显示子节点到根节点的路径

例子:

SQL> select LEVEL,RN,PRIOR RN,SYS_CONNECT_BY_PATH(rn,'')

2 FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=3) A

3 CONNECT BY NOCYCLE LEVEL<=3 AND rn<>PRIOR rn ;

LEVEL RN PRIORRN SYS_CONNECT_BY_PATH(RN,'')

--------------------------------------------------------------

1 1 1

2 2 1 12

3 3 2 123

2 3 1 13

3 2 3 132

1 2 2

2 1 2 21

3 3 1 213

2 3 2 23

3 1 3 231

1 3 3

2 1 3 31

3 2 1 312

2 2 3 32

3 1 2 321

15 rows selected(其实质就是概率与统计里面的排列计算)

答案:

WITH comb as (
SELECT INSTR(p,'1') AS Baker
,INSTR(p,'2') AS Cooper
,INSTR(p,'3') AS Fletcher
,INSTR(p,'4') AS Miller
,INSTR(p,'5') AS Smith
FROM ( SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,''),'') AS p
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=5)
WHERE LEVEL=5
CONNECT BY NOCYCLE LEVEL<=5 AND rn<>PRIOR rn
)
)
SELECT * FROM comb
WHERE Baker<>5
AND Cooper<>1
AND Fletcher NOT IN (1,5)
AND Miller > Cooper
AND Smith-Fletcher NOT IN (1,-1)
AND Cooper-Fletcher NOT IN (1,-1);

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22466621/viewspace-1049353/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-10

  • 博文量
    13
  • 访问量
    26596