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

Play with SQL 之整理贴

Play with SQL 之整理贴

Play with SQL 之整理贴

sql批量导入 之整理贴

Sound play with stream

Play with Bindings

Play with libvirt

play with the winner

Play with GNU Screen

hdu3487 Play with Chain

play with framework

uva10129(play with words)

HackerRank: Play with words

play with 打印机

【Bzoj4353】play with tree

Play Harball with Korea

Play with Chain HDU

Play with lkvm

C++ LPTSTR

学习MINA之一

C#创建用户控件：IPv4地址输入框

保留区

Play with SQL 之整理贴

Play with SQL 之整理贴

java 读取html写入excel （htmlpaser,jxl）

java实现手机发送信息(实例) xzhou

jqGrid属性中文详细说明

Axis2 错误:An error occurred while completing process -java.lang.reflect.InvocationTargetException

多工具条编程秘技

Section 1.1 Your Ride Is Here

hadoop分布式集群的搭建

mc9s12dg128使用定时器的输出比较产生定时中断代码亮1S灭1S