爱因斯坦的一个问题如何用Pl/sql实现

〇〇

有些关系不知怎么描述
with n as(select level l from dual connect by level<=5)
,g as(select '英瑞丹挪德' s from dual)
,s as(select '红绿白黄蓝' s from dual)
,c as(select '狗鸟猫马鱼' s from dual)
,h as(select '茶咖奶啤水' s from dual)
,y as(select 'PDBMC' s from dual)
,a as(select substr(g.s,n.l,1)||substr(s.s,m.l,1)||substr(c.s,o.l,1)
||substr(h.s,p.l,1)||substr(y.s,q.l,1)b
from n,n m,n o,n p,n q,g,s,c,h,y)
select * from a
where (b like '英红%' --1
or b like '瑞%狗%' --2
or b like '丹%茶%' --3
or b like '%绿%咖%' --5
or b like '%鸟%P' --6
or b like '%黄%D' --7
or b like '%啤B' --12
or b like '德%C') --13
and b not like '%猫%B' --10
and b not like '挪蓝%' --14
and b not like '%马%D' --11
and b not like '%水B' --15
and not ((instr(b,'红')>0 and translate(b,'$瑞丹挪德','$')<>b) or( instr(b,'英')>0 and translate(b,'$绿白黄蓝','$')<>b)) --1
and not ((instr(b,'狗')>0 and translate(b,'$英丹挪德','$')<>b) or( instr(b,'瑞')>0 and translate(b,'$鸟猫马鱼','$')<>b)) --2
and not ((instr(b,'茶')>0 and translate(b,'$英瑞挪德','$')<>b) or( instr(b,'丹')>0 and translate(b,'$咖奶啤水','$')<>b)) --3
and not ((instr(b,'咖')>0 and translate(b,'$红白黄蓝','$')<>b) or( instr(b,'绿')>0 and translate(b,'$茶奶啤水','$')<>b)) --5
and not ((instr(b,'P')>0 and translate(b,'$狗猫马鱼','$')<>b )or (instr(b,'鸟')>0 and translate(b,'$DBMC','$')<>b)) --6
and not ((instr(b,'D')>0 and translate(b,'$红绿白蓝','$')<>b )or (instr(b,'黄')>0 and translate(b,'$PBMC','$')<>b)) --7
and not ((instr(b,'B')>0 and translate(b,'$茶咖奶水','$')<>b )or (instr(b,'啤')>0 and translate(b,'$PDMC','$')<>b)) --12
and not ((instr(b,'C')>0 and translate(b,'$英瑞丹挪','$')<>b )or (instr(b,'德')>0 and translate(b,'$PDBM','$')<>b)) --13
;

losefish

深奥。。。

newkid

原帖由 〇〇 于 2010-1-3 09:53 发表
昨晚想这道题失眠了

这就是OO的可爱之处，只有热爱编程的人才能享受到这种乐趣。

newkid

原帖由 〇〇 于 2010-1-3 19:48 发表
如果要穷举所有可能的组合，从中找到一行符合要求的方法太笨了
房序号、颜色、国家、喝、吸、养每个都有5！个排列。一共就是120^6种

房子序号是不变的，1,2,3,4,5
因此是120^5。实际效果并不差。
条件8,9,14可以筛选掉一些。

你的方法和风铃有同样的问题，就是试图把列转成行。WHERE只能做列间比较，行间比较是做不到的，实际应用中，行间比较条件经常用分析函数转到同一行再作列间比较。

用我的方法写条件很清晰，再玩一点花样：

