Play with SQL (Mission 2)
这是一些有趣的小问题。我们可以尝试用T-SQL来解答。
Just for fun!
大家回复时，别忘了贴出自己的代码，可以共同分享一下SQL的技巧和思维的火花。
以下两个题目中，每个字母代表一个0-9的数字，并且不同字母代表的数字是不同的。
1.
a b c d e
* f
_____________
= g g g g g g
问：a b c d e f g各是什么数字（不重复）
（该问题可用一个SQL查询给出答案）
2.
abcdef * 2 = cdefab
cdefab * 2 = efabcd
问：a b c d e f各是什么数字（不重复）
（该问题似乎用SQL解还不如用纸笔解方便，不太符合Play with SQL的本意。但abcdef是一个很神奇的数字，可以Google计算结果“abcdef”查看关于这个神奇数字的故事。）
[解决办法]
反正就7个数，穷举法搞定，就不费脑子了。
DECLARE @NUMS TABLE(NUM INT)INSERT INTO @NUMS SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 0 AND 9SELECT N1.NUM,N2.NUM,N3.NUM,N4.NUM,N5.NUM,N6.NUM,N7.NUMFROM @NUMS N1INNER JOIN @NUMS N2 ON N1.NUM<>N2.NUMINNER JOIN @NUMS N3 ON N1.NUM<>N3.NUM AND N2.NUM<>N3.NUMINNER JOIN @NUMS N4 ON N1.NUM<>N4.NUM AND N2.NUM<>N4.NUM AND N3.NUM<>N4.NUMINNER JOIN @NUMS N5 ON N1.NUM<>N5.NUM AND N2.NUM<>N5.NUM AND N3.NUM<>N5.NUM AND N4.NUM<>N5.NUMINNER JOIN @NUMS N6 ON N1.NUM<>N6.NUM AND N2.NUM<>N6.NUM AND N3.NUM<>N6.NUM AND N4.NUM<>N6.NUM AND N5.NUM<>N6.NUMINNER JOIN @NUMS N7 ON N1.NUM<>N7.NUM AND N2.NUM<>N7.NUM AND N3.NUM<>N7.NUM AND N4.NUM<>N7.NUM AND N5.NUM<>N7.NUM AND N6.NUM<>N7.NUMWHERE N5.NUM*N6.NUM+N4.NUM*10*N6.NUM+N3.NUM*100*N6.NUM+N2.NUM*1000*N6.NUM+N1.NUM*10000*N6.NUM=N7.NUM+N7.NUM*10+N7.NUM*100+N7.NUM*1000+N7.NUM*10000+N7.NUM*100000/*9 5 2 3 8 7 6 SELECT 95238*7--SELECT 95238*7*/
[解决办法]
第二题
DECLARE @NUMS TABLE(NUM INT)INSERT INTO @NUMS SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 0 AND 9SELECT N1.NUM,N2.NUM,N3.NUM,N4.NUM,N5.NUM,N6.NUMFROM @NUMS N1INNER JOIN @NUMS N2 ON N1.NUM<>N2.NUMINNER JOIN @NUMS N3 ON N1.NUM<>N3.NUM AND N2.NUM<>N3.NUMINNER JOIN @NUMS N4 ON N1.NUM<>N4.NUM AND N2.NUM<>N4.NUM AND N3.NUM<>N4.NUMINNER JOIN @NUMS N5 ON N1.NUM<>N5.NUM AND N2.NUM<>N5.NUM AND N3.NUM<>N5.NUM AND N4.NUM<>N5.NUMINNER JOIN @NUMS N6 ON N1.NUM<>N6.NUM AND N2.NUM<>N6.NUM AND N3.NUM<>N6.NUM AND N4.NUM<>N6.NUM AND N5.NUM<>N6.NUMWHERE N1.NUM*100000*2+N2.NUM*10000*2+N3.NUM*1000*2+N4.NUM*100*2+N5.NUM*10*2+N6.NUM*1*2=N3.NUM*100000+N4.NUM*10000+N5.NUM*1000+N6.NUM*100+N1.NUM*10+N2.NUM*1AND N3.NUM*100000*2+N4.NUM*10000*2+N5.NUM*1000*2+N6.NUM*100*2+N1.NUM*10*2+N2.NUM*1*2=N5.NUM*100000+N6.NUM*10000+N1.NUM*1000+N2.NUM*100+N3.NUM*10+N4.NUM*1/*1 4 2 8 5 7*/
[解决办法]
2.declare @tb table (num int)insert into @tbselect number from master..spt_values where TYPE='p' and number between 0 and 9select distinct a.num a,b.num b,c.num c,d.num d,e.num e,f.num ffrom @tb a ,@tb b,@tb c,@tb d ,@tb e,@tb fwhere (a.num*100000+b.num*10000+c.num*1000+d.num*100+e.num*10+f.num)*2= c.num*100000+d.num*10000+e.num*1000+f.num*100+a.num*10+b.num and (c.num*100000+d.num*10000+e.num*1000+f.num*100+a.num*10+b.num)*2= e.num*100000+f.num*10000+a.num*1000+b.num*100+c.num*10+d.num and a.num<>b.num and a.num<>c.num and a.num<>d.num and a.num<>e.num and b.num<>c.num and b.num<>d.num and b.num<>e.num and c.num<>d.num and c.num<>e.num and d.num<>e.num a b c d e f1 4 2 8 5 7
[解决办法]
两个都改一下
1.declare @tb table (num int)insert into @tbselect number from master..spt_values where TYPE='p' and number between 0 and 9select distinct a.num a,b.num b,c.num c,d.num d,e.num e,f.num f,g.num gfrom @tb a ,@tb b,@tb c,@tb d ,@tb e,@tb f,@tb gwhere (a.num*10000+b.num*1000+c.num*100+d.num*10+e.num)*f.num =( g.num*100000+g.num*10000+g.num*1000+g.num*100+g.num*10+g.num) and a.num<>b.num and a.num<>c.num and a.num<>d.num and a.num<>e.num and a.num<>f.num and b.num<>c.num and b.num<>d.num and b.num<>e.num and b.num<>f.num and c.num<>d.num and c.num<>e.num and c.num<>f.num and d.num<>e.num and d.num<>f.num and e.num<>f.num and f.num<>0 and g.num<>0 and a.num<>g.num and b.num<>g.num and c.num<>g.num and d.num<>g.num and e.num<>g.num and f.num<>g.numa b c d e f g9 5 2 3 8 7 6select 95238*7 --=6666662.declare @tb table (num int)insert into @tbselect number from master..spt_values where TYPE='p' and number between 0 and 9select distinct a.num a,b.num b,c.num c,d.num d,e.num e,f.num ffrom @tb a ,@tb b,@tb c,@tb d ,@tb e,@tb fwhere (a.num*100000+b.num*10000+c.num*1000+d.num*100+e.num*10+f.num)*2= c.num*100000+d.num*10000+e.num*1000+f.num*100+a.num*10+b.num and (c.num*100000+d.num*10000+e.num*1000+f.num*100+a.num*10+b.num)*2= e.num*100000+f.num*10000+a.num*1000+b.num*100+c.num*10+d.num and a.num<>b.num and a.num<>c.num and a.num<>d.num and a.num<>e.num and a.num<>f.num and b.num<>c.num and b.num<>d.num and b.num<>e.num and b.num<>f.num and c.num<>d.num and c.num<>e.num and c.num<>f.num and d.num<>e.num and d.num<>f.num and e.num<>f.numa b c d e f1 4 2 8 5 7
[解决办法]
;with number as(SELECT NUMBER n FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 0 AND 9),tb1 as(select a.n*10000+b.n*1000+c.n*100+d.n*10+e.n abcde from number a,number b,number c,number d,number e where a.n <> 0 and a.n <> b.n and a.n <> c.n and a.n<>d.n and a.n <> e.nand b.n <> c.n and b.n<>d.n and b.n <> e.nand c.n<>d.n and c.n <> e.nand d.n <> e.n),tb2 as(select n * 111111 gggggg from number a where n <> 0 )select abcde,n f,gggggg from tb1,tb2,number where n > 0 and charindex(ltrim(n),ltrim(abcde)) = 0and charindex(left(gggggg,1),ltrim(abcde)) = 0 and gggggg <> nand abcde*n = gggggg/*abcde f gggggg95238 7 666666*/
[解决办法]
declare @n bigint;with cte1 as(select 1 as c union all select 1),cte2 as(select 1 as c from cte1 a,cte1 b),cte3 as(select 1 as c from cte2 a,cte2 b),cte4 as(select 1 as c from cte3 a,cte3 b),cte5 as(select 1 as c from cte4 a,cte4 b),cte6 as(select 1 as c from cte5 a,cte5 b),nums as (select row_number() over(order by c) as n from cte6)select n,n/10 as abcde,n%10 as f,(n/10)*(n%10) as gggggg from nums awhere n between 100000 and 999999and (n/10)*(n%10)%111111=0and ltrim(n) not like '%'+LEFT(ltrim((n/10)*(n%10)),1)+'%'and LEN(replace(n,substring(ltrim(n),1,1),''))=5and LEN(replace(n,substring(ltrim(n),2,1),''))=5and LEN(replace(n,substring(ltrim(n),3,1),''))=5and LEN(replace(n,substring(ltrim(n),4,1),''))=5and LEN(replace(n,substring(ltrim(n),5,1),''))=5and LEN(replace(n,substring(ltrim(n),6,1),''))=5and n%10<>0--n abcde f gggggg---------------------- -------------------- -------------------- ----------------------952387 95238 7 666666--(1 行受影响)
[解决办法]
分析：
先从g入手，最多尝试7次
g=111111*n
n只能从1~7，
111111能被3整除，分解因式=3*37037=3*37*1001
1001=7*143
=7*13*11
因为f是1位数
所以abcde必须能被11*13*37整除
with t as
(select 11*13*37*level s, level l from dual connect by level<=20)
select l,s from t where s<=99999 and length(translate('1234567890','$'||s,'$'))=5;
L S
----- ----------
2 10582
3 15873
6 31746
9 47619
10 52910
11 58201
12 63492
15 79365
18 95238
这些数没有7的倍数，因此n=7
所以
s中包含7的又排除了
同时满足l能被3整除的只有12 18
2*7=14，4重复
所以l=18
95238*7=666666
