SQL解决彩票问题
前段时间和同事研究了一个小问题，关于彩票的中奖判断逻辑。本身是一个Java测试题，不过发现用SQL写要更加简单一点，就写了一个练练手。题目是这样的：
在数据库中存放各个客户ID，及所选号码，每个用户的号码可能会不止一组。中奖规则如下：每个位置可选数字0-9，共7位。连续两位数字及位置均相同时为5等奖，连续3位数字及位置相同为4等奖，……，全部数字及位置相同为特等奖。最后返回所有中奖者名单及中奖数量。
主要是因为位置是固定的，所以比较方便，用decode将每一位拆分出来与答案比较即可得出结论：
create table t9 (id varchar2(6),num varchar2(7));
insert into t9 values('10001','7483627');
insert into t9 values('10002','9623584');
insert into t9 values('10004','1234569');
insert into t9 values('10002','3298648');
insert into t9 values('10002','5629874');
insert into t9 values('10003','0245896');
insert into t9 values('10001','1259567');
commit;
select id,k||'*'||sum(decode(k,'0',0,1)) x from
(select id,num,rn,
(case when rn like '%1111111%' then '特等奖'
when rn like '%111111%' then '一等奖'
when rn like '%11111%' then '二等奖'
when rn like '%1111%' then '三等奖'
when rn like '%111%' then '四等奖'
when rn like '%11%' then '五等奖'
else '0'
end) k from
(select id,num,a||b||c||d||e||f||g rn from
(select id,num,
decode(a.a,w.a,1,0) a,
decode(a.b,w.b,1,0) b,
decode(a.c,w.c,1,0) c,
decode(a.d,w.d,1,0) d,
decode(a.e,w.e,1,0) e,
decode(a.f,w.f,1,0) f,
decode(a.g,w.g,1,0) g from
(select id,num,substr(num,1,1) a,substr(num,2,1) b,substr(num,3,1) c,
substr(num,4,1) d,substr(num,5,1) e,substr(num,6,1) f,substr(num,7,1) g
from t9) a,
(select &1 a,&2 b,&3 c,&4 d,&5 e,&6 f,&7 g from dual) w--输入每期中将号码
)
)
)
group by id,k
having sum(decode(k,'0',0,1))<>0--不显示未中奖者名单
;
另外涉及到这个题目的一些衍生问题，如每个ID只生成一条记录，所有号用“,”分开，如何转换为T9中的格式：
由于每一组号码的长度是固定的(7位)，所以可以根据字段长度来半段出某个ID购买了几注——(length(num)+1)/8
这样可以使用一下代码将每一注号码区分出来：
create table t8 (id varchar2(6),num varchar2(999));
insert into t8 values('10001','7483627,5693268,1259026,0326597,1265975,1236498,0031699,1258962,1359871,1326987');
insert into t8 values('10002','9623584,1236895,2156298,2356897,2369654,1259864,2145236');
insert into t8 values('10003','1234569,1236598,1254785,1254696,1268954');
insert into t8 values('10004','3298648');
commit;
select distinct a.id,substr(a.num,instr(a.num,',',1,b.rn)+1,7) num from
(select id,num from t8) a,
(select rownum rn from dual connect by rownum<=100) b
order by 1,2
通过instr函数找到“,”的位置，然后通过substr找到号码。如果号码的位数不确定，也可以通过
instr(num,',',1,rn+1)-instr(num,',',1,rn)
来计算位数，从而得到结果。
这里直接使用rownum<=100，限制了每个ID的数量，而且直接使用了hash join，会造成系统效率比较低，暂时没有考虑优化的问题。
关于一般的彩票中存在的数字不重复，且无需按位数排列，或者数字可选两位数等等，写PLSQL要比直接写SQL代码简单得多，以后有空再整理一下。