简单分析《趣味题》中的SQL

最新推荐文章于 2022-04-20 16:19:17 发布
最新推荐文章于 2022-04-20 16:19:17 发布
阅读量656 收藏
点赞数
分类专栏： DBA 有关 文章标签： sql n2 filter loops nested transformation
探讨了如何利用SQL从1到9的数字中找出可以组成加法等式的三组数字，例如124+659=783。通过创建临时表和复杂的替换及筛选条件实现解决方案。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

这是原文地址《趣味题》，题目是这样的：

1～9有9个数字，三个三个一组，可能正好能组成一个加法等式比如：124＋659＝783

首先是szusunny在8楼给出了一个解决方案：

01with tmp as(
02select a.num from (select rownum as num from dual connect by rownum <= 999) a
03where substr(a.num,1,1) != substr(a.num,2,1)
04  and substr(a.num,3,1) != substr(a.num,2,1)
05  and substr(a.num,1,1) != substr(a.num,3,1)
06  and a.num >= 100
07)
08--
09select
10a.num || '+' || b.num || '=' || c.num as str
11from tmp a, tmp b, tmp c
12where replace(replace(replace(
13      replace(replace(replace(
14      replace(replace(replace('123456789',
15       substr(a.num,1,1)),substr(a.num,2,1)),substr(a.num,3,1)),
16       substr(b.num,1,1)),substr(b.num,2,1)),substr(b.num,3,1)),
17       substr(c.num,1,1)),substr(c.num,2,1)),substr(c.num,3,1)) 
18      is null
19      and a.num + b.num = c.num
20      and a.num < b.num
21      and b.num < c.num

我们逐步分析：

1select rownum as num from dual connect by rownum <= 999

这个语句是产生 1~999 的连续序列。

1select a.num from (select rownum as num from dual connect by rownum <= 999) a
2where substr(a.num,1,1) != substr(a.num,2,1)
3  and substr(a.num,3,1) != substr(a.num,2,1)
4  and substr(a.num,1,1) != substr(a.num,3,1)
5  and a.num >= 100

经过外层条件的过滤后就产生了一组三位数，这个三位数满足以下条件：

1。三个位两两不想等

2。范围是 123 ~ 987

将这组数字放在临时表tmp中，以备下面的查询使用。

01select
02a.num || '+' || b.num || '=' || c.num as str
03from tmp a, tmp b, tmp c
04where replace(replace(replace(
05      replace(replace(replace(
06      replace(replace(replace('123456789',
07       substr(a.num,1,1)),substr(a.num,2,1)),substr(a.num,3,1)),
08       substr(b.num,1,1)),substr(b.num,2,1)),substr(b.num,3,1)),
09       substr(c.num,1,1)),substr(c.num,2,1)),substr(c.num,3,1)) 
10      is null
11      and a.num + b.num = c.num
12      and a.num < b.num
13      and b.num < c.num

这个语句有点长，其中select的表达式就是构建输出xxx+yyy=zzz，重点是where后面的条件，先看冗长、一层层嵌套的replace函数：

1replace(replace(replace(
2replace(replace(replace(
3replace(replace(replace('123456789',
4 substr(a.num,1,1)),substr(a.num,2,1)),substr(a.num,3,1)),
5 substr(b.num,1,1)),substr(b.num,2,1)),substr(b.num,3,1)),
6 substr(c.num,1,1)),substr(c.num,2,1)),substr(c.num,3,1))  
7is null

简单来说就是3个3位数字（共9位）是否都在123456789这9个数字中，并且没有重复。

基本思路是穷举法，通过将临时表按条件自联接两次，从而获得所有可能的结果。

其中，9个replace函数嵌套的代码可以用translate函数代替：

1translate('123456789','0' || a.num || b.num || c.num,'0') is null

最后OO将该方案修改为更加简练的语句：

01with t as(
02  select x from (select level+122 x from dual connect by level<=(987-122)) 
03  where substr(x,1,1)<>substr(x,2,1) 
04    and substr(x,1,1)<>substr(x,3,1)
05    and instr(x,0)=0
06)
07--select count(*) from (
08  select a.x||'+'||b.x||'='||c.x
09  from t a,t b,t c
10  where a.x+b.x=c.x 
11    and a.x<494 
12    and a.x<b.x 
13    and b.x<c.x
14    and translate('123456789','$'||a.x||b.x||c.x,'$') is null
15--);

当然OO也做了些许优化，但是基本思路还是用穷举法遍历所有可能的组合。

执行计划和统计信息如下，我的服务器上执行用时约13秒：