WITH class_nationality as (
          SELECT 'Englishman' as s FROM dual
          union all
          SELECT 'Swede' FROM dual
          union all
          SELECT 'Dane' FROM dual
          union all
          SELECT 'Norwegian' FROM dual
          union all
          SELECT 'German' FROM dual
     ),
     class_house as(
          SELECT 'red' as s FROM dual
          union all
          SELECT 'white' FROM dual
          union all
          SELECT 'green' FROM dual
          union all
          SELECT 'yellow' FROM dual
          union all
          SELECT 'blue' FROM dual
     ),
     class_beverages as(
          SELECT 'tea' as s FROM dual
          union all
          SELECT 'coffee' FROM dual
          union all
          SELECT 'milk' FROM dual
          union all
          SELECT 'beer' FROM dual
          union all
          SELECT 'water' FROM dual
     ),
     class_cigarettes as(
          SELECT 'pall mall' as s FROM dual
          union all
          SELECT 'dunhill' FROM dual
          union all
          SELECT 'blends' FROM dual
          union all
          SELECT 'blue master' FROM dual
          union all
          SELECT 'prince' FROM dual
     ),
     class_pets as(
          SELECT 'dog' as s FROM dual
          union all
          SELECT 'bird' FROM dual
          union all
          SELECT 'cat' FROM dual
          union all
          SELECT 'horse' FROM dual
          union all
          SELECT 'fish' FROM dual
     )   
     ,nationality AS (
       SELECT n1,n2,n3,n4,n5
             ,DECODE('Englishman',n1,1,n2,2,n3,3,n4,4,n5,5) AS n_Englishman
             ,DECODE('Swede'     ,n1,1,n2,2,n3,3,n4,4,n5,5) AS n_Swede
             ,DECODE('Dane'      ,n1,1,n2,2,n3,3,n4,4,n5,5) AS n_Dane
             ,DECODE('Norwegian' ,n1,1,n2,2,n3,3,n4,4,n5,5) AS n_Norwegian
             ,DECODE('German'    ,n1,1,n2,2,n3,3,n4,4,n5,5) AS n_German
         FROM (
       SELECT TRIM(SUBSTR(p,2 ,19)) AS n1
             ,TRIM(SUBSTR(p,22,19)) AS n2
             ,TRIM(SUBSTR(p,42,19)) AS n3
             ,TRIM(SUBSTR(p,62,19)) AS n4
             ,TRIM(SUBSTR(p,82,19)) AS n5
         FROM (SELECT SYS_CONNECT_BY_PATH(RPAD(s,19),'\') AS p
                 FROM class_nationality
                WHERE LEVEL=5
                CONNECT BY NOCYCLE LEVEL<=5 AND s<>PRIOR s
                )
              )
        )
    ,house AS (
       SELECT h1,h2,h3,h4,h5
             ,DECODE('red'   ,h1,1,h2,2,h3,3,h4,4,h5,5) AS h_red  
             ,DECODE('white' ,h1,1,h2,2,h3,3,h4,4,h5,5) AS h_white
             ,DECODE('green' ,h1,1,h2,2,h3,3,h4,4,h5,5) AS h_green
             ,DECODE('yellow',h1,1,h2,2,h3,3,h4,4,h5,5) AS h_yellow
             ,DECODE('blue'  ,h1,1,h2,2,h3,3,h4,4,h5,5) AS h_blue
         FROM (
       SELECT TRIM(SUBSTR(p,2 ,19)) AS h1
             ,TRIM(SUBSTR(p,22,19)) AS h2
             ,TRIM(SUBSTR(p,42,19)) AS h3
             ,TRIM(SUBSTR(p,62,19)) AS h4
             ,TRIM(SUBSTR(p,82,19)) AS h5
         FROM (SELECT SYS_CONNECT_BY_PATH(RPAD(s,19),'\') AS p
                 FROM class_house
                WHERE LEVEL=5
                CONNECT BY NOCYCLE LEVEL<=5 AND s<>PRIOR s
                )
              )
        )
    ,beverages AS (
       SELECT b1,b2,b3,b4,b5
             ,DECODE('tea'   ,b1,1,b2,2,b3,3,b4,4,b5,5) AS b_tea  
             ,DECODE('coffee',b1,1,b2,2,b3,3,b4,4,b5,5) AS b_coffee
             ,DECODE('milk'  ,b1,1,b2,2,b3,3,b4,4,b5,5) AS b_milk
             ,DECODE('beer'  ,b1,1,b2,2,b3,3,b4,4,b5,5) AS b_beer
             ,DECODE('water' ,b1,1,b2,2,b3,3,b4,4,b5,5) AS b_water
         FROM (
       SELECT TRIM(SUBSTR(p,2 ,19)) AS b1
             ,TRIM(SUBSTR(p,22,19)) AS b2
             ,TRIM(SUBSTR(p,42,19)) AS b3
             ,TRIM(SUBSTR(p,62,19)) AS b4
             ,TRIM(SUBSTR(p,82,19)) AS b5
         FROM (SELECT SYS_CONNECT_BY_PATH(RPAD(s,19),'\') AS p
                 FROM class_beverages
                WHERE LEVEL=5
                CONNECT BY NOCYCLE LEVEL<=5 AND s<>PRIOR s
                )
               )
        )
    ,cigarettes AS (
       SELECT c1,c2,c3,c4,c5
             ,DECODE('pall mall'  ,c1,1,c2,2,c3,3,c4,4,c5,5) AS c_pall_mall
             ,DECODE('dunhill'    ,c1,1,c2,2,c3,3,c4,4,c5,5) AS c_dunhill   
             ,DECODE('blends'     ,c1,1,c2,2,c3,3,c4,4,c5,5) AS c_blends     
             ,DECODE('blue master',c1,1,c2,2,c3,3,c4,4,c5,5) AS c_blue_master
             ,DECODE('prince'     ,c1,1,c2,2,c3,3,c4,4,c5,5) AS c_prince     
         FROM (
       SELECT TRIM(SUBSTR(p,2 ,19)) AS c1
             ,TRIM(SUBSTR(p,22,19)) AS c2
             ,TRIM(SUBSTR(p,42,19)) AS c3
             ,TRIM(SUBSTR(p,62,19)) AS c4
             ,TRIM(SUBSTR(p,82,19)) AS c5
         FROM (SELECT SYS_CONNECT_BY_PATH(RPAD(s,19),'\') AS p
                 FROM class_cigarettes
                WHERE LEVEL=5
                CONNECT BY NOCYCLE LEVEL<=5 AND s<>PRIOR s
                )
               )
        )
    ,pets AS (
       SELECT p1,p2,p3,p4,p5
             ,DECODE('dog'  ,p1,1,p2,2,p3,3,p4,4,p5,5) AS p_dog
             ,DECODE('bird' ,p1,1,p2,2,p3,3,p4,4,p5,5) AS p_bird
             ,DECODE('cat'  ,p1,1,p2,2,p3,3,p4,4,p5,5) AS p_cat
             ,DECODE('horse',p1,1,p2,2,p3,3,p4,4,p5,5) AS p_horse
             ,DECODE('fish' ,p1,1,p2,2,p3,3,p4,4,p5,5) AS p_fish
         FROM (
       SELECT TRIM(SUBSTR(p,2 ,19)) AS p1
             ,TRIM(SUBSTR(p,22,19)) AS p2
             ,TRIM(SUBSTR(p,42,19)) AS p3
             ,TRIM(SUBSTR(p,62,19)) AS p4
             ,TRIM(SUBSTR(p,82,19)) AS p5
         FROM (SELECT SYS_CONNECT_BY_PATH(RPAD(s,19),'\') AS p
                 FROM class_pets
                WHERE LEVEL=5
                CONNECT BY NOCYCLE LEVEL<=5 AND s<>PRIOR s
                )
              )
        )
SELECT RPAD(n1,16)||RPAD(h1,16)||RPAD(b1,16)||RPAD(c1,16)||RPAD(p1,16)||CHR(10)||
       RPAD(n2,16)||RPAD(h2,16)||RPAD(b2,16)||RPAD(c2,16)||RPAD(p2,16)||CHR(10)||
       RPAD(n3,16)||RPAD(h3,16)||RPAD(b3,16)||RPAD(c3,16)||RPAD(p3,16)||CHR(10)||
       RPAD(n4,16)||RPAD(h4,16)||RPAD(b4,16)||RPAD(c4,16)||RPAD(p4,16)||CHR(10)||
       RPAD(n5,16)||RPAD(h5,16)||RPAD(b5,16)||RPAD(c5,16)||RPAD(p5,16) AS RESULT
  FROM nationality
      ,house
      ,beverages
      ,cigarettes
      ,pets           
WHERE  n_Englishman = h_red
       AND  n_Swede = p_dog
       AND  n_Dane = b_tea
       AND  h_green = h_white -1
       AND  h_green = b_coffee
       AND  c_pall_mall = p_bird
       AND  h_yellow = c_dunhill
       AND  b_milk  = 3  
       AND  n_Norwegian = 1
       AND  ABS(c_blends - p_cat) =1
       AND  ABS(c_dunhill - p_horse) =1
       AND  c_blue_master = b_beer
       AND  n_German = c_prince
       AND  h_blue = 2
       AND  ABS(c_blends - b_water) = 1
/

这样修改后consistent gets降到了25, 还没用8,9,14的人肉优化呢。

〇〇

with n as(select level l from dual connect by level<=5)
,g as(select '英瑞丹挪德' s from dual)
,s as(select '红绿白黄蓝' s from dual)
,c as(select '狗鸟猫马鱼' s from dual)
,h as(select '茶咖奶啤水' s from dual)
,y as(select 'PDBMC' s from dual)
,a as(select substr(g.s,n.l,1)||substr(s.s,m.l,1)||substr(c.s,o.l,1)
||substr(h.s,p.l,1)||substr(y.s,q.l,1)b
from n,n m,n o,n p,n q,g,s,c,h,y)
,b as(select * from a
where (b like '英红%' --1
or b like '瑞%狗%' --2
or b like '丹%茶%' --3
or b like '%绿%咖%' --5
or b like '%鸟%P' --6
or b like '%黄%D' --7
or b like '%啤B' --12
or b like '德%C') --13
and b not like '%猫%B' --10
and b not like '挪蓝%' --14
and b not like '%马%D' --11
and b not like '%水B' --15
and not ((instr(b,'红')>0 and translate(b,'$瑞丹挪德','$')<>b) or( instr(b,'英')>0 and translate(b,'$绿白黄蓝','$')<>b)) --1
and not ((instr(b,'狗')>0 and translate(b,'$英丹挪德','$')<>b) or( instr(b,'瑞')>0 and translate(b,'$鸟猫马鱼','$')<>b)) --2
and not ((instr(b,'茶')>0 and translate(b,'$英瑞挪德','$')<>b) or( instr(b,'丹')>0 and translate(b,'$咖奶啤水','$')<>b)) --3
and not ((instr(b,'咖')>0 and translate(b,'$红白黄蓝','$')<>b) or( instr(b,'绿')>0 and translate(b,'$茶奶啤水','$')<>b)) --5
and not ((instr(b,'P')>0 and translate(b,'$狗猫马鱼','$')<>b )or (instr(b,'鸟')>0 and translate(b,'$DBMC','$')<>b)) --6
and not ((instr(b,'D')>0 and translate(b,'$红绿白蓝','$')<>b )or (instr(b,'黄')>0 and translate(b,'$PBMC','$')<>b)) --7
and not ((instr(b,'B')>0 and translate(b,'$茶咖奶水','$')<>b )or (instr(b,'啤')>0 and translate(b,'$PDMC','$')<>b)) --12
and not ((instr(b,'C')>0 and translate(b,'$英瑞丹挪','$')<>b )or (instr(b,'德')>0 and translate(b,'$PDBM','$')<>b)) --13
)
,d as(
select b ,case when instr(b,'挪')>0 then 1 --9
when instr(b,'奶')>0 then 3 --8
when instr(b,'蓝')>0 then 2 --9，14
end n
from b)
,e as( --1~3
select replace(sys_connect_by_path(b,' '),' ','')l from (select b,n from d where n is not null)
where level=3
connect by level<=3 and prior n=n-1 and prior substr(b,1,1)<>substr(b,1,1) and translate(b,'$'||prior b,'$')=b
)
,f as( --4~5
select replace(sys_connect_by_path(b,' '),' ','')l from (select b,n from d where n is null)
where level=2
connect by level<=2 and prior substr(b,1,1)<>substr(b,1,1) and translate(b,'$'||prior b,'$')=b
)
select e.l||f.l from e,f
where
translate(e.l,'$'||f.l,'$')=e.l and translate(f.l,'$'||e.l,'$')=f.l
and abs(floor((instr(e.l||f.l,'B')-1)/5)-floor((instr(e.l||f.l,'猫')-1)/5))=1 --10
and abs(floor((instr(e.l||f.l,'D')-1)/5)-floor((instr(e.l||f.l,'马')-1)/5))=1 --11
and abs(floor((instr(e.l||f.l,'B')-1)/5)-floor((instr(e.l||f.l,'水')-1)/5))=1 --15
and floor((instr(e.l||f.l,'绿')-1)/5)-floor((instr(e.l||f.l,'白')-1)/5)<0 --4
;

E.L||F.L
----------------------------------------------
------------
挪绿鸟咖P丹蓝马茶M瑞黄狗奶D英红鱼啤B德白猫水C
挪绿鸟咖P丹蓝马茶M瑞黄狗奶D德白猫水C英红鱼啤B
挪绿鸟咖P丹蓝鱼茶M德白猫奶C英红马啤B瑞黄狗水D
挪绿鸟咖P丹蓝鱼茶M瑞黄狗奶D英红马啤B德白猫水C
挪白鸟奶P丹蓝鱼茶M德白猫奶C英红马啤B瑞黄狗水D
挪绿鱼咖M丹蓝鸟茶P德白猫奶C英红马啤B瑞黄狗水D
挪绿鱼咖M丹蓝鸟茶P瑞黄狗奶D英红马啤B德白猫水C
挪黄猫水D瑞蓝狗啤B英红猫奶M德绿鱼咖C丹白鸟茶P

已选择8行。

〇〇

找到问题了
因为有2种B打头的烟,我把第二个改成M,结果自己忘记了
还有一个问题"绿色房子在白色房子左面"是表示邻居?还是可以跨房?

爱因斯坦的一个问题如何用Pl/sql实现
爱因斯坦在20世纪初出的这个谜语。他说世界上有98％的人答不出来。
1、在一条街上，有5座房子，喷了5种颜色。　
2、每个房里住着不同国籍的人　
3、每个人喝不同的饮料，抽不同品牌的香烟，养不同的宠物　
问题是：谁养鱼？　
提示：　
1、英国人住红色房子　
2、瑞典人养狗　
3、丹麦人喝茶　
4、绿色房子在白色房子左面　
5、绿色房子主人喝咖啡　
6、抽Pall　Mall　香烟的人养鸟　
7、黄色房子主人抽Dunhill　香烟　
8、住在中间房子的人喝牛奶　
9、　挪威人住第一间房　
10、抽Blends香烟的人住在养猫的人隔壁　
11、养马的人住抽Dunhill　香烟的人隔壁　
12、抽Blue　Master的人喝啤酒　
13、德国人抽Prince香烟　
14、挪威人住蓝色房子隔壁　
15、抽Blends香烟的人有一个喝水的邻居　

with n as(select level l from dual connect by level<=5)
,g as(select '英瑞丹挪德' s from dual)
,s as(select '红绿白黄蓝' s from dual)
,c as(select '狗鸟猫马鱼' s from dual)
,h as(select '茶咖奶啤水' s from dual)
,y as(select 'PDBMC' s from dual)
,a as(select substr(g.s,n.l,1)||substr(s.s,m.l,1)||substr(c.s,o.l,1)
||substr(h.s,p.l,1)||substr(y.s,q.l,1)b
from n,n m,n o,n p,n q,g,s,c,h,y)
,b as(select * from a
where (b like '英红%' --1
or b like '瑞%狗%' --2
or b like '丹%茶%' --3
or b like '%绿%咖%' --5
or b like '%鸟%P' --6
or b like '%黄%D' --7
or b like '%啤M' --12
or b like '德%C') --13
and b not like '%猫%B' --10
and b not like '挪蓝%' --14
and b not like '%马%D' --11
and b not like '%水B' --15
and not ((instr(b,'红')>0 and translate(b,'$瑞丹挪德','$')<>b) or( instr(b,'英')>0 and translate(b,'$绿白黄蓝','$')<>b)) --1
and not ((instr(b,'狗')>0 and translate(b,'$英丹挪德','$')<>b) or( instr(b,'瑞')>0 and translate(b,'$鸟猫马鱼','$')<>b)) --2
and not ((instr(b,'茶')>0 and translate(b,'$英瑞挪德','$')<>b) or( instr(b,'丹')>0 and translate(b,'$咖奶啤水','$')<>b)) --3
and not ((instr(b,'咖')>0 and translate(b,'$红白黄蓝','$')<>b) or( instr(b,'绿')>0 and translate(b,'$茶奶啤水','$')<>b)) --5
and not ((instr(b,'P')>0 and translate(b,'$狗猫马鱼','$')<>b )or (instr(b,'鸟')>0 and translate(b,'$DBMC','$')<>b)) --6
and not ((instr(b,'D')>0 and translate(b,'$红绿白蓝','$')<>b )or (instr(b,'黄')>0 and translate(b,'$PBMC','$')<>b)) --7
and not ((instr(b,'M')>0 and translate(b,'$茶咖奶水','$')<>b )or (instr(b,'啤')>0 and translate(b,'$PBDC','$')<>b)) --12
and not ((instr(b,'C')>0 and translate(b,'$英瑞丹挪','$')<>b )or (instr(b,'德')>0 and translate(b,'$PDBM','$')<>b)) --13
)
,d as(
select b ,case when instr(b,'挪')>0 then 1 --9
when instr(b,'奶')>0 then 3 --8
when instr(b,'蓝')>0 then 2 --9，14
end n
from b)
,e as( --1~3
select replace(sys_connect_by_path(b,' '),' ','')l from (select b,n from d where n is not null)
where level=3
connect by level<=3 and prior n=n-1 and prior substr(b,1,1)<>substr(b,1,1) and translate(b,'$'||prior b,'$')=b
)
,f as( --4~5
select replace(sys_connect_by_path(b,' '),' ','')l from (select b,n from d where n is null)
where level=2
connect by level<=2 and prior substr(b,1,1)<>substr(b,1,1) and translate(b,'$'||prior b,'$')=b
)
select e.l||f.l from e,f
where
translate(e.l,'$'||f.l,'$')=e.l and translate(f.l,'$'||e.l,'$')=f.l and translate(substr(e.l,1,5),'$'||substr(e.l,11,5),'$')=substr(e.l,1,5)
and abs(floor((instr(e.l||f.l,'B')-1)/5)-floor((instr(e.l||f.l,'猫')-1)/5))=1 --10
and abs(floor((instr(e.l||f.l,'D')-1)/5)-floor((instr(e.l||f.l,'马')-1)/5))=1 --11
and abs(floor((instr(e.l||f.l,'B')-1)/5)-floor((instr(e.l||f.l,'水')-1)/5))=1 --15
and floor((instr(e.l||f.l,'绿')-1)/5)-floor((instr(e.l||f.l,'白')-1)/5)<0 --4 ?=-1
;

E.L||F.L
-----------------------------------------------
------------
挪绿鸟咖P德蓝鱼水C英红马奶B丹黄猫茶D瑞白狗啤M
挪绿鸟咖P德蓝鱼水C瑞白狗奶B丹黄猫茶D英红马啤M
挪绿鸟咖P德蓝猫水C英红马奶B丹黄鱼茶D瑞白狗啤M
挪绿鸟咖P德蓝猫水C瑞白狗奶B英红马啤M丹黄鱼茶D
挪绿鸟咖P德蓝猫水C瑞白狗奶B丹黄鱼茶D英红马啤M
挪绿鱼咖B德蓝猫水C瑞黄狗奶D英红马啤M丹白鸟茶P
挪黄猫水D丹蓝马茶B英红鸟奶P德绿鱼咖C瑞白狗啤M

已选择7行。

〇〇

楼上的代码在10.2和11.1都没问题，但在9i
with n as(select level l from dual connect by level<=5)
*
ERROR 位于第 1 行:
ORA-03113: 通信通道的文件结束


已用时间:  00: 00: 02.06
SQL> select * from v$version;
select * from v$version
*
ERROR 位于第 1 行:
ORA-03114: 未连接到 ORALCE


已用时间:  00: 00: 00.00
SQL> conn lt/lt
已连接。
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

已用时间:  00: 00: 00.01

newkid

我忽然意识到全排列只需作一次，于是截止目前最佳写法诞生了：

WITH comb as (  ----- 全排列；数字1-5可被解释为各种属性，而出现位置是房间号
         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
          )
     ,nationality AS (
       SELECT INSTR(p,'1') AS Englishman
             ,INSTR(p,'2') AS Swede      
             ,INSTR(p,'3') AS Dane      
             ,INSTR(p,'4') AS Norwegian  
             ,INSTR(p,'5') AS German     
        FROM comb
      )
    ,house AS (
       SELECT INSTR(p,'1') AS red   
             ,INSTR(p,'2') AS white
             ,INSTR(p,'3') AS green
             ,INSTR(p,'4') AS yellow
             ,INSTR(p,'5') AS blue  
        FROM comb
     )
    ,beverages AS (
       SELECT INSTR(p,'1') AS tea   
             ,INSTR(p,'2') AS coffee
             ,INSTR(p,'3') AS milk  
             ,INSTR(p,'4') AS beer  
             ,INSTR(p,'5') AS water
        FROM comb
     )
    ,cigarettes AS (
       SELECT INSTR(p,'1') AS pall_mall  
             ,INSTR(p,'2') AS dunhill   
             ,INSTR(p,'3') AS blends     
             ,INSTR(p,'4') AS blue_master
             ,INSTR(p,'5') AS prince     
        FROM comb
        )
    ,pets AS (
       SELECT INSTR(p,'1') AS dog  
             ,INSTR(p,'2') AS bird
             ,INSTR(p,'3') AS cat  
             ,INSTR(p,'4') AS horse
             ,INSTR(p,'5') AS fish
        FROM comb
        )
    ,res AS (
       SELECT *
         FROM nationality
             ,house
             ,beverages
             ,cigarettes
             ,pets           
       WHERE  Englishman     = red   ---- 连接条件：不同属性处于同一房间号，或者紧挨着的房间号
              AND  Swede     = dog
              AND  Dane      = tea
              AND  green     = white -1
              AND  green     = coffee
              AND  pall_mall = bird
              AND  yellow    = dunhill
              AND  milk      = 3  
              AND  Norwegian = 1
              AND  ABS(blends - cat)    =1
              AND  ABS(dunhill - horse) =1
              AND  blue_master = beer
              AND  German    = prince
              AND  blue      = 2
              AND  ABS(blends - water)  =1
       )
SELECT room_num
      ,DECODE(room_num,Englishman,'Englishman',Swede   ,'Swede'  ,Dane  ,'Dane'  ,Norwegian  ,'Norwegian'  ,German,'German') as nationality   
      ,DECODE(room_num,red       ,'red'       ,white   ,'white'  ,green ,'green' ,yellow     ,'yellow'     ,blue  ,'blue'  ) as house         
      ,DECODE(room_num,pall_mall ,'pall mall' ,dunhill ,'dunhill',blends,'blends',blue_master,'blue master',prince,'prince') as beverages      
      ,DECODE(room_num,tea       ,'tea'       ,coffee  ,'coffee' ,milk  ,'milk'  ,beer       ,'beer'       ,water ,'water' ) as cigarettes     
      ,DECODE(room_num,dog       ,'dog'       ,bird    ,'bird'   ,cat   ,'cat'   ,horse      ,'horse'      ,fish  ,'fish'  ) as pets           
  FROM res,(SELECT ROWNUM room_num FROM DUAL CONNECT BY ROWNUM<=5)
ORDER BY room_num;

  ROOM_NUM NATIONALIT HOUSE  BEVERAGES   CIGARE PETS
---------- ---------- ------ ----------- ------ -----
         1 Norwegian  yellow dunhill     water  cat
         2 Dane       blue   blends      tea    horse
         3 Englishman red    pall mall   milk   bird
         4 German     green  prince      coffee fish
         5 Swede      white  blue master beer   dog



[ 本帖最后由 newkid 于 2010-1-5 03:04 编辑 ]

newkid

你的子查询e里用translate(b,'$'||prior b,'$')=b来去掉重复，只能保证两层之间不重复；后来你又补上translate(substr(e.l,1,5),'$'||substr(e.l,11,5),'$')=substr(e.l,1,5)去掉1,3层的重复，虽然可以但是挺辛苦的。幸好层数不多。你把不同行数据拼到同一行再作比较的思路还是对的。

〇〇

nice 忽然