深夜独霸, 轮到我出个题目了——算24点的SQL解法

阿日 · 发表于 2008-9-25 12:50

大开眼界呀

廖猫猫 · 发表于 2008-9-25 17:33

强！　庆祝国庆。

newkid · 发表于 2008-9-25 21:58

多谢版主！只是别挂在这示众了，没什么技术含量。

jvkojvko · 发表于 2008-9-25 22:32

佩服
...

wuhuaT · 发表于 2008-9-26 18:16

原帖由 newkid 于 2008-9-25 21:58 发表
多谢版主！只是别挂在这示众了，没什么技术含量。

别谦虚,值得加精

ttxing · 发表于 2008-9-27 10:30

[php]
--只能计算10以内的（不包括10）4个不重复整数之间的不重复组合结果
--如果大于等于10需要考虑如何避免重复

--构造 (a b) (c d)
with tall
as(
select distinct greatest(n1,n2) * least(n2,n1) n12_mu,greatest(n1,n2)||' * '||least(n2,n1) title
  from (select a.n n1,
            b.n n2
      from t24 a,
            t24 b)
where n1 != n2
union all
select distinct greatest(n1,n2) - least(n2,n1) n12_mu,greatest(n1,n2)||' - '||least(n2,n1) title
  from (select a.n n1,
            b.n n2
      from t24 a,
            t24 b)
where n1 != n2
union all
select distinct greatest(n1,n2) / least(n2,n1) n12_mu,greatest(n1,n2)||' /'||least(n2,n1) title
  from (select a.n n1,
            b.n n2
      from t24 a,
            t24 b)
where n1 != n2
and mod(greatest(n1,n2) , least(n2,n1))=0
union all
select distinct greatest(n1,n2) + least(n2,n1) n12_mu,greatest(n1,n2)||' +'||least(n2,n1) title
  from (select a.n n1,
            b.n n2
      from t24 a,
            t24 b)
where n1 != n2
),
--构造 ((a b) c)
tall2 as(
select a.n12_mu*b.n n,'('||a.title||')*'||b.n title from tall a, t24 b
where substrb(a.title,1,1)!=n
and  substrb(a.title,-1,1)!=n
union all
select a.n12_mu-b.n n,'('||a.title||')-'||b.n title from tall a, t24 b
where substrb(a.title,1,1)!=n
and  substrb(a.title,-1,1)!=n
union all
select a.n12_mu/b.n n,'('||a.title||')/'||b.n title from tall a, t24 b
where substrb(a.title,1,1)!=n
and  substrb(a.title,-1,1)!=n
union all
select a.n12_mu+b.n n,'('||a.title||')+'||b.n title from tall a, t24 b
where substrb(a.title,1,1)!=n
and  substrb(a.title,-1,1)!=n),
--构造 ((a b) (c d))
tall4 as(select a.n12_mu *b.n12_mu n,'('||a.title||')'||'*'||'('||b.title||')' title from tall a, tall b
where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
and substrb(a.title,-1,1)!=substrb(b.title,1,1)
and substrb(a.title,1,1)!=substrb(b.title,1,1)
and substrb(a.title,1,1)!=substrb(b.title,-1,1)
union all
select a.n12_mu - b.n12_mu val,'('||a.title||')'||'-'||'('||b.title||')' title from tall a, tall b
where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
and substrb(a.title,-1,1)!=substrb(b.title,1,1)
and substrb(a.title,1,1)!=substrb(b.title,1,1)
and substrb(a.title,1,1)!=substrb(b.title,-1,1)
union all
select a.n12_mu / b.n12_mu val,'('||a.title||')'||'/'||'('||b.title||')' title from tall a, tall b
where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
and substrb(a.title,-1,1)!=substrb(b.title,1,1)
and substrb(a.title,1,1)!=substrb(b.title,1,1)
and substrb(a.title,1,1)!=substrb(b.title,-1,1)
union all
select a.n12_mu + b.n12_mu val,'('||a.title||')'||'+'||'('||b.title||')' title from tall a, tall b
where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
and substrb(a.title,-1,1)!=substrb(b.title,1,1)
and substrb(a.title,1,1)!=substrb(b.title,1,1)
and substrb(a.title,1,1)!=substrb(b.title,-1,1)
),
--构造 (((a b) c) d)
tall3 as(
select a.n*b.n n, '('||a.title||')*'||b.n title
from tall2 a, t24 b
where a.title not like '%'||b.n||'%'
union all
select a.n+b.n n, '('||a.title||')+'||b.n title
from tall2 a, t24 b
where a.title not like '%'||b.n||'%'
union all
select a.n/b.n n, '('||a.title||')/'||b.n title
from tall2 a, t24 b
where a.title not like '%'||b.n||'%'
union all
select a.n-b.n n, '('||a.title||')-'||b.n title
from tall2 a, t24 b
where a.title not like '%'||b.n||'%')
select * from tall3
where n=24
union all
select * from tall4
where n=24;
[/php]

