Solving the 'Divisible from 1 to 9' puzzle using SQL
-
Hi, I've been trying to solve this puzzle using SQL.
Is there a more eloquent solution than this?
DECLARE @ns TABLE
(n int)
INSERT @ns VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9);
SELECT n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n
FROM @ns as n1
INNER JOIN @ns as n2 ON (n1.n <> n2.n) AND (CAST(CAST(n1.n as varchar) + CAST(n2.n as varchar) as int) % 2 = 0)
INNER JOIN @ns as n3 ON (n1.n <> n3.n) AND (n2.n <> n3.n) AND (CAST(CAST(n1.n as varchar) + CAST(n2.n as varchar) + CAST(n3.n as varchar) as int) % 3 = 0)
......etc
-
Another way...
WITH ns(n) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
SELECT ((((((((((((((((n1.n*10)+n2.n)*10)+n3.n)*10)+n4.n)*10)+n5.n)*10)+n6.n)*10)+n7.n)*10)+n8.n)*10)+n9.n)
FROM ns n1
INNER JOIN ns n2 ON n2.n NOT IN (n1.n)
INNER JOIN ns n3 ON n3.n NOT IN (n1.n,n2.n)
INNER JOIN ns n4 ON n4.n NOT IN (n1.n,n2.n,n3.n)
INNER JOIN ns n5 ON n5.n NOT IN (n1.n,n2.n,n3.n,n4.n)
INNER JOIN ns n6 ON n6.n NOT IN (n1.n,n2.n,n3.n,n4.n,n5.n)
INNER JOIN ns n7 ON n7.n NOT IN (n1.n,n2.n,n3.n,n4.n,n5.n,n6.n)
INNER JOIN ns n8 ON n8.n NOT IN (n1.n,n2.n,n3.n,n4.n,n5.n,n6.n,n7.n)
INNER JOIN ns n9 ON n9.n NOT IN (n1.n,n2.n,n3.n,n4.n,n5.n,n6.n,n7.n,n8.n)
WHERE ((((((((((((((((n1.n*10)+n2.n)*10)+n3.n)*10)+n4.n)*10)+n5.n)*10)+n6.n)*10)+n7.n)*10)+n8.n)*10)+n9.n) % 9 =0
AND ((((((((((((((n1.n*10)+n2.n)*10)+n3.n)*10)+n4.n)*10)+n5.n)*10)+n6.n)*10)+n7.n)*10)+n8.n) % 8 =0
AND ((((((((((((n1.n*10)+n2.n)*10)+n3.n)*10)+n4.n)*10)+n5.n)*10)+n6.n)*10)+n7.n) % 7 =0
AND ((((((((((n1.n*10)+n2.n)*10)+n3.n)*10)+n4.n)*10)+n5.n)*10)+n6.n) % 6 =0
AND ((((((((n1.n*10)+n2.n)*10)+n3.n)*10)+n4.n)*10)+n5.n) % 5 =0
AND ((((((n1.n*10)+n2.n)*10)+n3.n)*10)+n4.n) % 4 =0
AND ((((n1.n*10)+n2.n)*10)+n3.n) % 3 =0
AND ((n1.n*10)+n2.n) % 2 =0
AND n1.n % 1 =0
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
One elegent solution is to use a Recursive Common Table Element:
DECLARE @ns TABLE (n int)
INSERT @ns VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
;WITH Numbers AS
(SELECT 1 AS LEVEL
,CAST(n AS VARCHAR(9) ) AS NString
FROM@ns
UNION ALL
SELECTLEVEL + 1
,CAST( NString + CAST(n AS CHAR(1) ) AS VARCHAR(9) )
FROMnumbers
CROSS JOIN @ns
WHERELEVEL < 9
AND0 = CAST (NString + CAST(n AS CHAR(1) ) AS NUMERIC(9,0) ) % ( LEVEL + 1 )
AND0 = CHARINDEX(CAST(n AS CHAR(1) ) , NString )
)
SELECTNString
FROMNumbers
WHERELEVEL = 9
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply