数据库编程大赛：一条SQL计算扑克牌24点

前段时间参NineData举办的数据库编程大赛–只能用一条SQL计算扑克24点算法
具体细节可参见这里

这个以前我也写过。但是用动态SQL实现的，这次提升了难度
1、必须只能用一条SQL实现
2、不能使用自定义函数
3、测试数据有10000条记录

我用MySQL做了尝试
具体代码如下：

--  构建测试数据10000条
CREATE TABLE if not exists cards1 (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c1 int NOT NULL,
  c2 int NOT NULL,
  c3 int NOT NULL,
  c4 int NOT NULL
);

insert into cards1(c1,c2,c3,c4)
WITH RECURSIVE cte (n) AS
(
  SELECT 1 id
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 10
)
select t1.n c1,t2.n c2,t3.n c3,t4.n c4 from cte t1,cte t2,cte t3,cte t4;

--  数据构建完毕
/*
truncate table cards1;
insert into cards1
values(1,1,1,5,5);
*/


with cte0 as  
(
 select a.id,a.c1,a.c2,a.c3,a.c4,t.str from cards1 a
 inner join LATERAL  
 (select group_concat(c order by c) str 
  from ( select b1.id, c1 as c from cards1 b1 where b1.id = a.id union all select b2.id, c2 as c from cards1 b2 where b2.id = a.id  union all select b3.id, c3 as c from cards1 b3 where b3.id = a.id union all select b4.id, c4 as c from cards1 b4 where b4.id = a.id  ) as t1  ) as t on 1=1
  group by id 
),
cte00 as  -- 去重 比如1,5,5,1  与 1,5,1,5 保存一个用于计算就可以了
(
select a.*,b.str from cards1 a 
inner join
(select max(id) as id,str from cte0 group by str) b on a.id = b.id
)
, cte1 as  -- 行转列 
(
select id*100 +1 as id,str,cast(c1 as DECIMAL(24,10)) as num from cte00 union all
select id*100 +2 as id,str,cast(c2 as DECIMAL(24,10)) as num from cte00 union all
select id*100 +3 as id,str,cast(c3 as DECIMAL(24,10)) as num from cte00 union all      
select id*100 +4 as id,str,cast(c4 as DECIMAL(24,10)) as num from cte00
)
,
cte2 as   --  构造四个运算符
(select '*'  as op
 union all 
 select '/'
 union all
 select '+' 
 union all
 select '-')
 ,
 cte3 as -- 与加减乘除产生笛卡尔积 
 (
select * from (select max(id) as id,max(str) as str,num from cte1 group by round(id,-2) ,num ) t,cte2
)