09:59:34 test@REG>select * from t24;

      N
----------
      1
      2
      3
      4

已用时间:  00: 00: 00.01
09:59:39 test@REG>--只能计算10以内的（不包括10）4个不重复整数之间的不重复组合结果
09:59:44 test@REG>--如果大于等于10需要考虑如何避免重复
09:59:44 test@REG>
09:59:44 test@REG>--构造 (a b) (c d)
09:59:44 test@REG>with tall
09:59:44 2  as(
09:59:44 3  select distinct greatest(n1,n2) * least(n2,n1) n12_mu,greatest(n1,n2)||' * '||least(n2,n1) title
09:59:44 4 from (select a.n n1,
09:59:44 5                b.n n2
09:59:44 6          from t24 a,
09:59:44 7                t24 b)
09:59:44 8 where n1 != n2
09:59:44 9 union all
09:59:44  10 select distinct greatest(n1,n2) - least(n2,n1) n12_mu,greatest(n1,n2)||' - '||least(n2,n1) title
09:59:44  11 from (select a.n n1,
09:59:44  12                b.n n2
09:59:44  13          from t24 a,
09:59:44  14                t24 b)
09:59:44  15 where n1 != n2
09:59:44  16 union all
09:59:44  17 select distinct greatest(n1,n2) / least(n2,n1) n12_mu,greatest(n1,n2)||' /'||least(n2,n1) title
09:59:44  18 from (select a.n n1,
09:59:44  19                b.n n2
09:59:44  20          from t24 a,
09:59:44  21                t24 b)
09:59:44  22 where n1 != n2
09:59:44  23 and mod(greatest(n1,n2) , least(n2,n1))=0
09:59:44  24 union all
09:59:44  25 select distinct greatest(n1,n2) + least(n2,n1) n12_mu,greatest(n1,n2)||' +'||least(n2,n1) title
09:59:44  26 from (select a.n n1,
09:59:44  27                b.n n2
09:59:44  28          from t24 a,
09:59:44  29                t24 b)
09:59:44  30 where n1 != n2
09:59:44  31  ),
09:59:44  32  --构造 ((a b) c)
09:59:44  33  tall2 as(
09:59:44  34  select a.n12_mu*b.n n,'('||a.title||')*'||b.n title from tall a, t24 b
09:59:44  35  where substrb(a.title,1,1)!=n
09:59:44  36  and  substrb(a.title,-1,1)!=n
09:59:44  37  union all
09:59:44  38  select a.n12_mu-b.n n,'('||a.title||')-'||b.n title from tall a, t24 b
09:59:44  39  where substrb(a.title,1,1)!=n
09:59:44  40  and  substrb(a.title,-1,1)!=n
09:59:44  41  union all
09:59:44  42  select a.n12_mu/b.n n,'('||a.title||')/'||b.n title from tall a, t24 b
09:59:44  43  where substrb(a.title,1,1)!=n
09:59:44  44  and  substrb(a.title,-1,1)!=n
09:59:44  45  union all
09:59:44  46  select a.n12_mu+b.n n,'('||a.title||')+'||b.n title from tall a, t24 b
09:59:44  47  where substrb(a.title,1,1)!=n
09:59:44  48  and  substrb(a.title,-1,1)!=n),
09:59:44  49  --构造 ((a b) (c d))
09:59:44  50  tall4 as(select a.n12_mu *b.n12_mu n,'('||a.title||')'||'*'||'('||b.title||')' title from tall a, tall b
09:59:44  51  where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
09:59:44  52  and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
09:59:44  53  and substrb(a.title,-1,1)!=substrb(b.title,1,1)
09:59:44  54  and substrb(a.title,1,1)!=substrb(b.title,1,1)
09:59:44  55  and substrb(a.title,1,1)!=substrb(b.title,-1,1)
09:59:44  56  union all
09:59:44  57  select a.n12_mu - b.n12_mu val,'('||a.title||')'||'-'||'('||b.title||')' title from tall a, tall b
09:59:44  58  where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
09:59:44  59  and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
09:59:44  60  and substrb(a.title,-1,1)!=substrb(b.title,1,1)
09:59:44  61  and substrb(a.title,1,1)!=substrb(b.title,1,1)
09:59:45  62  and substrb(a.title,1,1)!=substrb(b.title,-1,1)
09:59:45  63  union all
09:59:45  64  select a.n12_mu / b.n12_mu val,'('||a.title||')'||'/'||'('||b.title||')' title from tall a, tall b
09:59:45  65  where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
09:59:45  66  and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
09:59:45  67  and substrb(a.title,-1,1)!=substrb(b.title,1,1)
09:59:45  68  and substrb(a.title,1,1)!=substrb(b.title,1,1)
09:59:45  69  and substrb(a.title,1,1)!=substrb(b.title,-1,1)
09:59:45  70  union all
09:59:45  71  select a.n12_mu + b.n12_mu val,'('||a.title||')'||'+'||'('||b.title||')' title from tall a, tall b
09:59:45  72  where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
09:59:45  73  and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
09:59:45  74  and substrb(a.title,-1,1)!=substrb(b.title,1,1)
09:59:45  75  and substrb(a.title,1,1)!=substrb(b.title,1,1)
09:59:45  76  and substrb(a.title,1,1)!=substrb(b.title,-1,1)
09:59:45  77  ),
09:59:45  78  --构造 (((a b) c) d)
09:59:45  79  tall3 as(
09:59:45  80  select a.n*b.n n, '('||a.title||')*'||b.n title
09:59:45  81  from tall2 a, t24 b
09:59:45  82  where a.title not like '%'||b.n||'%'
09:59:45  83  union all
09:59:45  84  select a.n+b.n n, '('||a.title||')+'||b.n title
09:59:45  85  from tall2 a, t24 b
09:59:45  86  where a.title not like '%'||b.n||'%'
09:59:45  87  union all
09:59:45  88  select a.n/b.n n, '('||a.title||')/'||b.n title
09:59:45  89  from tall2 a, t24 b
09:59:45  90  where a.title not like '%'||b.n||'%'
09:59:45  91  union all
09:59:45  92  select a.n-b.n n, '('||a.title||')-'||b.n title
09:59:45  93  from tall2 a, t24 b
09:59:45  94  where a.title not like '%'||b.n||'%')
09:59:45  95  select * from tall3
09:59:45  96  where n=24
09:59:45  97  union all
09:59:45  98  select * from tall4
09:59:45  99  where n=24;

      N TITLE
