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

近日，有幸参与了一次sql编程比赛，题目为用一条SQL给出扑克牌24点的计算表达式。

有一张表 cards，id 是自增字段的数字主键，另外有4个字段 c1,c2,c3,c4 ，每个字段随机从 1~10 之间选择一个整数 要求选手使用一条 SQL 给出 24 点的计算公式，返回的内容示如图所示，其中 result 字段是计算的表达式，只需返回1个解，如果没有解，result 返回null。

 

我的答案为：

with op as (
select '+' op from dual
union all
select '-' from dual
union all
select '*' from dual
union all
select '/' from dual),
t_one as(
select id,value,ltrim(rn,'C') rn from cards
unpivot
(value for rn in (c1 ,c2 ,c3 ,c4)) t1),
t_two as(
select id,greatest(rn1,rn2) r1,least(rn1,rn2) r2,value1 value1,min(exp) exp from (
select t1.id,'('||t1.value||op.op||t2.value||')' exp,
case when op.op='+' then t1.value+t2.value
     when op.op='-' then t1.value-t2.value
	 when op.op='*' then t1.value*t2.value
	 when op.op='/' then t1.value/t2.value
	 else null end value1,t1.rn rn1,t2.rn rn2
from
t_one t1,t_one t2,op
where t1.id=t2.id
  and t1.rn<>t2.rn) where value1>0 group by id,greatest(rn1,rn2),least(rn1,rn2),value1),
t_two_two as (
select id,min(exp1) exp from (
select t1.id,t1.r1,t1.r2,t2.r1,t2.r2,t1.exp||op.op||t2.exp exp1,
case when op.op='+' then t1.value1+t2.value1
     when op.op='-' then t1.value1-t2.value1
	 when op.op='*' then t1.value1*t2.value1
	 when op.op='/' then t1.value1/t2.value1
	 else null end value1
from   t_two t1,t_two t2,op
where t1.id=t2.id and (t1.r1<>t2.r1 and t1.r2<>t2.r2 and t1.r2<>t2.r1 and t1.r1<>t2.r2) and t1.r1+t1.r2+t2.r2+t2.r1=10 and t1.value1>=t2.value1)
where value1=24 group by id),
t_one_1 as (select * from t_one where id not in (select id from t_two_two)),
t_two_1 as(select * from t_two where id not in (select id from t_two_two)),
t_three as (
select t1.id,t1.r1,t1.r2,t2.rn r3,'('||t1.exp||op.op||t2.value||')' exp1,'('||t2.value||op.op||t1.exp||')' exp2,
case when op.op='+' then t1.value1+t2.value
     when op.op='-' then t1.value1-t2.value
	 when op.op='*' then t1.value1*t2.value
	 when op.op='/' then t1.value1/t2.value
	 else null end value1,
case when op.op='+' then t2.value+t1.value1
     when op.op='-' then t2.value-t1.value1
	 when op.op='*' then t2.value*t1.value1
	 when op.op='/' then t2.value/t1.value1
	 else null end value2
from   t_two_1 t1,t_one_1 t2,op
where t1.id=t2.id  and t1.r1<>t2.rn and t1.r2<>t2.rn),
t_three_one as(
select  id,min(case when trunc(value1,36)=24 then exp1
	                 when trunc(value4,36)=24 then exp4 else null end) exp from (
select t1.id,t1.r1,t1.r2,t1.r3,t2.rn r4,
       t1.exp1||op.op||t2.value exp1,
	   t1.exp2||op.op||t2.value exp2,
	   t2.value||op.op||t1.exp1 exp3,
	   t2.value||op.op||t1.exp2 exp4,
case when op.op='+' then t1.value1+t2.value
     when op.op='-' then t1.value1-t2.value
	 when op.op='*' then t1.value1*t2.value
	 when op.op='/' then t1.value1/t2.value
	 else null end value1,
case when op.op='+' then t1.value2+t2.value
     when op.op='-' then t1.value2-t2.value
	 when op.op='*' then t1.value2*t2.value
	 when op.op='/' then t1.value2/t2.value
	 else null end value2,
case when op.op='+' then t2.value+t1.value1
     when op.op='-' then t2.value-t1.value1
	 when op.op='*' then t2.value*t1.value1
	 when op.op='/' and t1.value1>0 then t2.value/t1.value1
	 else null end value3,
case when op.op='+' then t2.value+t1.value2
     when op.op='-' then t2.value-t1.value2
	 when op.op='*' then t2.value*t1.value2
	 when op.op='/' and t1.value2>0 then t2.value/t1.value2
	 else null end value4
from   t_three t1,t_one_1 t2,op
where t1.id=t2.id  and t1.r1<>t2.rn and t1.r2<>t2.rn and t1.r3<>t2.rn and r1+r2+r3+t2.rn=10)
where trunc(value1,36)=24 or trunc(value4,36)=24 group by id)
select /*+monitor sql_24*/ t.*,t1.exp result
  from cards t,(select * from t_three_one
                union all
                select * from t_two_two) t1
  where t.id=t1.id(+) order by t.id;

能实现功能，但性能一般。重在参与。