有意思的SQL(四)SQL实现24点

今天一个平时不讨论技术的数据库技术群里有人50红包悬赏了一个技术问题，用SQL实现24点。群里有人用PG调python插件实现，也有人用oracle实现，我自己用MySQL做了一个实现。
24点规则还是很简单 给定4个数字，每个数字必须用一次且也只能用一次通过加减乘除加括号等运算符运算后能否得到24

我实现的代码比较长。在注释中顺带讲解一下
贴上代码

with cte1 as  --构造测试数据4个数 
(
 select 5 as num, 1 as id
 union all
 select 6 ,2
 union all
 select 7,3
 union all
 select 8,4
),
cte2 as   --  构造四个运算符
(select '*'  as op
 union all 
 select '/'
 union all
 select '+' 
 union all
 select '-'),
 cte3 as -- 与加减乘除产生笛卡尔积 
 (
select * from cte1,cte2),
cte4 as(   --然后再与排除自身后运算
select a.*,t.id as id2,t.num as num2 from cte3 a
LEFT JOIN LATERAL (select * from cte1 b where a.id<> b.id) as t on 1=1
)
,cte5 as -- 继续与加减乘除产生笛卡尔积
(
select a.*,b.op as op2 from cte4 a,cte2 b
)
,
cte6 as(  -- 继续排除已用数字后运算
select a.*,t.id as id3,t.num as num3 from cte5 a
LEFT JOIN LATERAL (select * from cte1 b where b.id <> a.id and b.id <> a.id2 ) as t on 1=1
)
,cte7 as -- 继续与加减乘除产生笛卡尔积
(
select a.*,b.op as op3 from cte6 a,cte2 b
)
,
cte8 as(  -- 继续排除已用数字后运算
select a.*,t.id as id4,t.num as num4 from cte7 a
LEFT JOIN LATERAL (select * from cte1 b where b.id <> a.id and b.id <> a.id2 and b.id <> a.id3 ) as t on 1=1
)  -- 穷举括号出现的可能性
select @s := concat(@s , concat(' union select ','((',num,op,num2,')',op2,num3,')',op3,num4,' as v,',quote(concat('((',num,op,num2,')',op2,num3,')',op3,num4)), ' as e' )) 
      ,@s := concat(@s , concat(' union select ','(',num,op,num2,')',op2,'(',num3,op3,num4,')',' as v,',quote(concat('(',num,op,num2,')',op2,'(',num3,op3,num4,')')), ' as e' )) 
      ,@s := concat(@s , concat(' union select ','(',num,op,'(',num2,op2,num3,'))',op3,num4,' as v,',quote(concat('(',num,op,'(',num2,op2,num3,'))',op3,num4)), ' as e' )) 
  	  ,@s := concat(@s , concat(' union select ',num,op,'((',num2,op2,num3,')',op3,num4,')',' as v,',quote(concat(num,op,'((',num2,op2,num3,')',op3,num4,')')), ' as e' )) 	  ,@s := concat(@s , concat(' union select ',num,op,'(',num2,op2,'(',num3,op3,num4,'))',' as v,',quote(concat(num,op,'(',num2,op2,num3,'(',op3,num4,'))')), ' as e' )) 
from cte8,(select @s :='') as t;
select @s := right(@s,length(@s)-6);
select @s := concat('select * from (',@s,') as t where round(v,10)=24');
--使用预编译执行
PREPARE stmt3 FROM @s;
EXECUTE stmt3;

上述执行在我的电脑上报如下错误

段落引用
Error Code: 1436. Thread stack overrun: 1028600 bytes used of a 1048576 byte stack, and 20000 bytes needed. Use ‘mysqld --thread_stack=#’ to specify a bigger stack.

改配置文件my.ini
增加 thread_stack=2M
重启服务后执行成功

此方法用SQL实现是较复杂，也难理解。
这种算法放应用端实现是更合适。

有人在群里吐槽说这种用SQL做没有什么意思
但我想说的是对于复杂SQL,或者一些高技巧的使用研发人员还是可以掌握的
会不会做与该不该在生产上应用是两码事。

你解决一件事情只有你会两种即以上方法才会有选择