0

## SQL 小程式 "樂透" 比對問題

### 2 個回答

2
shortie
iT邦新手 5 級 ‧ 2011-01-15 16:52:19

``````&lt;pre class="c" name="code">
DROP TABLE lottery;
DROP TABLE drawn_numbers;
DROP TABLE ticket;

CREATE TABLE lottery (numbers INTEGER);
CREATE TABLE ticket (numbers INTEGER);
CREATE TABLE drawn_numbers (numbers INTEGER);

-- 投入號碼球

INSERT INTO lottery
SELECT ROWNUM
FROM all_objects
WHERE ROWNUM &lt;= 39;
COMMIT;

-- 自選號

INSERT INTO ticket VALUES (2);
--INSERT INTO ticket VALUES (3);
--INSERT INTO ticket VALUES (5);
INSERT INTO ticket VALUES (7);
--INSERT INTO ticket VALUES (11);
INSERT INTO ticket VALUES (13);
--INSERT INTO ticket VALUES (17);
--INSERT INTO ticket VALUES (19);
INSERT INTO ticket VALUES (23);
--INSERT INTO ticket VALUES (29);
--INSERT INTO ticket VALUES (31);
INSERT INTO ticket VALUES (37);
COMMIT;

-- 開彩
-- 電腦隨機選號

INSERT INTO drawn_numbers
SELECT x.numbers
FROM (  SELECT numbers
FROM lottery
ORDER BY DBMS_RANDOM.VALUE) x
WHERE ROWNUM &lt;= 5;
COMMIT;

-- 派彩

SELECT CASE x.criteria
WHEN 5 THEN '特獎'
WHEN 4 THEN '一獎'
WHEN 3 THEN '二獎'
WHEN 2 THEN '三獎'
ELSE '沒獎'
END
AS prize
FROM (SELECT COUNT (*) AS criteria
FROM drawn_numbers dn INNER JOIN ticket t ON dn.numbers = t.numbers) x;
``````
2
chk
iT邦新手 3 級 ‧ 2011-01-13 14:28:32

ouyuntisi iT邦新手 5 級 ‧ 2011-01-13 16:09:14 檢舉