---------- -------------------------
      24 ((3 * 2)*4)*1
      24 ((4 * 2)*3)*1
      24 ((4 * 3)*2)*1
      24 ((3 * 1)*4)*2
      24 ((4 * 1)*3)*2
      24 ((4 * 3)*1)*2
      24 ((3 /1)*4)*2
      24 ((4 /1)*3)*2
      24 ((4 * 3)/1)*2
      24 ((2 * 1)*4)*3
      24 ((4 * 1)*2)*3
      24 ((4 * 2)*1)*3
      24 ((2 /1)*4)*3
      24 ((4 /1)*2)*3
      24 ((4 * 2)/1)*3
      24 ((2 * 1)*3)*4
      24 ((3 * 1)*2)*4
      24 ((3 * 2)*1)*4
      24 ((2 /1)*3)*4
      24 ((3 /1)*2)*4
      24 ((3 * 2)/1)*4
      24 ((2 +1)+3)*4
      24 ((3 +1)+2)*4
      24 ((3 +2)+1)*4
      24 ((3 * 2)*4)/1
      24 ((4 * 2)*3)/1
      24 ((4 * 3)*2)/1
      24 (4 * 3)*(2 * 1)
      24 (4 * 2)*(3 * 1)
      24 (3 * 2)*(4 * 1)
      24 (4 * 1)*(3 * 2)
      24 (4 /1)*(3 * 2)
      24 (3 * 1)*(4 * 2)
      24 (3 /1)*(4 * 2)
      24 (2 * 1)*(4 * 3)
      24 (2 /1)*(4 * 3)
      24 (4 * 3)*(2 /1)
      24 (4 * 2)*(3 /1)
      24 (3 * 2)*(4 /1)
      24 (4 +2)*(3 +1)
      24 (3 +1)*(4 +2)

