How to solve the 3 Digits Plus 3 Digits Puzzle in SQL
November 2nd, 2008 By Frank Zhou
The following is an interesting problem posted by mathforum.org:
Use the digits 1 to 9 only once in a sum that must be a three-digit number plus another three-digit number to equal another three-digit number. Each digit can only be used once, but all must be used.
—————————————————– 10G SQL Solution——————————————–
SELECT num1||' + '||num2||' = '|| num3 as Equation_str, CASE WHEN lag(cnt) over (order by num1) <> cnt OR lag(cnt) over (order by num1) IS NULL THEN cnt END AS Counter FROM (SELECT num1, num2, num3, count(*) over ( ) as cnt FROM (SELECT substr(num,1,3) num1, substr(num,4,3) num2, substr(num,7,3) num3 FROM (SELECT replace(sys_connect_by_path(n,','), ',') num FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <=9) WHERE LEVEL = 9 CONNECT BY NOCYCLE PRIOR n != n AND LEVEL <=9 AND CASE LEVEL WHEN 1 THEN CASE WHEN n < 9 THEN 1 END WHEN 4 THEN CASE WHEN n + CONNECT_BY_ROOT(n) < 10 THEN 1 END WHEN 7 THEN CASE WHEN CONNECT_BY_ROOT(n) =1 THEN CASE WHEN n >= 3 THEN 1 END ELSE CASE WHEN n >= CONNECT_BY_ROOT(n) + 1 THEN 1 END END ELSE 1 END = 1 ) ) WHERE to_number(num1) + to_number(num2) = to_number(num3) ); EQUATION_STR COUNTER ------------------------------------------ ---------- 124 + 659 = 783 336 125 + 739 = 864 127 + 359 = 486 127 + 368 = 495 128 + 367 = 495 128 + 439 = 567 129 + 357 = 486 129 + 735 = 864 129 + 654 = 783 129 + 438 = 567 134 + 658 = 792 ..................................................... ..................................................... ..................................................... ..................................................... ..................................................... 762 + 183 = 945 763 + 182 = 945 782 + 154 = 936 782 + 163 = 945 783 + 162 = 945 784 + 152 = 936 336 rows selected.
June 24th, 2010 at 11:14 am
11GR2: WITH n AS ( SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9 ) ,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS ( SELECT n,0,0,0,0,0,0,0,0,1 FROM n WHERE n<=4 UNION ALL SELECT t.n1 ,DECODE(t.lvl,1,n.n,t.n2) ,DECODE(t.lvl,2,n.n,t.n3) ,DECODE(t.lvl,3,n.n,t.n4) ,DECODE(t.lvl,4,n.n,t.n5) ,DECODE(t.lvl,5,n.n,t.n6) ,DECODE(t.lvl,6,n.n,t.n7) ,DECODE(t.lvl,7,n.n,t.n8) ,DECODE(t.lvl,8,n.n,t.n9) ,t.lvl+1 FROM t,n WHERE n.n NOT IN (n1,n2,n3,n4,n5,n6,n7,n8) AND (t.lvl=1 AND n.n>t.n1 AND n.n+t.n1<=9 OR (t.lvl=2 AND n.n - t.n1- t.n2 IN (0,1)) OR (t.lvl=4 AND n.n > t.n4) OR (t.lvl=5 AND n.n = MOD(t.n4+ t.n5,10)) OR (t.lvl=7 AND n.n > t.n7) OR t.lvl=8 AND n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n.n*10+n6 OR t.lvl IN (3,6) ) ) ,t2 AS (SELECT * FROM t WHERE lvl=9) SELECT n1||n7||n4||' + '||n2||n8||n5||' = '||n3||n9||n6 FROM t2 UNION ALL SELECT n1||n7||n5||' + '||n2||n8||n4||' = '||n3||n9||n6 FROM t2 UNION ALL SELECT n1||n8||n4||' + '||n2||n7||n5||' = '||n3||n9||n6 FROM t2 UNION ALL SELECT n1||n8||n5||' + '||n2||n7||n4||' = '||n3||n9||n6 FROM t2 UNION ALL SELECT n3||n7||n4||' + '||n2||n8||n5||' = '||n1||n9||n6 FROM t2 UNION ALL SELECT n3||n7||n5||' + '||n2||n8||n4||' = '||n1||n9||n6 FROM t2 UNION ALL SELECT n3||n8||n4||' + '||n2||n7||n5||' = '||n1||n9||n6 FROM t2 UNION ALL SELECT n3||n8||n5||' + '||n2||n7||n4||' = '||n1||n9||n6 FROM t2 ;