01------------------------------------------------------------------------------------------------------------
02| Id  | Operation                       | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
03------------------------------------------------------------------------------------------------------------
04|   0 | SELECT STATEMENT                |                          |     1 |    39 |     8   (0)| 00:00:01 |
05|   1 |  TEMP TABLE TRANSFORMATION      |                          |       |       |            |          |
06|   2 |   LOAD AS SELECT                | SYS_TEMP_0FD9D6921_55670 |       |       |            |          |
07|*  3 |    VIEW                         |                          |     1 |    13 |     2   (0)| 00:00:01 |
08|*  4 |     CONNECT BY WITHOUT FILTERING|                          |       |       |            |          |
09|   5 |      FAST DUAL                  |                          |     1 |       |     2   (0)| 00:00:01 |
10|   6 |   NESTED LOOPS                  |                          |     1 |    39 |     6   (0)| 00:00:01 |
11|   7 |    MERGE JOIN CARTESIAN         |                          |     1 |    26 |     4   (0)| 00:00:01 |
12|*  8 |     VIEW                        |                          |     1 |    13 |     2   (0)| 00:00:01 |
13|   9 |      TABLE ACCESS FULL          | SYS_TEMP_0FD9D6921_55670 |     1 |    13 |     2   (0)| 00:00:01 |
14|  10 |     BUFFER SORT                 |                          |     1 |    13 |     4   (0)| 00:00:01 |
15|  11 |      VIEW                       |                          |     1 |    13 |     2   (0)| 00:00:01 |
16|  12 |       TABLE ACCESS FULL         | SYS_TEMP_0FD9D6921_55670 |     1 |    13 |     2   (0)| 00:00:01 |
17|* 13 |    VIEW                         |                          |     1 |    13 |     2   (0)| 00:00:01 |
18|  14 |     TABLE ACCESS FULL           | SYS_TEMP_0FD9D6921_55670 |     1 |    13 |     2   (0)| 00:00:01 |
19------------------------------------------------------------------------------------------------------------
20  
21Predicate Information (identified by operation id):
22---------------------------------------------------
23  
24   3 - filter(SUBSTR(TO_CHAR("X"),1,1)<>SUBSTR(TO_CHAR("X"),2,1) AND
25              SUBSTR(TO_CHAR("X"),1,1)<>SUBSTR(TO_CHAR("X"),3,1) AND INSTR(TO_CHAR("X"),'0')=0)
26   4 - filter(LEVEL<=865)
27   8 - filter("A"."X"<494)
28  13 - filter("C"."X"="A"."X"+"B"."X" AND "A"."X"<"B"."X" AND "B"."X"<"C"."X" AND
29              TRANSLATE('123456789','$'||TO_CHAR("A"."X")||TO_CHAR("B"."X")||TO_CHAR("C"."X"),'$') IS NULL)
30  
31  
32Statistics
33----------------------------------------------------------
34          2  recursive calls
35          8  db block gets
36     430532  consistent gets
37          1  physical reads
38        600  redo size
39       4873  bytes sent via SQL*Net to client
40        644  bytes received via SQL*Net from client
41         13  SQL*Net roundtrips to/from client
42          2  sorts (memory)
43          0  sorts (disk)
44        168  rows processed

可以看到这种方法的成本还是很高的。

 

newkid在41楼给出了另外一种解决方案，他用到了Oracle 11g的递归CTE语法，递归CTE在SQL Server 2005开始支持，也是SQL Server层次查询的常规做法：

01WITH n AS (
02   SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9
03   )
04,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS (
05   SELECT n,0,0,0,0,0,0,0,0,1
06     FROM n
07    WHERE n<=4
08   UNION ALL
09   SELECT t.n1
10         ,DECODE(t.lvl,1,n.n,t.n2)
11         ,DECODE(t.lvl,2,n.n,t.n3)
12         ,DECODE(t.lvl,3,n.n,t.n4)
13         ,DECODE(t.lvl,4,n.n,t.n5)
14         ,DECODE(t.lvl,5,n.n,t.n6)
15         ,DECODE(t.lvl,6,n.n,t.n7)
16         ,DECODE(t.lvl,7,n.n,t.n8)
17         ,DECODE(t.lvl,8,n.n,t.n9)
18         ,t.lvl+1
19     FROM t,n
20    WHERE n.n NOT IN (n1,n2,n3,n4,n5,n6,n7,n8,n9)
21          AND (t.lvl=1 AND n.n>t.n1 AND n.n+t.n1<=9
22               OR (t.lvl=2 AND n.n - t.n1- t.n2 IN (0,1))
23               OR (t.lvl=5 AND n.n = MOD(t.n4+ t.n5,10))
24               OR t.lvl IN (3,4,6,7,8)
25              )
26   )
27SELECT n1||n7||n4||' + '||n2||n8||n5||' = '||n3||n9||n6
28  FROM t
29WHERE lvl=9
30      AND n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6
31      ;

N1~N9的意义比较特殊，并不是直观 N1N2N3 + N4N5N6 = N7N8N9，从最外层查询的判断条件 n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6 可以知道N1N7N4 + N2N8N5 = N3N9N6，用竖式来表示比较容易看出来规律来：

1  N1 N7 N4
2+ N2 N8 N5
3----------
4  N3 N9 N6

只要稍作分析可以得到以下几个性质，其实都是小学数学的问题：

1。N1和N2中较小的那个最大值不超过4，如果规定第一组三位数一定要比第二组三位数小，那么N1的取值范围只能是1~4，这也解释n<=4这个条件。
2。N1+N2不能超过9，这个估计比较容易明白的。
3。(N4+N5)%10 = N6
4。N3-N2-N1的值不是1 就是 0 。

这个SQL还是比较难分析，由于是递归CTE的，最好一步一步地跟踪临时表t的变化。

起始步骤，称为第0步，首先是union all 之前的语句，比较好懂：

1WITH n AS (
2   SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9
3   )
4,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS (
5   SELECT n,0,0,0,0,0,0,0,0,1
6     FROM n
7    WHERE n<=4
8)
9select * from t
1N1  N2  N3  N4  N5  N6  N7  N8  N9 LVL
2--- --- --- --- --- --- --- --- --- ---
3  1   0   0   0   0   0   0   0   0   1
4  2   0   0   0   0   0   0   0   0   1
5  3   0   0   0   0   0   0   0   0   1
6  4   0   0   0   0   0   0   0   0   1

进入迭代过程，临时表不断增长：

第1~2步： 