已选择41行。

已用时间:  00: 00: 00.10

09:59:54 test@REG>truncate table t24;

表被截断。

已用时间:  00: 00: 00.06
10:00:00 test@REG>insert into t24 values (3);

已创建 1 行。

已用时间:  00: 00: 00.03
10:00:15 test@REG>insert into t24 values (5);

已创建 1 行。

已用时间:  00: 00: 00.01
10:00:20 test@REG>insert into t24 values (6);

已创建 1 行。

已用时间:  00: 00: 00.00
10:00:23 test@REG>insert into t24 values (9);

已创建 1 行。

已用时间:  00: 00: 00.01
10:00:26 test@REG>commit;

提交完成。

已用时间:  00: 00: 00.01
10:00:28 test@REG>--只能计算10以内的（不包括10）4个不重复整数之间的不重复组合结果
10:00:57 test@REG>--如果大于等于10需要考虑如何避免重复
10:00:57 test@REG>
10:00:57 test@REG>--构造 (a b) (c d)
10:00:57 test@REG>with tall
10:00:57 2  as(
10:00:57 3  select distinct greatest(n1,n2) * least(n2,n1) n12_mu,greatest(n1,n2)||' * '||least(n2,n1) title
10:00:57 4 from (select a.n n1,
10:00:57 5                b.n n2
10:00:57 6          from t24 a,
10:00:57 7                t24 b)
10:00:57 8 where n1 != n2
10:00:57 9 union all
10:00:57  10 select distinct greatest(n1,n2) - least(n2,n1) n12_mu,greatest(n1,n2)||' - '||least(n2,n1) title
10:00:57  11 from (select a.n n1,
10:00:57  12                b.n n2
10:00:57  13          from t24 a,
10:00:57  14                t24 b)
10:00:57  15 where n1 != n2
10:00:57  16 union all
10:00:57  17 select distinct greatest(n1,n2) / least(n2,n1) n12_mu,greatest(n1,n2)||' /'||least(n2,n1) title
10:00:57  18 from (select a.n n1,
10:00:57  19                b.n n2
10:00:57  20          from t24 a,
10:00:57  21                t24 b)
10:00:57  22 where n1 != n2
10:00:57  23 and mod(greatest(n1,n2) , least(n2,n1))=0
10:00:57  24 union all
10:00:57  25 select distinct greatest(n1,n2) + least(n2,n1) n12_mu,greatest(n1,n2)||' +'||least(n2,n1) title
10:00:57  26 from (select a.n n1,
10:00:57  27                b.n n2
10:00:57  28          from t24 a,
10:00:57  29                t24 b)
10:00:57  30 where n1 != n2
10:00:57  31  ),
10:00:57  32  --构造 ((a b) c)
10:00:57  33  tall2 as(
10:00:57  34  select a.n12_mu*b.n n,'('||a.title||')*'||b.n title from tall a, t24 b
10:00:57  35  where substrb(a.title,1,1)!=n
10:00:57  36  and  substrb(a.title,-1,1)!=n
10:00:57  37  union all
10:00:57  38  select a.n12_mu-b.n n,'('||a.title||')-'||b.n title from tall a, t24 b
10:00:57  39  where substrb(a.title,1,1)!=n
10:00:57  40  and  substrb(a.title,-1,1)!=n
10:00:57  41  union all
10:00:57  42  select a.n12_mu/b.n n,'('||a.title||')/'||b.n title from tall a, t24 b
10:00:57  43  where substrb(a.title,1,1)!=n
10:00:57  44  and  substrb(a.title,-1,1)!=n
10:00:57  45  union all
10:00:57  46  select a.n12_mu+b.n n,'('||a.title||')+'||b.n title from tall a, t24 b
10:00:57  47  where substrb(a.title,1,1)!=n
10:00:57  48  and  substrb(a.title,-1,1)!=n),
10:00:57  49  --构造 ((a b) (c d))
10:00:57  50  tall4 as(select a.n12_mu *b.n12_mu n,'('||a.title||')'||'*'||'('||b.title||')' title from tall a, tall b
10:00:57  51  where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
10:00:57  52  and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
10:00:57  53  and substrb(a.title,-1,1)!=substrb(b.title,1,1)
10:00:57  54  and substrb(a.title,1,1)!=substrb(b.title,1,1)
10:00:57  55  and substrb(a.title,1,1)!=substrb(b.title,-1,1)
10:00:57  56  union all
10:00:57  57  select a.n12_mu - b.n12_mu val,'('||a.title||')'||'-'||'('||b.title||')' title from tall a, tall b
10:00:57  58  where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
10:00:57  59  and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
10:00:57  60  and substrb(a.title,-1,1)!=substrb(b.title,1,1)
10:00:57  61  and substrb(a.title,1,1)!=substrb(b.title,1,1)
10:00:57  62  and substrb(a.title,1,1)!=substrb(b.title,-1,1)
10:00:57  63  union all
10:00:57  64  select a.n12_mu / b.n12_mu val,'('||a.title||')'||'/'||'('||b.title||')' title from tall a, tall b
10:00:57  65  where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
10:00:57  66  and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
10:00:57  67  and substrb(a.title,-1,1)!=substrb(b.title,1,1)
10:00:57  68  and substrb(a.title,1,1)!=substrb(b.title,1,1)
10:00:57  69  and substrb(a.title,1,1)!=substrb(b.title,-1,1)
10:00:57  70  union all
10:00:57  71  select a.n12_mu + b.n12_mu val,'('||a.title||')'||'+'||'('||b.title||')' title from tall a, tall b
10:00:57  72  where replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')!=replace(replace(replace(replace(b.title,'*',''),'+',''),'-',''),'/','')
10:00:57  73  and substrb(a.title,-1,1)!=substrb(b.title,-1,1)
10:00:57  74  and substrb(a.title,-1,1)!=substrb(b.title,1,1)
10:00:57  75  and substrb(a.title,1,1)!=substrb(b.title,1,1)
10:00:57  76  and substrb(a.title,1,1)!=substrb(b.title,-1,1)
10:00:57  77  ),
10:00:57  78  --构造 (((a b) c) d)
10:00:57  79  tall3 as(
10:00:57  80  select a.n*b.n n, '('||a.title||')*'||b.n title
10:00:57  81  from tall2 a, t24 b
10:00:57  82  where a.title not like '%'||b.n||'%'
10:00:57  83  union all
10:00:57  84  select a.n+b.n n, '('||a.title||')+'||b.n title
10:00:57  85  from tall2 a, t24 b
10:00:57  86  where a.title not like '%'||b.n||'%'
10:00:57  87  union all
10:00:57  88  select a.n/b.n n, '('||a.title||')/'||b.n title
10:00:57  89  from tall2 a, t24 b
10:00:57  90  where a.title not like '%'||b.n||'%'
10:00:57  91  union all
10:00:57  92  select a.n-b.n n, '('||a.title||')-'||b.n title
10:00:57  93  from tall2 a, t24 b
10:00:57  94  where a.title not like '%'||b.n||'%')
10:00:57  95  select * from tall3
10:00:57  96  where n=24
10:00:57  97  union all
10:00:57  98  select * from tall4
10:00:57  99  where n=24;

      N TITLE
