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代碼簡單得多，以后有空再整理一下。
?
?
?