使用 SQL 語句實現一個年會抽獎程序的代碼
年關將近,抽獎想必是大傢在公司年會上最期待的活動瞭。如果老板讓你做一個年會抽獎的程序,你會怎麼實現呢?今天給大傢介紹一下如何通過 SQL 語句來實現這個功能。實現的原理其實非常簡單,就是通過函數為每個人分配一個隨機數,然後取最大或者最小的 N 個隨機數對應的員工。
📝本文使用的示例表可以點此下載。
Oracle
Oracle 提供瞭一個系統程序包DBMS_RANDOM,可以用於生成隨機數據,包括隨機數字和隨機字符串等。其中,DBMS_RANDOM.VALUE 函數可以用於生成一個大於等於 0 小於 1 的隨機數字。利用這個函數,我們可以從表中返回隨機的數據行。例如:
SELECT emp_id, emp_name FROM employee ORDER BY dbms_random.value FETCH FIRST 1 ROWS ONLY; EMP_ID|EMP_NAME| ------|--------| 3|張飛 |
再次執行以上查詢將會返回其他員工。我們也可以一次返回多名隨機員工:
SELECT emp_id, emp_name FROM employee ORDER BY dbms_random.value FETCH FIRST 3 ROWS ONLY; EMP_ID|EMP_NAME| ------|--------| 6|魏延 | 21|黃權 | 9|趙雲 |
為瞭避免同一個員工中獎多次,可以創建一個存儲已中獎員工的表:
每次開獎時
-- 中獎員工表 CREATE TABLE emp_win( emp_id integer PRIMARY KEY, -- 員工編號 emp_name varchar(50) NOT NULL, -- 員工姓名 grade varchar(50) NOT NULL -- 中獎級別 );
將中獎員工和級別存入 emp_win 表中,同時每次開獎時排除已經中獎的員工。例如,以下語句可以抽出 3 名三等獎:
INSERT INTO emp_win SELECT emp_id, emp_name, '三等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經中獎的員工 ORDER BY dbms_random.value FETCH FIRST 3 ROWS ONLY; SELECT * FROM emp_win; EMP_ID|EMP_NAME|GRADE | ------|--------|--------| 8|孫丫鬟 |三等獎 | 3|張飛 |三等獎 | 9|趙雲 |三等獎 |
繼續抽出 2 名二等獎和 1 名一等獎:
-- 二等獎2名 INSERT INTO emp_win SELECT emp_id, emp_name, '二等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY dbms_random.value FETCH FIRST 2 ROWS ONLY; -- 一等獎1名 INSERT INTO emp_win SELECT emp_id, emp_name, '一等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY dbms_random.value FETCH FIRST 1 ROWS ONLY; SELECT * FROM emp_win; EMP_ID|EMP_NAME|GRADE | ------|--------|-------| 8|孫丫鬟 |三等獎 | 3|張飛 |三等獎 | 9|趙雲 |三等獎 | 6|魏延 |二等獎 | 22|糜竺 |二等獎 | 10|廖化 |一等獎 |
我們可以進一步將以上語句封裝成一個存儲過程:
CREATE OR REPLACE PROCEDURE luck_draw(pv_grade varchar, pn_num integer) IS BEGIN INSERT INTO emp_win SELECT emp_id, emp_name, pv_grade FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY dbms_random.value FETCH FIRST pn_num ROWS ONLY; COMMIT; END luck_draw; / CALL luck_draw('特等獎', 1); SELECT * FROM emp_win WHERE grade = '特等獎'; EMP_ID|EMP_NAME|GRADE | ------|--------|-------| 25|孫乾 |特等獎 |
關於 Oracle 中如何生成隨機數字、字符串、日期、驗證碼以及 UUID,可以參考這篇文章。
MySQL
MySQL 提供瞭一個系統函數RAND,可以用於生成一個大於等於 0 小於 1 的隨機數字。利用這個函數,我們可以從表中返回隨機記錄。例如:
SELECT emp_id, emp_name FROM employee ORDER BY RAND() LIMIT 1; emp_id|emp_name| ------|--------| 19|龐統 |
再次執行以上語句將會返回其他員工。我們也可以一次返回多名隨機的員工:
SELECT emp_id, emp_name FROM employee ORDER BY RAND() LIMIT 3; emp_id|emp_name| ------|--------| 1|劉備 | 20|蔣琬 | 23|鄧芝 |
為瞭避免同一個員工中獎多次,我們可以創建一個存儲已中獎員工的表:
-- 中獎員工表 CREATE TABLE emp_win( emp_id integer PRIMARY KEY, -- 員工編號 emp_name varchar(50) NOT NULL, -- 員工姓名 grade varchar(50) NOT NULL -- 中獎級別 );
每次開獎時將中獎員工和級別存入 emp_win 表中,同時每次開獎時排除已經中獎的員工。例如,以下語句可以抽出 3 名三等獎:
INSERT INTO emp_win SELECT emp_id, emp_name, '三等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經中獎的員工 ORDER BY RAND() LIMIT 3; SELECT * FROM emp_win; emp_id|emp_name|grade | ------|--------|-------| 18|法正 |三等獎 | 23|鄧芝 |三等獎 | 24|簡雍 |三等獎 |
我們繼續抽出 2 名二等獎和 1 名一等獎:
-- 二等獎2名 INSERT INTO emp_win SELECT emp_id, emp_name, '二等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經中獎的員工 ORDER BY RAND() LIMIT 2; -- 一等獎1名 INSERT INTO emp_win SELECT emp_id, emp_name, '一等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經中獎的員工 ORDER BY RAND() LIMIT 1; SELECT * FROM emp_win; emp_id|emp_name|grade | ------|--------|-------| 2|關羽 |二等獎 | 18|法正 |三等獎 | 20|蔣琬 |一等獎 | 23|鄧芝 |三等獎 | 24|簡雍 |三等獎 | 25|孫乾 |二等獎 |
我們可以進一步將以上語句封裝成一個存儲過程:
DELIMITER $$ CREATE PROCEDURE luck_draw(IN pv_grade varchar(50), IN pn_num integer) BEGIN INSERT INTO emp_win SELECT emp_id, emp_name, pv_grade FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY RAND() LIMIT pn_num; SELECT * FROM emp_win; END$$ DELIMITER ; CALL luck_draw('特等獎', 1); emp_id|emp_name|grade | ------|--------|-------| 2|關羽 |二等獎 | 8|孫丫鬟 |特等獎 | 18|法正 |三等獎 | 20|蔣琬 |一等獎 | 23|鄧芝 |三等獎 | 24|簡雍 |三等獎 | 25|孫乾 |二等獎 |
關於 MySQL 中如何生成隨機數字、字符串、日期、驗證碼以及 UUID,可以參考這篇文章。
Microsoft SQL Server
Microsoft SQL Server 提供瞭一個系統函數NEWID,可以用於生成一個隨機的 GUID。利用這個函數,我們可以從表中返回隨機的數據行。例如:
SELECT TOP(1) emp_id, emp_name FROM employee ORDER BY NEWID(); emp_id|emp_name| ------|--------| 25|孫乾 |
再次執行以上語句將會返回其他員工。我們也可以一次返回多名隨機員工:
SELECT TOP(3) emp_id, emp_name FROM employee ORDER BY NEWID(); emp_id|emp_name| ------|--------| 23|鄧芝 | 1|劉備 | 21|黃權 |
雖然 Microsoft SQL Server 提供瞭一個返回隨機數字的 RAND 函數,但是該函數對於所有的數據行都返回相同的結果,因此不能用於返回表中的隨機記錄。例如:
SELECT TOP(3) emp_id, emp_name, RAND() AS rd FROM employee ORDER BY RAND(); emp_id|emp_name|rd | ------|--------|------------------| 23|鄧芝 |0.8623555267583647| 18|法正 |0.8623555267583647| 11|關平 |0.8623555267583647|
為瞭避免同一個員工中獎多次,我們可以創建一個存儲已中獎員工的表:
-- 中獎員工表 CREATE TABLE emp_win( emp_id integer PRIMARY KEY, -- 員工編號 emp_name varchar(50) NOT NULL, -- 員工姓名 grade varchar(50) NOT NULL -- 中獎級別 );
我們在每次開獎時將中獎員工和級別存入 emp_win 表中,同時每次開獎時排除已經中獎的員工。例如,以下語句可以抽出 3 名三等獎:
INSERT INTO emp_win SELECT TOP(3) emp_id, emp_name, '三等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經中獎的員工 ORDER BY NEWID(); SELECT * FROM emp_win; emp_id|emp_name|grade| ------|--------|-----| 14|張苞 |三等獎| 17|馬岱 |三等獎| 21|黃權 |三等獎|
繼續抽出 2 名二等獎和 1 名一等獎:
-- 二等獎2名 INSERT INTO emp_win SELECT TOP(2) emp_id, emp_name, '二等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY NEWID(); -- 一等獎1名 INSERT INTO emp_win SELECT TOP(1) emp_id, emp_name, '一等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY NEWID(); SELECT * FROM emp_win; emp_id|emp_name|grade| ------|--------|-----| 14|張苞 |三等獎| 15|趙統 |一等獎| 17|馬岱 |三等獎| 18|法正 |二等獎| 21|黃權 |三等獎| 22|糜竺 |二等獎|
我們可以進一步將以上語句封裝成一個存儲過程:
CREATE OR ALTER PROCEDURE luck_draw(@pv_grade VARCHAR(50), @pn_num integer) AS BEGIN INSERT INTO emp_win SELECT TOP(@pn_num) emp_id, emp_name, @pv_grade FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY NEWID() SELECT * FROM emp_win END; EXEC luck_draw '特等獎', 1; emp_id|emp_name|grade| ------|--------|-----| 14|張苞 |三等獎| 15|趙統 |一等獎| 17|馬岱 |三等獎| 18|法正 |二等獎| 21|黃權 |三等獎| 22|糜竺 |二等獎| 23|鄧芝 |特等獎|
關於 Microsoft SQL Server 中如何生成隨機數字、字符串、日期、驗證碼以及 UUID,可以參考這篇文章。
PostgreSQL
PostgreSQL 提供瞭一個系統函數 RANDOM,可以用於生成一個大於等於 0 小於 1 的隨機數字。利用這個函數,我們可以從表中返回隨機記錄。例如:
SELECT emp_id, emp_name FROM employee ORDER BY RANDOM() LIMIT 1; emp_id|emp_name| ------|--------| 22|糜竺 |
再次執行以上語句將會返回其他員工。我們也可以一次返回多名隨機的員工:
SELECT emp_id, emp_name FROM employee ORDER BY RAND() LIMIT 3; emp_id|emp_name| ------|--------| 8|孫丫鬟 | 4|諸葛亮 | 9|趙雲 |
為瞭避免同一個員工中獎多次,我們可以創建一個存儲已中獎員工的表:
-- 中獎員工表 CREATE TABLE emp_win( emp_id integer PRIMARY KEY, -- 員工編號 emp_name varchar(50) NOT NULL, -- 員工姓名 grade varchar(50) NOT NULL -- 中獎級別 );
每次開獎時將中獎員工和級別存入 emp_win 表中,同時每次開獎時排除已經中獎的員工。例如,以下語句可以抽出 3 名三等獎:
INSERT INTO emp_win SELECT emp_id, emp_name, '三等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經中獎的員工 ORDER BY RANDOM() LIMIT 3; SELECT * FROM emp_win; emp_id|emp_name|grade| ------|--------|-----| 23|鄧芝 |三等獎| 15|趙統 |三等獎| 24|簡雍 |三等獎|
我們繼續抽出 2 名二等獎和 1 名一等獎:
-- 二等獎2名 INSERT INTO emp_win SELECT emp_id, emp_name, '二等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY RANDOM() LIMIT 2; -- 一等獎1名 INSERT INTO emp_win SELECT emp_id, emp_name, '一等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY RANDOM() LIMIT 1; SELECT * FROM emp_win; emp_id|emp_name|grade| ------|--------|-----| 23|鄧芝 |三等獎| 15|趙統 |三等獎| 24|簡雍 |三等獎| 1|劉備 |二等獎| 21|黃權 |二等獎| 22|糜竺 |一等獎|
我們可以進一步將以上語句封裝成一個存儲過程:
CREATE OR REPLACE PROCEDURE luck_draw(pv_grade IN VARCHAR, pn_num IN INTEGER) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO emp_win SELECT emp_id, emp_name, pv_grade FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY RANDOM() LIMIT pn_num; END; $$ CALL luck_draw('特等獎', 1); SELECT * FROM emp_win WHERE grade = '特等獎'; emp_id|emp_name|grade| ------|--------|-----| 5|黃忠 |特等獎|
關於 PostgreSQL 中如何生成隨機數字、字符串、日期、驗證碼以及 UUID,可以參考這篇文章。
SQLite
SQLite 中的RANDOM 函數可以用於生成一個大於等於 -9223372036854775808 小於 9223372036854775807 的隨機整數。利用這個函數,我們可以從表中返回隨機的數據行。例如:
SELECT emp_id, emp_name FROM employee ORDER BY RANDOM() LIMIT 1; emp_id|emp_name| ------|--------| 4|諸葛亮 |
再次執行以上語句將會返回其他員工。我們也可以一次返回多名隨機員工:
SELECT emp_id, emp_name FROM employee ORDER BY RANDOM() LIMIT 3; emp_id|emp_name| ------|--------| 16|周倉 | 15|趙統 | 11|關平 |
為瞭避免同一個員工中獎多次,我們可以創建一個存儲已中獎員工的表:
-- 中獎員工表 CREATE TABLE emp_win( emp_id integer PRIMARY KEY, -- 員工編號 emp_name varchar(50) NOT NULL, -- 員工姓名 grade varchar(50) NOT NULL -- 中獎級別 );
我們在每次開獎時將中獎員工和級別存入 emp_win 表中,同時每次開獎時排除已經中獎的員工。例如,以下語句可以抽出 3 名三等獎:
INSERT INTO emp_win SELECT emp_id, emp_name, '三等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經中獎的員工 ORDER BY RANDOM() LIMIT 3; SELECT * FROM emp_win; emp_id|emp_name|grade| ------|--------|-----| 2|關羽 |三等獎| 3|張飛 |三等獎| 8|孫丫鬟 |三等獎|
繼續抽出 2 名二等獎和 1 名一等獎:
-- 二等獎2名 INSERT INTO emp_win SELECT emp_id, emp_name, '二等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY RANDOM() LIMIT 2; -- 一等獎1名 INSERT INTO emp_win SELECT emp_id, emp_name, '一等獎' FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) ORDER BY RANDOM() LIMIT 1; SELECT * FROM emp_win; emp_id|emp_name|grade| ------|--------|-----| 2|關羽 |三等獎| 3|張飛 |三等獎| 4|諸葛亮 |一等獎| 8|孫丫鬟 |三等獎| 16|周倉 |二等獎| 23|鄧芝 |二等獎|
關於 SQLite 中如何生成隨機數字、字符串、日期、驗證碼以及 UUID,可以參考這篇文章。
總結
我們通過數據庫系統提供的隨機數函數返回表中的隨機記錄,從而實現年會抽獎的功能。
到此這篇關於使用 SQL 語句實現一個年會抽獎程序的文章就介紹到這瞭,更多相關sql年會抽獎程序內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- Python全棧之學習MySQL(2)
- mysql查詢優化之100萬條數據的一張表優化方案
- 淺談MySQL 億級數據分頁的優化
- MySQL 生成隨機數字、字符串、日期、驗證碼及 UUID的方法
- MySQL操作並使用Python進行連接