发现一位专门用SQL解各种谜题的高人！

http://oraqa.com/2008/02/01/how-to-solve-the-coin-combinations-puzzle-in-sql/
高人的
http://oraqa.com/author/frank-zhou/


发现一位专门用SQL解各种谜题的高人！


名字叫Frank Zhou, 似乎是中国人：
http://oraqa.com/author/frank-zhou/

最近OO发的几个趣味问题，他早就做过了。他写的SQL很对我的胃口，我准备陆续把一些有趣的问题转贴过来，比如这个：

http://oraqa.com/2008/02/01/how- ... ions-puzzle-in-sql/

Assuming you have enough coins of 1, 5, 10, 25 and 50 cents, how many ways are there to make change for a dollar?

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

我用11GR2的写法：
WITH coins AS (
  SELECT 1 cents FROM DUAL
  UNION ALL SELECT 5 cents FROM DUAL
  UNION ALL SELECT 10 cents FROM DUAL
  UNION ALL SELECT 25 cents FROM DUAL
  UNION ALL SELECT 50 cents FROM DUAL
  )
,t(c1,c5,c10,c25,c50,cents,total_val) AS (
SELECT DECODE(c.cents,1,1,0)
      ,DECODE(c.cents,5,1,0)         
      ,DECODE(c.cents,10,1,0)         
      ,DECODE(c.cents,25,1,0)         
      ,DECODE(c.cents,50,1,0)         
      ,cents
      ,cents
  FROM coins c
UNION ALL
SELECT c1 + DECODE(c.cents,1,1,0)
      ,c5 + DECODE(c.cents,5,1,0)         
      ,c10+ DECODE(c.cents,10,1,0)         
      ,c25+ DECODE(c.cents,25,1,0)         
      ,c50+ DECODE(c.cents,50,1,0)         
      ,c.cents
      ,t.total_val + c.cents
  FROM t, coins c
WHERE t.total_val + c.cents<=100 AND t.cents<=c.cents
)
SELECT * FROM t WHERE total_val=100;

他的写法在11G通不过：
SELECT ltrim(max(str),',') combinations,
       rtrim(ltrim(replace(max(str_num),',','+'),'+'),'+') Sum,
       count(*) over () as counter
FROM
(SELECT sys_connect_by_path(str,', ') str, rownum rn,
        translate(sys_connect_by_path(str_num,','),
                  chr(0)||'ABCDE',chr(0))||',' AS str_num
FROM
(SELECT str_num, str, to_number(substr(str_num,0,length(str_num)-1)) n,
        substr(str_num,length(str_num)) coin_type
  FROM
(SELECT LEVEL||'A' str_num ,   LEVEL||' Penny' AS str
   FROM dual CONNECT BY LEVEL <=100
UNION ALL
SELECT LEVEL*5||'B' str_num,  LEVEL||' Nickel'
   FROM dual CONNECT BY LEVEL*5 <=100
UNION ALL
SELECT LEVEL*10||'C' str_num, LEVEL||' Dime'
   FROM dual CONNECT BY LEVEL*10 <=100
UNION ALL
SELECT LEVEL*25||'D' str_num, LEVEL||' Quarter'
   FROM dual CONNECT BY LEVEL*25 <=100
UNION ALL
SELECT LEVEL*50||'E' str_num, LEVEL||' Half D$'
   FROM dual CONNECT BY LEVEL*50<=100
)
)
WHERE
CASE LEVEL
WHEN  1 THEN CASE WHEN n = 100 THEN 1 END
WHEN  2 THEN CASE WHEN n + PRIOR  n =100 THEN 1 END
WHEN  3 THEN CASE WHEN CONNECT_BY_ROOT n + PRIOR n + n =100 THEN 1 END
ELSE  1 END =1
CONNECT BY PRIOR coin_type < coin_type
AND
CASE LEVEL
WHEN 2 THEN CASE WHEN n + PRIOR  n <=100 THEN 1 END
WHEN 3 THEN CASE WHEN CONNECT_BY_ROOT n + PRIOR n + n <=100 THEN 1 END
WHEN 4 THEN CASE WHEN CONNECT_BY_ROOT n + PRIOR n + n <96   THEN 1 END
WHEN 5 THEN CASE WHEN CONNECT_BY_ROOT n + PRIOR n + n <=85  THEN 1 END
ELSE 1 END =1
)
CONNECT BY PRIOR rn = rn
AND INSTR(str_num,',', 1,LEVEL+1) > 0
AND PRIOR dbms_random.string('p',10) IS NOT NULL
GROUP BY rn
HAVING SUM(TO_NUMBER(SUBSTR(str_num,
           INSTR(str_num,',', 1, LEVEL) + 1,
           INSTR(str_num,',', 1, LEVEL+1) -
           INSTR(str_num,',', 1, LEVEL) -1))) = 100 ;

WHEN 3 THEN CASE WHEN CONNECT_BY_ROOT n + PRIOR n + n <=100 THEN 1 END
                      *
ERROR at line 38:
ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY condition

印象中只有某个版本允许在CONNECT BY条件中使用CONNECT_BY_ROOT。
发现一位专门用SQL解各种谜题的高人！