01WITH n AS (
02   SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9
03   )
04,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS (
05   SELECT n,0,0,0,0,0,0,0,0,1
06     FROM n
07    WHERE n<=4
08   UNION ALL
09   SELECT t.n1
10         ,DECODE(t.lvl,1,n.n,t.n2)
11         ,DECODE(t.lvl,2,n.n,t.n3)
12         ,DECODE(t.lvl,3,n.n,t.n4)
13         ,DECODE(t.lvl,4,n.n,t.n5)
14         ,DECODE(t.lvl,5,n.n,t.n6)
15         ,DECODE(t.lvl,6,n.n,t.n7)
16         ,DECODE(t.lvl,7,n.n,t.n8)
17         ,DECODE(t.lvl,8,n.n,t.n9)
18         ,t.lvl+1
19     FROM t,n
20    WHERE n.n NOT IN (n1,n2,n3,n4,n5,n6,n7,n8,n9)
21          AND     (t.lvl=1 AND n.n > t.n1 AND n.n+t.n1<=9 
22               OR (t.lvl=2 AND n.n - t.n1 - t.n2 IN (0,1) )
23               OR (t.lvl=5 AND n.n = MOD(t.n4+ t.n5,10)   )
24               OR  t.lvl IN (3,4,6,7,8)
25              )
26   )
27SELECT n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl,lvl-1 step
28  FROM t
29WHERE lvl<=3
01N1  N2  N3  N4  N5  N6  N7  N8  N9 LVL       STEP <-- 第0步的结果
02--- --- --- --- --- --- --- --- --- --- ----------
03  1   0   0   0   0   0   0   0   0   1          0
04  2   0   0   0   0   0   0   0   0   1          0
05  3   0   0   0   0   0   0   0   0   1          0
06  4   0   0   0   0   0   0   0   0   1          0
07 N1  N2  N3  N4  N5  N6  N7  N8  N9 LVL       STEP <-- 第1步的结果
08--- --- --- --- --- --- --- --- --- --- ----------  
09  1   2   0   0   0   0   0   0   0   2          1
10  1   3   0   0   0   0   0   0   0   2          1
11  1   4   0   0   0   0   0   0   0   2          1
12  1   5   0   0   0   0   0   0   0   2          1
13  1   6   0   0   0   0   0   0   0   2          1
14  1   7   0   0   0   0   0   0   0   2          1
15  1   8   0   0   0   0   0   0   0   2          1
16  2   3   0   0   0   0   0   0   0   2          1
17  2   4   0   0   0   0   0   0   0   2          1
18  2   5   0   0   0   0   0   0   0   2          1
19  2   6   0   0   0   0   0   0   0   2          1
20  2   7   0   0   0   0   0   0   0   2          1
21  3   4   0   0   0   0   0   0   0   2          1
22  3   5   0   0   0   0   0   0   0   2          1
23  3   6   0   0   0   0   0   0   0   2          1
24  4   5   0   0   0   0   0   0   0   2          1
25 N1  N2  N3  N4  N5  N6  N7  N8  N9 LVL       STEP <-- 第2步的结果
26--- --- --- --- --- --- --- --- --- --- ----------  
27  1   2   3   0   0   0   0   0   0   3          2
28  1   2   4   0   0   0   0   0   0   3          2
29  1   3   4   0   0   0   0   0   0   3          2
30  1   3   5   0   0   0   0   0   0   3          2
31  1   4   5   0   0   0   0   0   0   3          2
32  1   4   6   0   0   0   0   0   0   3          2
33  1   5   6   0   0   0   0   0   0   3          2
34  1   5   7   0   0   0   0   0   0   3          2
35  1   6   7   0   0   0   0   0   0   3          2
36  1   6   8   0   0   0   0   0   0   3          2
37  1   7   8   0   0   0   0   0   0   3          2
38  1   7   9   0   0   0   0   0   0   3          2
39  1   8   9   0   0   0   0   0   0   3          2
40  2   3   5   0   0   0   0   0   0   3          2
41  2   3   6   0   0   0   0   0   0   3          2
42  2   4   6   0   0   0   0   0   0   3          2
43  2   4   7   0   0   0   0   0   0   3          2
44  2   5   7   0   0   0   0   0   0   3          2
45  2   5   8   0   0   0   0   0   0   3          2
46  2   6   8   0   0   0   0   0   0   3          2
47  2   6   9   0   0   0   0   0   0   3          2
48  2   7   9   0   0   0   0   0   0   3          2
49  3   4   7   0   0   0   0   0   0   3          2
50  3   4   8   0   0   0   0   0   0   3          2
51  3   5   8   0   0   0   0   0   0   3          2
52  3   5   9   0   0   0   0   0   0   3          2
53  3   6   9   0   0   0   0   0   0   3          2
54  4   5   9   0   0   0   0   0   0   3          2

从第1第2步结果可以看出

1。条件 t.lvl=1 AND n.n > t.n1 AND n.n+t.n1<=9  限制了第1步的结果范围，不会出现以下的一条记录：

1N1  N2  N3  N4  N5  N6  N7  N8  N9 LVL       STEP
2--- --- --- --- --- --- --- --- --- --- ----------
3  1   9   0   0   0   0   0   0   0   2          1

2。条件 t.lvl=2 AND n.n - t.n1 - t.n2 IN (0,1)  限制了第2步的结果范围，不会出现以下的一条记录：

1N1  N2  N3  N4  N5  N6  N7  N8  N9 LVL       STEP
2--- --- --- --- --- --- --- --- --- --- ----------
3  1   2   8   0   0   0   0   0   0   3          2

如此类推，第5步就是判断 (N4+N5)%10 = N6，SQL中的条件字句是t.lvl=5 AND n.n = MOD(t.n4+ t.n5,10)，摘录部分结果集：

01N1  N2  N3  N4  N5  N6  N7  N8  N9 LVL
02--- --- --- --- --- --- --- --- --- ---
03  4   5   9   1   7   8   0   0   0   6
04  4   5   9   2   1   3   0   0   0   6
05  4   5   9   2   6   8   0   0   0   6
06  4   5   9   3   8   1   0   0   0   6
07  4   5   9   6   1   7   0   0   0   6
08  4   5   9   6   2   8   0   0   0   6
09  4   5   9   6   7   3   0   0   0   6
10  4   5   9   7   1   8   0   0   0   6
11  4   5   9   7   6   3   0   0   0   6
12  4   5   9   8   3   1   0   0   0   6