---------- -------------------------
      24 ((9 +5)-6)*3
      24 ((9 - 6)+5)*3
      24 ((6 * 5)-9)+3
      24 ((5 - 3)*9)+6
      24 ((6 - 3)*5)+9
      24 ((9 - 3)*5)-6
      24 ((6 +5)*3)-9
      24 ((6 * 5)+3)-9
      24 (5 +3)*(9 - 6)
      24 (9 - 6)*(5 +3)
      24 (6 * 5)-(9 - 3)

已选择11行。

已用时间:  00: 00: 00.12
10:00:59 test@REG>spool off

yangshuo · 发表于 2008-9-27 11:16

create table t24 (sn number,n number);
INSERT INTO t24 VALUES (1,3);
INSERT INTO t24 VALUES (2,3);
INSERT INTO t24 VALUES (3,4);
INSERT INTO t24 VALUES (4,5);
create table o24 (o varchar2 (1));
INSERT INTO o24 VALUES ('+');
INSERT INTO o24 VALUES ('-');
INSERT INTO o24 VALUES ('*');
INSERT INTO o24 VALUES ('/');
COMMIT;

CREATE OR REPLACE FUNCTION Get_Value(i_Str VARCHAR2) RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;  --自动事务
v_Value  NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT '||i_Str||' FROM DUAL' INTO v_Value;
  RETURN v_Value;
EXCEPTION
  WHEN OTHERS THEN
RETURN -1;
END;
/