,cte4 as(   -- 然后再与排除自身后运算
select a.*,t.id as id2,t.num as num2 from cte3 a
inner join cte1 t on  round(a.id,-2) = round(t.id,-2) and  a.id<> t.id
)
,cte5 as -- 继续与加减乘除产生笛卡尔积
(
select a.*,b.op as op2 from (select max(id) as id,max(str) as str,max(id2) as id2,num,op,num2 from cte4 group by round(id,-2),num,op,num2) a,cte2 b
)
,
cte6 as(  -- 继续排除已用数字后运算
select a.*,t.id as id3,t.num as num3 from cte5 a
inner join cte1 t on  round(a.id,-2) = round(t.id,-2) and t.id <> a.id and t.id <> a.id2
)
,cte7 as -- 继续与加减乘除产生笛卡尔积
(
select a.*,b.op as op3 from (select max(id) as id,max(str) as str,max(id2) as id2,max(id3) as id3,num,op,num2,op2,num3 from cte6 group by round(id,-2),num,op,num2,op2,num3) a,cte2 b
)
,
cte8 as(  -- 继续排除已用数字后运算
select a.*,t.id as id4,t.num as num4  from cte7 a
inner join cte1 t on  round(a.id,-2) = round(t.id,-2) and t.id <> a.id and t.id <> a.id2 and t.id <> a.id3
) 
,cte9 as
(
select cte8.id,cte8.str,
-- 按计算顺序枚举一下
concat('((',num,op,num2,')',op2,num3,')',op3,num4) as exp1,
 @a1 := case when op = '*' then num * num2 
			when op = '/' then num / num2
            when op = '+' then num + num2
            when op = '-' then num - num2  else 0 end as a1,
            
 @a2 := case when op2 = '*' then  @a1 * num3 
			when op2 = '/' then  @a1 / num3
            when op2 = '+' then  @a1 + num3
            when op2 = '-' then @a1 - num3  else 0 end as a2,
            
 @a3 := round(case when op3 = '*' then @a2 * num4 
			when op3 = '/' then @a2 / num4
            when op3 = '+' then @a2 + num4
            when op3 = '-' then @a2 - num4  else 0 end,10) as a3,
            
concat('(',num,op,num2,')',op2,'(',num3,op3,num4,')') as exp2,          
 @b1 := case when op = '*' then num * num2 
			when op = '/' then num / num2
            when op = '+' then num + num2
            when op = '-' then num - num2  else 0 end as b1,
 @b2 := case when op3 = '*' then num3 * num4 
			when op3 = '/' then num3 / num4
            when op3 = '+' then num3 + num4
            when op3 = '-' then num3 - num4  else 0 end as b2,
            
 @b3 := round(case when op2 = '*' then  @b1 * @b2
			when op2 = '/' then  @b1 / @b2
            when op2 = '+' then  @b1 + @b2
            when op2 = '-' then  @b1 - @b2  else 0 end,10) as b3,
  concat('(',num,op,'(',num2,op2,num3,'))',op3,num4) as exp3,          
            
@c1 := case when op2 = '*' then num2 * num3 
			when op2 = '/' then num2 / num3
            when op2 = '+' then num2 + num3
            when op2 = '-' then num2 - num3  else 0 end as c1,
@c2 := case when op = '*' then num * @c1 
			when op = '/' then num / @c1
            when op = '+' then num + @c1
            when op = '-' then num - @c1  else 0 end as c2,
            
@c3 := round(case when op3 = '*' then  @c2 * num4
			when op3 = '/' then  @c2 / num4
            when op3 = '+' then @c2 + num4
            when op3 = '-' then  @c2 - num4  else 0 end,10) as c3,        
  concat(num,op,'((',num2,op2,num3,')',op3,num4,')') as exp4,
  
@d1 := case when op2 = '*' then num2 * num3 
			when op2 = '/' then num2 / num3
            when op2 = '+' then num2 + num3
            when op2 = '-' then num2 - num3  else 0 end as d1,
@d2 := case when op3 = '*' then @d1 * num4
			when op3 = '/' then @d1 / num4
            when op3 = '+' then @d1 + num4
            when op3 = '-' then @d1 - num4  else 0 end as d2,
            
@d3 := round(case when op = '*' then num * @d2
			when op = '/' then  num / @d2
            when op = '+' then num + @d2
            when op = '-' then  num - @d2  else 0 end,10) as d3,  
  concat(num,op,'(',num2,op2,'(',num3,op3,num4,'))') as exp5,    
  @e1 := case when op3 = '*' then num3 * num4 
			when op3 = '/' then num3 / num4
            when op3 = '+' then num3 + num4
            when op3 = '-' then num3 - num4  else 0 end as e1,
@e2 := case when op2 = '*' then  num2 *  @e1
			when op2 = '/' then num2 /  @e1
            when op2 = '+' then num2 +  @e1
            when op2 = '-' then num2 -  @e1  else 0 end as e2,
            
@e3 := round(case when op = '*' then num * @e2
			when op = '/' then  num / @e2
            when op = '+' then num + @e2
            when op = '-' then  num - @e2  else 0 end,10) as e3
 from cte8
 )
 ,cte10 as
 (
 select *  ,row_number() over(partition by round(id,-2) order by id) as rowid
 from cte9
 where round(a3,10) = 24 or  round(b3,10) = 24 or  round(c3,10) = 24  or  round(d3,10) = 24  or  round(e3,10) = 24 
)

select  b.*,
case when round(a.a3,10) = 24  then exp1
	 when round(a.b3,10) = 24  then exp2
     when round(a.c3,10) = 24  then exp3
     when round(a.d3,10) = 24  then exp4
     when round(a.e3,10) = 24  then exp5
     end as expr
from  cte0 b 
left join cte10 a on a.str = b.str and a.rowid = 1
order by b.id ;
复制

上述是我改进后的语句，我第一版是未去重，10000条记录直接查不出来
这一版在两个方向进行了去重
1、10000个数字中，有4个数据是重复的。 比如 1,1,5,5 与 1,5,1,5、5，5，1，1等由两个1,两个5组成的四个数字，解的答案是一致的。所以这里取一条即可了
2、由于我的算法是列转行了，所以与运算符做迪卡尔积的时候，也会有重复产生。这里也做了一次去重。
经过两次去重，整个SQL能成功运行结果。在我的本机上耗时50S左右
上面代码可直接使用。你也可以COPY下来试试！！