可以清楚看出(N4+N5)%10=N6这样的规律。

现在回过头来看，那一堆decode语句：

01SELECT t.n1
02     ,DECODE(t.lvl,1,n.n,t.n2)
03     ,DECODE(t.lvl,2,n.n,t.n3)
04     ,DECODE(t.lvl,3,n.n,t.n4)
05     ,DECODE(t.lvl,4,n.n,t.n5)
06     ,DECODE(t.lvl,5,n.n,t.n6)
07     ,DECODE(t.lvl,6,n.n,t.n7)
08     ,DECODE(t.lvl,7,n.n,t.n8)
09     ,DECODE(t.lvl,8,n.n,t.n9)
10     ,t.lvl+1

每一次迭代都是“修改”修改一个列，与此反复经过9步迭代之后N1~N9都被“修改”了，最后，最外层查询语句作最终的验证筛选

1SELECT n1||n7||n4||' + '||n2||n8||n5||' = '||n3||n9||n6
2  FROM t
3WHERE lvl=9
4      AND n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6
5      ;

执行计划和统计信息如下，用时约0.2秒：

01-----------------------------------------------------------------------------------------------------------------------
02| Id  | Operation                                  | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
03-----------------------------------------------------------------------------------------------------------------------
04|   0 | SELECT STATEMENT                           |                          |     2 |   260 |     8   (0)| 00:00:01 |
05|   1 |  TEMP TABLE TRANSFORMATION                 |                          |       |       |            |          |
06|   2 |   LOAD AS SELECT                           | SYS_TEMP_0FD9D695D_55670 |       |       |            |          |
07|   3 |    COUNT                                   |                          |       |       |            |          |
08|*  4 |     CONNECT BY WITHOUT FILTERING           |                          |       |       |            |          |
09|   5 |      FAST DUAL                             |                          |     1 |       |     2   (0)| 00:00:01 |
10|*  6 |   VIEW                                     |                          |     2 |   260 |     6   (0)| 00:00:01 |
11|   7 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|                          |       |       |            |          |
12|*  8 |     VIEW                                   |                          |     1 |    13 |     2   (0)| 00:00:01 |
13|   9 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D695D_55670 |     1 |    13 |     2   (0)| 00:00:01 |
14|  10 |     NESTED LOOPS                           |                          |     1 |   143 |     4   (0)| 00:00:01 |
15|  11 |      RECURSIVE WITH PUMP                   |                          |       |       |            |          |
16|* 12 |      VIEW                                  |                          |     1 |    13 |     2   (0)| 00:00:01 |
17|  13 |       TABLE ACCESS FULL                    | SYS_TEMP_0FD9D695D_55670 |     1 |    13 |     2   (0)| 00:00:01 |
18-----------------------------------------------------------------------------------------------------------------------
19  
20Predicate Information (identified by operation id):
21---------------------------------------------------
22  
23   4 - filter(ROWNUM<=9)
24   6 - filter("LVL"=9 AND "N1"*100+"N7"*10+"N4"+"N2"*100+"N8"*10+"N5"="N3"*100+"N9"*10+"N6")
25   8 - filter("N"<=4)
26  12 - filter("N"."N"<>"N1" AND "N"."N"<>"N2" AND "N"."N"<>"N3" AND "N"."N"<>"N4" AND "N"."N"<>"N5" AND
27              "N"."N"<>"N6" AND "N"."N"<>"N7" AND "N"."N"<>"N8" AND "N"."N"<>"N9" AND ("T"."LVL"=1 AND "N"."N">"T"."N1" AND
28              "N"."N"+"T"."N1"<=9 OR "T"."LVL"=2 AND ("N"."N"-"T"."N1"-"T"."N2"=0 OR "N"."N"-"T"."N1"-"T"."N2"=1) OR
29              "T"."LVL"=5 AND "N"."N"=MOD("T"."N4"+"T"."N5",10) OR ("T"."LVL"=3 OR "T"."LVL"=4 OR "T"."LVL"=6 OR "T"."LVL"=7
30              OR "T"."LVL"=8)))
31  
32  
33Statistics
34----------------------------------------------------------
35          3  recursive calls
36       4023  db block gets
37      22960  consistent gets
38          1  physical reads
39        600  redo size
40       5566  bytes sent via SQL*Net to client
41        644  bytes received via SQL*Net from client
42         13  SQL*Net roundtrips to/from client
43         11  sorts (memory)
44          0  sorts (disk)
45        168  rows processed

newkid 的思想是，每次迭代都用条件限制相应的结果集，我的优化思想是尽量限制每一步的结果集，每一步的结果集越少，下一步产生的结果集就越少，于是我修改newkid的SQL语句中的条件语句。

我在第7和第8步加入了判断条件，更精确地控制这两步产生的结果集：

01WITH n AS (
02   SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9
03   )
04,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS (
05   SELECT n,0,0,0,0,0,0,0,0,1
06     FROM n
07    WHERE n<=4
08   UNION ALL
09   SELECT t.n1
10         ,DECODE(t.lvl,1,n.n,t.n2)
11         ,DECODE(t.lvl,2,n.n,t.n3)
12         ,DECODE(t.lvl,3,n.n,t.n4)
13         ,DECODE(t.lvl,4,n.n,t.n5)
14         ,DECODE(t.lvl,5,n.n,t.n6)
15         ,DECODE(t.lvl,6,n.n,t.n7)
16         ,DECODE(t.lvl,7,n.n,t.n8)
17         ,DECODE(t.lvl,8,n.n,t.n9)
18         ,t.lvl+1
19     FROM t,n
20    WHERE n.n NOT IN (n1,n2,n3,n4,n5,n6,n7,n8,n9)
21          AND (t.lvl=1 AND n.n>t.n1 AND n.n+t.n1<=9
22               OR (t.lvl=2 AND n.n - t.n1- t.n2 IN (0,1) AND n.n>t.n1)
23               OR (t.lvl=5 AND n.n = MOD(t.n4+ t.n5,10))
24               OR (t.lvl=7 AND trunc( ((t.n7 + n.n)*10+t.n4+t.n5)/100 )=t.n3-t.n2-t.n1)
25               OR (t.lvl=8 AND (t.n3 - t.n1 - t.n2 )*10 + n.n - t.n7 - t.n8 = trunc((t.n4+t.n5)/10) )              
26               OR t.lvl IN (3,4,6)
27              )
28   )
29SELECT n1||n7||n4||' + '||n2||n8||n5||' = '||n3||n9||n6
30  FROM t
31WHERE lvl=9
32      AND n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6