SELECT Str1, Get_Value(Str1)
  FROM (SELECT '((' || N1 || O1 || N2 || ')' || O2 || N3 || ')' || O3 || N4 AS Str1
      FROM (SELECT a.n AS N1, b.n AS N2, c.n AS N3, d.n AS N4
               FROM T24 a, T24 b, T24 c, T24 d
               WHERE a.Sn <> b.Sn
               AND a.Sn <> c.Sn
               AND a.Sn <> d.Sn
               AND b.Sn <> c.Sn
               AND b.Sn <> d.Sn
               AND c.Sn <> d.Sn) m,
            (SELECT a.o AS O1, b.o AS O2, c.o AS O3 FROM O24 a, O24 b, O24 c) n
      UNION ALL
      SELECT '(' || N1 || O1 || N2 || ')' || O2 || '(' || N3 || O3 || N4 || ')' AS Str1
      FROM (SELECT a.n AS N1, b.n AS N2, c.n AS N3, d.n AS N4
               FROM T24 a, T24 b, T24 c, T24 d
               WHERE a.Sn <> b.Sn
               AND a.Sn <> c.Sn
               AND a.Sn <> d.Sn
               AND b.Sn <> c.Sn
               AND b.Sn <> d.Sn
               AND c.Sn <> d.Sn) m,
            (SELECT a.o AS O1, b.o AS O2, c.o AS O3 FROM O24 a, O24 b, O24 c) n

      )
WHERE Get_Value(Str1) = 24

顾而不问 · 发表于 2008-9-27 11:32

好东西，值得版主为你设精！

learner1314 · 发表于 2008-9-27 15:05

select D3||'='||to_char(a3) from (select  n1,n2,n3,n4,o1,o2,o3,case o3
      when '+' then
      a2+n4
      when '-' then
      a2-n4
      when '*' then
      a2*n4
      when '/' then
      a2/n4
   end a3, case o3
      when '+' then
      '('||d2||'+'||n4||')'
      when '-' then
         '('||d2||'-'||n4||')'
      when '*' then
         '('||d2||'*'||n4||')'
      when '/' then
         '('||d2||'/'||n4||')'
   end d3 from (select n1,n2,n3,n4,o1,o2,o3, case o2
      when '+' then
      '('||d1||'+'||n3||')'
      when '-' then
      '('||d1||'-'||n3||')'
      when '*' then
         '('||d1||'*'||n3||')'
      when '/' then
         '('||d1||'/'||n3||')'
   end d2,case o2
      when '+' then
      a1+n3
      when '-' then
      a1-n3
      when '*' then
      a1*n3
      when '/' then
      a1/n3
   end a2 from (
with t as (select s1,s2,s3,s4,n1,n2,n3,n4,e.o o1,f.o o2,g.o o3 from (
select s1, s2, s3, s4, n1, n2, n3, n4
from (select n n1, rownum s1 from t24) a
  cross join (select n n2, rownum s2 from t24) b
  cross join (select n n3, rownum s3 from t24) c
  cross join (select n n4, rownum s4 from t24) d
  where a.s1 <> b.s2
and a.s1 <> c.s3
and a.s1 <> d.s4
and b.s2 <> c.s3
and b.s2 <> d.s4
and c.s3 <> d.s4) cross join o24 e cross join o24 f cross join o24 g
order by s1,s2,s3,s4)
select  n1,n2,n3,n4,o1,o2,o3,case o1
      when '+' then
      n1 + n2
      when '-' then
      n1 - n2
      when '*' then
      n1 * n2
      when '/' then
      n1 / n2
   end a1,case o1
      when '+' then
      '('||n1||'+'||n2||')'
      when '-' then
      '('||n1||'-'||n2||')'
      when '*' then
      '('||n1||'*'||n2||')'
      when '/' then
      '('||n1||'/'||n2||')'
   end d1 from t))) where a3 = 24

newkid · 发表于 2008-9-28 04:18

#56 的 ttxing:
兄弟辛苦了! 其实这四个数可以相等的，所以你的tall构造有问题，也导致了tall4的连接写法有了问题。
replace(replace(replace(replace(a.title,'*',''),'+',''),'-',''),'/','')可以简单地用：
TRANSLATE(a.title,'+-/*','')
其他几个等我有时间慢慢看，谢谢大家参与!

[精华] 深夜独霸, 轮到我出个题目了——算24点的SQL解法

浏览过的版块