近日，有幸参与了一次sql编程比赛，题目为用一条SQL给出扑克牌24点的计算表达式。
有一张表 cards，id 是自增字段的数字主键，另外有4个字段 c1,c2,c3,c4 ，每个字段随机从 1~10 之间选择一个整数 要求选手使用一条 SQL 给出 24 点的计算公式，返回的内容示如图所示，其中 result 字段是计算的表达式，只需返回1个解，如果没有解，result 返回null。
我的答案为：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 |
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; |
能实现功能，但性能一般。重在参与。