执行计划如下，用时约0.11秒：

01------------------------------------------------------------------------------------------------------------------------
02| Id  | Operation                                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
03------------------------------------------------------------------------------------------------------------------------
04|   0 | SELECT STATEMENT                            |                          |     2 |   260 |     9  (12)| 00:00:01 |
05|   1 |  TEMP TABLE TRANSFORMATION                  |                          |       |       |            |          |
06|   2 |   LOAD AS SELECT                            | SYS_TEMP_0FD9D6975_55670 |       |       |            |          |
07|   3 |    COUNT                                    |                          |       |       |            |          |
08|*  4 |     CONNECT BY WITHOUT FILTERING            |                          |       |       |            |          |
09|   5 |      FAST DUAL                              |                          |     1 |       |     2   (0)| 00:00:01 |
10|   6 |   SORT ORDER BY                             |                          |     2 |   260 |     7  (15)| 00:00:01 |
11|*  7 |    VIEW                                     |                          |     2 |   260 |     6   (0)| 00:00:01 |
12|   8 |     UNION ALL (RECURSIVE WITH) BREADTH FIRST|                          |       |       |            |          |
13|*  9 |      VIEW                                   |                          |     1 |    13 |     2   (0)| 00:00:01 |
14|  10 |       TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6975_55670 |     1 |    13 |     2   (0)| 00:00:01 |
15|  11 |      NESTED LOOPS                           |                          |     1 |   143 |     4   (0)| 00:00:01 |
16|  12 |       RECURSIVE WITH PUMP                   |                          |       |       |            |          |
17|* 13 |       VIEW                                  |                          |     1 |    13 |     2   (0)| 00:00:01 |
18|  14 |        TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6975_55670 |     1 |    13 |     2   (0)| 00:00:01 |
19------------------------------------------------------------------------------------------------------------------------
20  
21Predicate Information (identified by operation id):
22---------------------------------------------------
23  
24   4 - filter(ROWNUM<=9)
25   7 - filter("LVL"=9 AND "N1"*100+"N7"*10+"N4"+"N2"*100+"N8"*10+"N5"="N3"*100+"N9"*10+"N6")
26   9 - filter("N"<=4)
27  13 - filter("N"."N"<>"N1" AND "N"."N"<>"N2" AND "N"."N"<>"N3" AND "N"."N"<>"N4" AND "N"."N"<>"N5" AND
28              "N"."N"<>"N6" AND "N"."N"<>"N7" AND "N"."N"<>"N8" AND "N"."N"<>"N9" AND ("T"."LVL"=1 AND "N"."N">"T"."N1" AND
29              "N"."N"+"T"."N1"<=9 OR "T"."LVL"=2 AND ("N"."N"-"T"."N1"-"T"."N2"=0 OR "N"."N"-"T"."N1"-"T"."N2"=1) OR
30              "T"."LVL"=5 AND "N"."N"=MOD("T"."N4"+"T"."N5",10) OR "T"."LVL"=7 AND
31              TRUNC((("T"."N7"+"N"."N")*10+"T"."N4"+"T"."N5")/100)="T"."N3"-"T"."N2"-"T"."N1" OR "T"."LVL"=8 AND
32              ("T"."N3"-"T"."N1"-"T"."N2")*10+"N"."N"-"T"."N7"-"T"."N8"=TRUNC(("T"."N4"+"T"."N5")/10) OR ("T"."LVL"=3 OR
33              "T"."LVL"=4 OR "T"."LVL"=6)))
34  
35  
36Statistics
37----------------------------------------------------------
38          3  recursive calls
39        330  db block gets
40      12221  consistent gets
41          1  physical reads
42        556  redo size
43       5566  bytes sent via SQL*Net to client
44        644  bytes received via SQL*Net from client
45         13  SQL*Net roundtrips to/from client
46         12  sorts (memory)
47          0  sorts (disk)
48        168  rows processed

 

一开始看到这题之后，我第一个想法是按照我最初做数独题目的时候，用几个表来联接，在一个“大包围”的方案中进行筛选，于是我尝试写了一下：

01WITH n AS (
02   SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9
03   ),
04tmp as (
05   select n1.n n1 , n2.n n2, n3.n n3 , 
06          n4.n n4 , n5.n n5, n6.n n6 , 
07          n7.n n7 , n8.n n8, n9.n n9
08   from n n1,n n2,n n3,n n4,n n5,n n6,n n7,n n8,n n9
09   where n1.n between 1 and 4
10     and n1.n + n4.n <= 9 /*
11     and n7.n - n1.n - n4.n = trunc(( ( n2.n + n5.n )  + (n3.n + n6.n - n9.n)/10 )/10)*/
12     and n8.n - n2.n - n5.n = (n3.n + n6.n - n9.n)/10
13     and mod( n3.n + n6.n ,10 ) = n9.n
14     and n1.n < n4.n
15     and n1.n*100+n2.n*10+n3.n + n4.n*100+n5.n*10+n6.n = n7.n*100+n8.n*10+n9.n
16     and n1.n not in (       n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n)
17     and n2.n not in ( n1.n,       n3.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n)
18     and n3.n not in ( n1.n, n2.n,       n4.n, n5.n, n6.n, n7.n, n8.n, n9.n)
19     and n4.n not in ( n1.n, n2.n, n3.n,       n5.n, n6.n, n7.n, n8.n, n9.n)
20     and n5.n not in ( n1.n, n2.n, n3.n, n4.n,       n6.n, n7.n, n8.n, n9.n)
21     and n6.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n,       n7.n, n8.n, n9.n)
22     and n7.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n,       n8.n, n9.n)
23     and n8.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n7.n,       n9.n)
24     and n9.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n      )
25)
26select * from tmp;

