SQL趣味解题:24点

SQL趣味解题：24点

规则：

任意抽取4张牌(称为牌组)，用加、减、乘、除，括号，把牌面上的数算成24。每张牌必须用且只能用一次。

请用一条SQL给出扑克牌24点的所有的计算表达式。

解题思路分析：

题目要求找出所有的计算表达式，因涉及4个数字，4个运算符，以及括号，则解题就转化为如何借助SQL列出所有的排列组合。

1）4张牌，我们先做如下简单替代：第一张牌记为a,第二张牌记为b,第三张牌记为c,第四张牌记为d。

因为括号可以改变运算的顺序，先括号，后乘除，再加减，所以有必要先分析一下括号的影响。

首先，建立4个数字的模型A：

位置1 a 位置2 b 位置3 c 位置4 d 位置5

因为括号必须成对出现在上面的位置1到5处，那么有效的括号组合模型就是如下：

一个括号的情况下的组合为：

• (a b) c d
• a (b c) d
• a b (c d)
• (a b c) d
• a (b c d)

两个括号的的情况下的组合为：

• (a b) (c d)

当我们将带有括号的结果看成一个结果值，例如：(a b) 转化为 x，则上面的部分组合模型可以再次演变为3个值的模型B:

位置1 x 位置2 y 位置3 z 位置4

同上理，一个括号的情况下的组合为：

• (x y) z
• x (y z)

不存在2个括号的组合。

最终结合模型A的所有组合与模型B的所有组合，可以得到所有的最终有效组合如下：

• ( ( a b ) c ) d
• ( a ( b c ) ) d
• a ( ( b c ) d )
• a ( b ( c d ) )
• ( a b ) ( c d )

2）接下来，我们考虑加入运算符。观察第一步的最终结果，不难发现，所有的组合都是基于2个数字的之上再运算的，则构造2个数字与运算符的组合就是所有组合的基础，且因为两个数字之间的运算符可能为任意一个（加减乘除），所以在做除法运算的时候，要特别注意0的情况。

因a b均可以为任意一个数字（特别注意：可以排除数字重复使用的情况，减少运算量，重复使用指的是，同一张牌被使用大于1次），则任意2个数字与运算符的组合就转化为构造笛卡儿集。

3）考虑a b c d，均只能使用一次，这里不能简单的做a 不等于 其它3个数字的判断。因为存在梅花6与红桃6 ，两张相同牌值的情况。所以，我们做个简单标记：a 标记为1000，b标记为100，c标记为10，d标记为1。当标记和等于1111的，则做为有效组合。

4）最终，我们构造a b c d的集合，作为最基本的集合，与运算符一起参与集合运算，就得到了任意2个数字与运算符的基本集合。

5）有了第4步产生的集合，接下来，就是按照第1步骤的有效组合进行集合的运算了。

6）最后，获取运算值为24的，且标志和为1111的就是最终结果。

注意：因我的实现是基于Postgresql，这里有个小问题需要特别注意：

形如：6*6*(2/3)，结果不是24，而是0。

6.0*6.0*(2.0/3.0)，结果也不是24.0，而是24.0000000000000000001200。

所以，最后我们需要做个简单四舍五入，round(value,10)，来进行判断是否等于24。

下面附上完整的SQL脚本。

--创建表

create table pt (id serial,a smallint,b smallint,c smallint,d smallint);

--插入一条数据

insert into pt (a,b,c,d) values (5,6,7,8);

-- 解题SQL

with cte_source (id,value,tag)

as (

select id,a*1.0,1000 from pt

union all

select id,b*1.0,100 from pt

union all

select id,c*1.0,10 from pt

union all

select id,d*1.0,1 from pt

),

cte_operators (opt)

as (

select '+'

union

select '-'

union

select '*'

union

select '/'),

-- cte_base 任意2个数字，运算符构成的笛卡儿积，做为基本集合

cte_base as (

select c1.id,

case when o.opt = '+' then c1.value + c2.value

when o.opt = '-' then c1.value - c2.value

when o.opt = '*' then c1.value * c2.value

when o.opt = '/' then c1.value / c2.value

else null

end as value ,

case when o.opt = '+' then cast(c1.value::int as text) || ' + ' || cast(c2.value::int as text)

when o.opt = '-' then cast(c1.value::int as text) || ' - ' || cast(c2.value::int as text)

when o.opt = '*' then cast(c1.value::int as text) || ' * ' || cast(c2.value::int as text)

when o.opt = '/' then cast(c1.value::int as text) || ' / ' || cast(c2.value::int as text)

else null

end as opts,

c1.tag+c2.tag as tag

from cte_source c1

join cte_source c2 on c1.id = c2.id and c1.tag <> c2.tag

cross join cte_operators o),

