Play with SQL 之整理贴

feilniu :

 

这是一些有趣的小问题。我们可以尝试用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”查看关于这个神奇数字的故事。）

 

 

guguda2008 :

 

反正就7个数，穷举法搞定，就不费脑子了。

SQL code

DECLARE @NUMS TABLE(NUM INT)
INSERT INTO @NUMS
SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 0 AND 9

SELECT N1.NUM,N2.NUM,N3.NUM,N4.NUM,N5.NUM,N6.NUM,N7.NUM
FROM @NUMS N1
INNER JOIN @NUMS N2 ON N1.NUM<>N2.NUM
INNER JOIN @NUMS N3 ON N1.NUM<>N3.NUM AND N2.NUM<>N3.NUM
INNER JOIN @NUMS N4 ON N1.NUM<>N4.NUM AND N2.NUM<>N4.NUM AND N3.NUM<>N4.NUM
INNER JOIN @NUMS N5 ON N1.NUM<>N5.NUM AND N2.NUM<>N5.NUM AND N3.NUM<>N5.NUM AND N4.NUM<>N5.NUM
INNER 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.NUM
INNER 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.NUM
WHERE 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
*/

第二题

SQL code

 

DECLARE @NUMS TABLE(NUM INT)
INSERT INTO @NUMS
SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 0 AND 9

SELECT N1.NUM,N2.NUM,N3.NUM,N4.NUM,N5.NUM,N6.NUM
FROM @NUMS N1
INNER JOIN @NUMS N2 ON N1.NUM<>N2.NUM
INNER JOIN @NUMS N3 ON N1.NUM<>N3.NUM AND N2.NUM<>N3.NUM
INNER JOIN @NUMS N4 ON N1.NUM<>N4.NUM AND N2.NUM<>N4.NUM AND N3.NUM<>N4.NUM
INNER JOIN @NUMS N5 ON N1.NUM<>N5.NUM AND N2.NUM<>N5.NUM AND N3.NUM<>N5.NUM AND N4.NUM<>N5.NUM
INNER 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.NUM

WHERE 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*1
AND 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
*/

 

jaydom :

declare @tb table (num int)
insert into @tb
select number from master..spt_values
where TYPE='p' and number between 0 and 9
select distinct a.num a,b.num b,c.num c,d.num d,e.num e,f.num f,g.num g
from @tb a ,@tb b,@tb c,@tb d ,@tb e,@tb f,@tb g
where (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

a    b    c    d    e    f    g
6    3    4    9    2    7    4
9    5    2    3    8    7    6

select 63492*7 --=444444
select 95238*7 --=666666


是这样吗

 

guguda2008 :

引用 7 楼 jaydom 的回复:

SQL code


declare @tb table (num int)
insert into @tb
select number from master..spt_values
where TYPE='p' and number between 0 and 9
select distinct a.num a,b.num b,c.num c,d.num d,e.num e,f.num ……

你再把G的限制加上，就只剩一条了。

jaydom :

 

2.

declare @tb table (num int)
insert into @tb
select number from master..spt_values
where TYPE='p' and number between 0 and 9
select distinct a.num a,b.num b,c.num c,d.num d,e.num e,f.num f
from @tb a ,@tb b,@tb c,@tb d ,@tb e,@tb f
where (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    f
1    4    2    8    5    7

 

jaydom :

 

引用 8 楼 guguda2008 的回复:
引用 7 楼 jaydom 的回复:

SQL code


declare @tb table (num int)
insert into @tb
select number from master..spt_values
where TYPE='p' and number between 0 and 9
select distinct a.num a,b.num b,c.nu……

没看到g 呵呵

 

 

jaydom :

 

 

两个都改一下

SQL code

 

1. declare @tb table (num int)
insert into @tb
select number from master..spt_values
where TYPE='p' and number between 0 and 9
select distinct a.num a,b.num b,c.num c,d.num d,e.num e,f.num f,g.num g
from @tb a ,@tb b,@tb c,@tb d ,@tb e,@tb f,@tb g
where (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.num


a    b    c    d    e    f    g
9    5    2    3    8    7    6

select 95238*7 --=666666


2.

declare @tb table (num int)
insert into @tb
select number from master..spt_values
where TYPE='p' and number between 0 and 9
select distinct a.num a,b.num b,c.num c,d.num d,e.num e,f.num f
from @tb a ,@tb b,@tb c,@tb d ,@tb e,@tb f
where (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.num

a    b    c    d    e    f
1    4    2    8    5    7

feilniu  :

第一题：

SQL code

 

SELECT tmp.abcdefg
FROM (
    SELECT
        gggggg = G.n * 111111, g = G.n, f = F.n, abcde = G.n * 111111 / F.n,
        abcdefg = CAST(G.n * 111111 / F.n AS char(5)) + CAST(F.n AS char(1)) + CAST(G.n AS char(1))
    FROM dbo.Nums G
    INNER JOIN dbo.Nums F
    ON G.n <= 9 AND F.n BETWEEN G.n + 1 AND 9
    WHERE G.n * 111111 % F.n = 0
) tmp
INNER JOIN dbo.Nums n
ON n.n <= 9 AND tmp.abcdefg LIKE '%' + CAST(n.n AS char(1)) + '%'
GROUP BY tmp.abcdefg
HAVING COUNT(DISTINCT n.n) = 7;
--9523876

 

hokor :

SQL code

;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.n
and  b.n <> c.n and b.n<>d.n and b.n <> e.n
and  c.n<>d.n and c.n <> e.n
and  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)) = 0
and charindex(left(gggggg,1),ltrim(abcde)) = 0 and gggggg <> n
and abcde*n = gggggg

/*
abcde    f    gggggg
95238    7    666666
*/

ldslove :

declare @n bigint

;with cte1 as
(select 1 as c union all select 1)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  a
where
n between 100000 and 999999
and (n/10)*(n%10)%111111=0
and ltrim(n) not like '%'+LEFT(ltrim((n/10)*(n%10)),1)+'%'
and LEN(replace(n,substring(ltrim(n),1,1),''))=5
and LEN(replace(n,substring(ltrim(n),2,1),''))=5
and LEN(replace(n,substring(ltrim(n),3,1),''))=5
and LEN(replace(n,substring(ltrim(n),4,1),''))=5
and LEN(replace(n,substring(ltrim(n),5,1),''))=5
and LEN(replace(n,substring(ltrim(n),6,1),''))=5
and n%10<>0


--n                    abcde                f                    gggggg
---------------------- -------------------- -------------------- --------------------
--952387               95238                7                    666666

--(1 行受影响)