这里没什么算法可言，首先是9个1~9的序列表进行连接，联接的条件包括，其中直接就把题意的写到联接条件中

1n1.n*100+n2.n*10+n3.n + n4.n*100+n5.n*10+n6.n = n7.n*100+n8.n*10+n9.n

当然还有9个数字不重复出现的条件

1and n1.n not in (       n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n)
2and n2.n not in ( n1.n,       n3.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n)
3and n3.n not in ( n1.n, n2.n,       n4.n, n5.n, n6.n, n7.n, n8.n, n9.n)
4and n4.n not in ( n1.n, n2.n, n3.n,       n5.n, n6.n, n7.n, n8.n, n9.n)
5and n5.n not in ( n1.n, n2.n, n3.n, n4.n,       n6.n, n7.n, n8.n, n9.n)
6and n6.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n,       n7.n, n8.n, n9.n)
7and n7.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n,       n8.n, n9.n)
8and n8.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n7.n,       n9.n)
9and n9.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n      )

然后之前的条件是经过我们推导得出的“优化”条件。

执行计划和统计信息如下，用时约0.23秒：

01------------------------------------------------------------------------------------------------------------
02| Id  | Operation                       | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
03------------------------------------------------------------------------------------------------------------
04|   0 | SELECT STATEMENT                |                          |     1 |   117 |    20   (0)| 00:00:01 |
05|   1 |  TEMP TABLE TRANSFORMATION      |                          |       |       |            |          |
06|   2 |   LOAD AS SELECT                | SYS_TEMP_0FD9D6999_55670 |       |       |            |          |
07|   3 |    COUNT                        |                          |       |       |            |          |
08|*  4 |     CONNECT BY WITHOUT FILTERING|                          |       |       |            |          |
09|   5 |      FAST DUAL                  |                          |     1 |       |     2   (0)| 00:00:01 |
10|   6 |   NESTED LOOPS                  |                          |     1 |   117 |    18   (0)| 00:00:01 |
11|   7 |    NESTED LOOPS                 |                          |     1 |   104 |    16   (0)| 00:00:01 |
12|   8 |     NESTED LOOPS                |                          |     1 |    91 |    14   (0)| 00:00:01 |
13|   9 |      NESTED LOOPS               |                          |     1 |    78 |    12   (0)| 00:00:01 |
14|  10 |       NESTED LOOPS              |                          |     1 |    65 |    10   (0)| 00:00:01 |
15|  11 |        NESTED LOOPS             |                          |     1 |    52 |     8   (0)| 00:00:01 |
16|  12 |         NESTED LOOPS            |                          |     1 |    39 |     6   (0)| 00:00:01 |
17|  13 |          NESTED LOOPS           |                          |     1 |    26 |     4   (0)| 00:00:01 |
18|  14 |           VIEW                  |                          |     1 |    13 |     2   (0)| 00:00:01 |
19|  15 |            TABLE ACCESS FULL    | SYS_TEMP_0FD9D6999_55670 |     1 |    13 |     2   (0)| 00:00:01 |
20|* 16 |           VIEW                  |                          |     1 |    13 |     2   (0)| 00:00:01 |
21|  17 |            TABLE ACCESS FULL    | SYS_TEMP_0FD9D6999_55670 |     1 |    13 |     2   (0)| 00:00:01 |
22|* 18 |          VIEW                   |                          |     1 |    13 |     2   (0)| 00:00:01 |
23|  19 |           TABLE ACCESS FULL     | SYS_TEMP_0FD9D6999_55670 |     1 |    13 |     2   (0)| 00:00:01 |
24|* 20 |         VIEW                    |                          |     1 |    13 |     2   (0)| 00:00:01 |
25|  21 |          TABLE ACCESS FULL      | SYS_TEMP_0FD9D6999_55670 |     1 |    13 |     2   (0)| 00:00:01 |
26|* 22 |        VIEW                     |                          |     1 |    13 |     2   (0)| 00:00:01 |
27|  23 |         TABLE ACCESS FULL       | SYS_TEMP_0FD9D6999_55670 |     1 |    13 |     2   (0)| 00:00:01 |
28|* 24 |       VIEW                      |                          |     1 |    13 |     2   (0)| 00:00:01 |
29|  25 |        TABLE ACCESS FULL        | SYS_TEMP_0FD9D6999_55670 |     1 |    13 |     2   (0)| 00:00:01 |
30|* 26 |      VIEW                       |                          |     1 |    13 |     2   (0)| 00:00:01 |
31|  27 |       TABLE ACCESS FULL         | SYS_TEMP_0FD9D6999_55670 |     1 |    13 |     2   (0)| 00:00:01 |
32|* 28 |     VIEW                        |                          |     1 |    13 |     2   (0)| 00:00:01 |
33|  29 |      TABLE ACCESS FULL          | SYS_TEMP_0FD9D6999_55670 |     1 |    13 |     2   (0)| 00:00:01 |
34|* 30 |    VIEW                         |                          |     1 |    13 |     2   (0)| 00:00:01 |
35|  31 |     TABLE ACCESS FULL           | SYS_TEMP_0FD9D6999_55670 |     1 |    13 |     2   (0)| 00:00:01 |
36------------------------------------------------------------------------------------------------------------
37  
38Predicate Information (identified by operation id):
39---------------------------------------------------
40  
41   4 - filter(ROWNUM<=9)
42  16 - filter("N8"."N"<>"N9"."N")
43  18 - filter("N7"."N"<>"N8"."N" AND "N7"."N"<>"N9"."N")
44  20 - filter("N6"."N"<>"N7"."N" AND "N6"."N"<>"N8"."N" AND "N6"."N"<>"N9"."N")
45  22 - filter("N9"."N"=MOD("N3"."N"+"N6"."N",10) AND "N3"."N"<>"N6"."N" AND "N3"."N"<>"N7"."N" AND
46              "N3"."N"<>"N8"."N" AND "N3"."N"<>"N9"."N")
47  24 - filter("N3"."N"<>"N5"."N" AND "N5"."N"<>"N6"."N" AND "N5"."N"<>"N7"."N" AND
48              "N5"."N"<>"N8"."N" AND "N5"."N"<>"N9"."N")
49  26 - filter("N2"."N"<>"N3"."N" AND "N2"."N"<>"N5"."N" AND "N2"."N"<>"N6"."N" AND
50              "N2"."N"<>"N7"."N" AND "N2"."N"<>"N8"."N" AND "N2"."N"<>"N9"."N" AND
51              "N8"."N"-"N2"."N"-"N5"."N"=("N3"."N"+"N6"."N"-"N9"."N")/10)
52  28 - filter("N2"."N"<>"N4"."N" AND "N3"."N"<>"N4"."N" AND "N4"."N"<>"N5"."N" AND
53              "N4"."N"<>"N6"."N" AND "N4"."N"<>"N7"."N" AND "N4"."N"<>"N8"."N" AND "N4"."N"<>"N9"."N" AND
54              "N4"."N">1)
55  30 - filter("N1"."N">=1 AND "N1"."N"<=4 AND "N1"."N"+"N4"."N"<=9 AND "N1"."N"<"N4"."N" AND
56              "N1"."N"*100+"N2"."N"*10+"N3"."N"+"N4"."N"*100+"N5"."N"*10+"N6"."N"="N7"."N"*100+"N8"."N"*10+"N9"."N
57              " AND "N1"."N"<>"N2"."N" AND "N1"."N"<>"N3"."N" AND "N1"."N"<>"N4"."N" AND "N1"."N"<>"N5"."N" AND
58              "N1"."N"<>"N6"."N" AND "N1"."N"<>"N7"."N" AND "N1"."N"<>"N8"."N" AND "N1"."N"<>"N9"."N")
59  
60  
61Statistics
62----------------------------------------------------------
63          2  recursive calls
64          8  db block gets
65      49879  consistent gets
66          1  physical reads
67        600  redo size
68       3501  bytes sent via SQL*Net to client
69        578  bytes received via SQL*Net from client
70          7  SQL*Net roundtrips to/from client
71          1  sorts (memory)
72          0  sorts (disk)
73         84  rows processed