-- 组合A: (A B)(C D)

cte_a as (

select c1.id,

case when o.opt = '+' then c1.value + c2.value

when o.opt = '-' then c1.value - c2.value

when o.opt = '*' then c1.value * c2.value

when o.opt = '/' and c2.value <>0 then c1.value / c2.value

else null

end as value ,

case when o.opt = '+' then ' ( ' || c1.opts || ' ) + ( ' || c2.opts || ' ) '

when o.opt = '-' then ' ( ' || c1.opts || ' ) - ( ' || c2.opts || ' ) '

when o.opt = '*' then ' ( ' || c1.opts || ' ) * ( ' || c2.opts || ' ) '

when o.opt = '/' and c2.value <>0 then ' ( ' || c1.opts || ' ) / ( ' || c2.opts || ' ) '

else null

end as opts,

c1.tag+c2.tag as tag

from cte_base c1

join cte_base c2 on c1.id = c2.id

cross join cte_operators o),

-- 组合B: ((A B)C)D

cte_b_1 as (

select c1.id,

case when o.opt = '+' then c1.value + c2.value

when o.opt = '-' then c1.value - c2.value

when o.opt = '*' then c1.value * c2.value

when o.opt = '/' and c2.value <>0 then c1.value / c2.value

else null

end as value ,

case when o.opt = '+' then ' ( ' || c1.opts || ' ) + ' || cast(c2.value::int as text)

when o.opt = '-' then ' ( ' || c1.opts || ' ) - ' || cast(c2.value::int as text)

when o.opt = '*' then ' ( ' || c1.opts || ' ) * ' || cast(c2.value::int as text)

when o.opt = '/' and c2.value <>0 then ' ( ' || c1.opts || ' ) / ' || cast(c2.value::int as text)

else null

end as opts,

c1.tag+c2.tag as tag

from cte_base c1

join cte_source c2 on c1.id = c2.id

cross join cte_operators o),

cte_b as (

select c1.id,

case when o.opt = '+' then c1.value + c2.value

when o.opt = '-' then c1.value - c2.value

when o.opt = '*' then c1.value * c2.value

when o.opt = '/' and c2.value <>0 then c1.value / c2.value

else null

end as value ,

case when o.opt = '+' then ' ( ' || c1.opts || ' ) + ' || cast(c2.value::int as text)

when o.opt = '-' then ' ( ' || c1.opts || ' ) - ' || cast(c2.value::int as text)

when o.opt = '*' then ' ( ' || c1.opts || ' ) * ' || cast(c2.value::int as text)

when o.opt = '/' and c2.value <>0 then ' ( ' || c1.opts || ' ) / ' || cast(c2.value::int as text)

else null

end as opts,

c1.tag+c2.tag as tag

from cte_b_1 c1

join cte_source c2 on c1.id = c2.id and c1.tag<1111

cross join cte_operators o),

-- 组合C: (A(B C))D

cte_c_1 as (

select c1.id,

case when o.opt = '+' then c2.value + c1.value

when o.opt = '-' then c2.value - c1.value

when o.opt = '*' then c2.value * c1.value

when o.opt = '/' and c1.value <>0 then c2.value / c1.value

else null

end as value ,

case when o.opt = '+' then cast(c2.value::int as text) || ' + ( ' || c1.opts || ' ) '

when o.opt = '-' then cast(c2.value::int as text) || ' - ( ' || c1.opts || ' ) '

when o.opt = '*' then cast(c2.value::int as text) || ' * ( ' || c1.opts || ' ) '

when o.opt = '/' and c1.value <>0 then cast(c2.value::int as text) || ' / ( ' || c1.opts || ' ) '

else null

end as opts,

c1.tag+c2.tag as tag

from cte_base c1

join cte_source c2 on c1.id = c2.id

cross join cte_operators o),

cte_c as (

select c1.id,

case when o.opt = '+' then c1.value + c2.value

when o.opt = '-' then c1.value - c2.value

when o.opt = '*' then c1.value * c2.value

when o.opt = '/' and c2.value <>0 then c1.value / c2.value

else null

end as value ,

case when o.opt = '+' then ' ( ' || c1.opts || ' ) + ' || cast(c2.value::int as text)

when o.opt = '-' then ' ( ' || c1.opts || ' ) - ' || cast(c2.value::int as text)

when o.opt = '*' then ' ( ' || c1.opts || ' ) * ' || cast(c2.value::int as text)

when o.opt = '/' and c2.value <>0 then ' ( ' || c1.opts || ' ) / ' || cast(c2.value::int as text)

else null

end as opts,

c1.tag+c2.tag as tag

from cte_c_1 c1

join cte_source c2 on c1.id = c2.id and c1.tag<1111

cross join cte_operators o),

