数据库编程大赛：一条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下来试试！！