虽然效率不怎么样，但是比预想中好，consistent gets仅比newkid的多3倍，比szusunn（以及OO修改后的版本）的方法少8倍。

这充分说明SQL是一种声明式语言，具体实施交给数据库引擎去做的事实。

面试那些事儿：Java全栈技术在电商订单处理中的趣味问答[特殊字符]
weixin_47523390的博客
04-24 41
面试官：李小梅，欢迎你来到我们互联网大厂的技术面试现场。今天我们聚焦电商订单处理系统，看看你对Java全栈技术的掌握如何。准备好了吗？😎李小梅：准备好了，面试官大人，来吧，我的Java武器已经上膛！🔫。
SQL趣味练习题
乐弈的专栏
06-12 733
--第一中情况 create table renwu ( name varchar(20) not null, fahter varchar(20) ) insert into renwu values('小甲','大甲'); insert into renwu values('大甲','老甲'); insert into renwu (name) values('老甲'); insert
[原]简单分析《趣味题》中的SQL
weixin_34159110的博客
07-18 171
这是原文地址《趣味题》，题目是这样的： 1～9有9个数字，三个三个一组，可能正好能组成一个加法等式比如：124＋659＝783 首先是szusunny在8楼给出了一个解决方案： with tmp as( select a.num from (select rownum as num from dual connect by rownum &lt;= 999) a where substr...
sql趣味题
kmblack1的专栏
04-04 535
用sql实现从0累加到100 with recursive cte(id,val) as ( values(0,0) union all select (id+1),((id+1)+val) from cte where id&amp;amp;lt;=100 )select val from cte where id=100; 结果为5050...
趣味sql（九九乘法表）
weixin_33671935的博客
03-02 156
select r1 || '*' || r1 || '=' || r1 * r1 A, decode(r2, '', '', r2 || '*' || r1 || '=' || r2 * r1) b, decode(r3, '', '', r3 || '*' || r1 || '=' || r3 * r1) C, decode(r4, ''...
有趣的SQL
u010835426的博客
07-29 270
-- 一张 match 的比赛表，里面只有一个字段name, 一共有4 条纪录，分别是T1,T2,T3,T4 -- 对应四个球队，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合. select m1.name , m2.name from match m1 , match m2 where m1.name < m2.name 结果： Team1 Team2 ...
SQL Server 2005数据库电子教案快速入门指南
2005版本是SQL Server数据库产品线中的一个分支，它在当时提供了企业级的数据管理和分析解决方案。 知识点一：SQL Server 2005特性与架构 SQL Server 2005具备了多项特性，例如：增强的报表功能、集成的复制功能、...
【网页设计案例分析课】：期末考试题与答案深入探讨
![【网页设计案例分析课】：期末考试题与答案...随后，文章探讨了设计理念在网页设计中的体现，最佳实践的代码实现方法，以及应对考试题目的策略。进阶应用章节着重于高级前端框架的应用、交互设计与用户体验的提升以及
2021-2022计算机二级等级考试试题及答案No.10346.docx
10-25
- **解析**: C++语言程序中的简单语句必须以分号结束。这是C++语言的基本语法规则之一，用于区分不同的语句。 - **扩展**: 此外，C++允许使用多行书写语句，注释可以出现在代码的任意位置，而不仅仅是在同一行。...
【AP Classroom学习进度追踪】：数据分析在教育中的神奇作用
![【AP Classroom学习进度追踪】：数据分析在教育中的神奇作用](https://www.frontiersin.org/files/Articles/767837/fpsyg-12-767837-HTML/image_m/fpsyg-12-767837-g001.jpg) ...教育数据的分析并不简单是数
趣味 SQL：使用蒙特卡洛方法计算圆周率 Pi 的值
12-14
圆周率（Pi）是圆的周长与直径的比值，一般用希腊字母 π 表示，是一个在数学及物理学中普遍存在的数学常数。π 也等于圆形之面积与半径平方之比，是精确计算圆周长、圆面积、球体积等几何形状的关键值。 蒙特卡洛方法（Monte Carlo method）也称统计模拟法、统计试验法。蒙特卡洛方法的基本思想是：首先建立一个描述问题的概率模型或随机过程，使它的参数或数字特征等于问题的解；然后通过对模型或过程的观察或抽样试验来计算这些参数或数字特征；最后给出所求解的近似值。 使用蒙特卡洛法求解 π 值基本步骤如下：构造一个单位正方形和一个单位圆的 1/4；往正方形内随机投点，根据点到原点的距离是否大于 1
有趣的SQL(一)
老叶茶馆
07-18 271
大家都想提高自己的SQL能力，但是SQL能力也是需要慢慢提高的，为了让大家的SQL有所提高，特整理了下面的SQL。需求：如下所示，有从1开始的2的幂的数组- - 1, 2...
用SQL解决两道有趣的题（一）
SkidRow_xp的博客
06-24 265
Oracle的SQL语句功能还是很强的，看到两道比较有趣的题，用SQL来尝试求解。 第一个问题： 已知两个1~30之间的数字，甲知道两数之和，乙知道两数之积。 甲问乙："你知道是哪两个数吗？"乙说：“不知道”； 乙问甲："你知道是哪两个数吗？"甲说：“也不知道”； 于是，乙说：“那我知道了”； 随后甲也说：“那我也知道了”； 这两个数是什么？ 先给出SQL解，然后简单描述一下。 这道题分两种情况，两个数不重复，那么有两个可能结果： SQL> WITH 2 T AS 3 (SELECT ROWNUM N
趣味题sql，1-9，三个数字一组，前两组之后等于第三组
cmodnsbs880547的博客
03-09 205
在论坛上看到一篇帖子，尝试了写一下，发现基本上所有人都自连接了三次，笛卡尔积非常大，所以效率特别低，我的写法，只需要自连接两次，效率提升很多，不过我判断是否有重复数字的步骤，不是最好的，但是效率仍然没的说，毕竟笛卡尔积小了...
些许有趣的SQL
独者looc
01-17 456
1.从MySQL中导出数据库 # mysqldump -u root -p 被导出数据库名(例：movie) > 导出文件名(movie.sql) Enter password: 2.将*.sql文件导入数据库 首先创建一个数据库 MariaDB [(none)]>create database movie; 然后选择该数据库 MariaDB [(non
sql趣味自增
qq_39062888的博客
04-09 163
Purpose：改变auto_increment自增的起始值 众所周知 当一个表的主键id设置auto_increment时，每插入一条数据，id就会自动加1，即使清空表以后，id的值依然会保存，当再插入数据时，id会在原有id值上接着加1。 1、创建一个表 create table per( id int PRIMARY KEY auto_increment, name VARCHAR(20) ...
一些有趣的SQL题目
我想我是海 冬天的大海 心情随风轻摆
09-20 1846
题目来自: 点击打开链接 下面是本人的答案： --一下语句中用到的表tb均指这个测试表 --1、你猜下面这条语句能得到什么结果，这个很常用 if object_id('tb') is not null drop table tb go select getdate() create table tb(id int identity ,name varchar(10)) insert into
一个有趣的SQL优化案例
qq_39744051的博客
04-20 657
问题描述 分析 表的信息 估算cost start-up cost run cost 执行计划 实际执行时间 从内核视角来分析 解决方案 写在最后 Coding过程中经常会写SQL语句，有时写的SQL出现慢查询而被DBA鄙视。 今天我们一起从使用者，DBA，内核开发三个不同角度来分析和解决一个SQL性能问题，来重新认识逻辑优化与物理优化。 ..
趣味SQL：用SQL计算瓷砖费用
weixin_30270561的博客
01-14 107
最近家里在装修，于是去建材市场看瓷砖，问了不同型号瓷砖的价格。 问了装修的师傅，厨房、卫生间分别需要多少面积的墙砖、地砖。 一开始用计算机，计算到头晕，完全计算不对。 于是，想想可以用sql来计算，很方便，有准确。 代码如下： select t1.prod_name, t1.price [每块瓷砖的价格], t1.area [...
高效与趣味并存：SQL语句集锦
本文档是一份关于SQL语句的搞笑且实用的总结，旨在帮助学习者提升在Access数据库管理系统中的SQL技巧。以下是一些关键知识点的详细介绍： 1. 空值比较：SQL查询中，使用`1<>1`这样的条件来表示空值（NULL）的检查，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值