-- 组合D: A((B C)D)

cte_d_1 as (

select c1.id,

case when o.opt = '+' then c1.value + c2.value

when o.opt = '-' then c1.value - c2.value

when o.opt = '*' then c1.value * c2.value

when o.opt = '/' and c2.value <>0 then c1.value / c2.value

else null

end as value ,

case when o.opt = '+' then ' ( ' || c1.opts || ' ) + ' || cast(c2.value::int as text)

when o.opt = '-' then ' ( ' || c1.opts || ' ) - ' || cast(c2.value::int as text)

when o.opt = '*' then ' ( ' || c1.opts || ' ) * ' || cast(c2.value::int as text)

when o.opt = '/' and c2.value <>0 then ' ( ' || c1.opts || ' ) / ' || cast(c2.value::int as text)

else null

end as opts,

c1.tag+c2.tag as tag

from cte_base c1

join cte_source c2 on c1.id = c2.id

cross join cte_operators o),

cte_d as (

select c1.id,

case when o.opt = '+' then c2.value + c1.value

when o.opt = '-' then c2.value - c1.value

when o.opt = '*' then c2.value * c1.value

when o.opt = '/' and c1.value <>0 then c2.value / c1.value

else null

end as value ,

case when o.opt = '+' then cast(c2.value::int as text) || ' + ( ' || c1.opts || ' ) '

when o.opt = '-' then cast(c2.value::int as text) || ' - ( ' || c1.opts || ' ) '

when o.opt = '*' then cast(c2.value::int as text) || ' * ( ' || c1.opts || ' ) '

when o.opt = '/' and c1.value <>0 then cast(c2.value::int as text) || ' / ( ' || c1.opts || ' ) '

else null

end as opts,

c1.tag+c2.tag as tag

from cte_d_1 c1

join cte_source c2 on c1.id = c2.id and c1.tag<1111

cross join cte_operators o),

-- 组合E: A(B(C D))

cte_e_1 as (

select c1.id,

case when o.opt = '+' then c2.value + c1.value

when o.opt = '-' then c2.value - c1.value

when o.opt = '*' then c2.value * c1.value

when o.opt = '/' and c1.value <>0 then c2.value / c1.value

else null

end as value ,

case when o.opt = '+' then cast(c2.value::int as text) || ' + ( ' || c1.opts || ' ) '

when o.opt = '-' then cast(c2.value::int as text) || ' - ( ' || c1.opts || ' ) '

when o.opt = '*' then cast(c2.value::int as text) || ' * ( ' || c1.opts || ' ) '

when o.opt = '/' and c1.value <>0 then cast(c2.value::int as text) || ' / ( ' || c1.opts || ' ) '

else null

end as opts,

c1.tag+c2.tag as tag

from cte_base c1

join cte_source c2 on c1.id = c2.id

cross join cte_operators o),

cte_e as (

select c1.id,

case when o.opt = '+' then c2.value + c1.value

when o.opt = '-' then c2.value - c1.value

when o.opt = '*' then c2.value * c1.value

when o.opt = '/' and c1.value <>0 then c2.value / c1.value

else null

end as value ,

case when o.opt = '+' then cast(c2.value::int as text) || ' + ( ' || c1.opts || ' ) '

when o.opt = '-' then cast(c2.value::int as text) || ' - ( ' || c1.opts || ' ) '

when o.opt = '*' then cast(c2.value::int as text) || ' * ( ' || c1.opts || ' ) '

when o.opt = '/' and c1.value <>0 then cast(c2.value::int as text) || ' / ( ' || c1.opts || ' ) '

else null

end as opts,

c1.tag+c2.tag as tag

from cte_e_1 c1

join cte_source c2 on c1.id = c2.id and c1.tag<1111

cross join cte_operators o),

cte_last as (

select distinct id,opts from cte_a

where round(value,10) =24 and tag = 1111

union

select distinct id,opts from cte_b

where round(value,10) =24 and tag = 1111

union

select distinct id,opts from cte_c

where round(value,10) =24 and tag = 1111

union

select distinct id,opts from cte_d

where round(value,10) =24 and tag = 1111

union

select distinct id,opts from cte_e

where round(value,10) =24 and tag = 1111 )

select id,opts

from cte_last

order by id;

以下是所得结果的截图：

如上是我的解题思路，感兴趣的读者可以自行测试。本文如有不足，错误，不妥之处，也恳请大家批评指出。