使用 SQL 语句实现一个年会抽奖程序
作者 | 董旭陽??責編 | 張文
頭圖 | CSDN?下載自視覺中國
出品 | CSDN（ID：CSDNnews）
年關將近，抽獎想必是大家在公司年會上最期待的活動了。如果老板讓你做一個年會抽獎的程序，你會怎么實現呢？
今天給大家介紹一下如何通過 SQL 語句來實現這個功能。實現的原理其實非常簡單，就是通過函數為每個人分配一個隨機數，然后取最大或者最小的 N 個隨機數對應的員工。
Oracle
Oracle 提供了一個系統程序包?DBMS_RANDOM，可以用于生成隨機數據，包括隨機數字和隨機字符串等。其中，DBMS_RANDOM.VALUE 函數可以用于生成一個大于等于 0 小于 1 的隨機數字。利用這個函數，我們可以從表中返回隨機的數據行。例如：
SELECT emp_id, emp_nameFROM employee ORDER BY dbms_random.valueFETCH FIRST 1 ROWS ONLY; EMP_ID|EMP_NAME|------|--------| 3|張飛 |?
再次執行以上查詢將會返回其他員工。我們也可以一次返回多名隨機員工：
SELECT emp_id, emp_nameFROM employee ORDER BY dbms_random.valueFETCH 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_winSELECT emp_id, emp_name, '三等獎'FROM employeeWHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經中獎的員工ORDER BY dbms_random.valueFETCH FIRST 3 ROWS ONLY; SELECT * FROM emp_win; EMP_ID|EMP_NAME|GRADE |------|--------|--------| 8|孫丫鬟 |三等獎 | 3|張飛 |三等獎 | 9|趙云 |三等獎 |繼續抽出 2 名二等獎和 1 名一等獎：
-- 二等獎2名INSERT INTO emp_winSELECT emp_id, emp_name, '二等獎'FROM employeeWHERE emp_id NOT IN (SELECT emp_id FROM emp_win)ORDER BY dbms_random.valueFETCH FIRST 2 ROWS ONLY; -- 一等獎1名INSERT INTO emp_winSELECT emp_id, emp_name, '一等獎'FROM employeeWHERE emp_id NOT IN (SELECT emp_id FROM emp_win)ORDER BY dbms_random.valueFETCH 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)ISBEGIN 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，可以參考這篇文章：
https://tonydong.blog.csdn.net/article/details/108007423
MySQL
MySQL 提供了一個系統函數?RAND，可以用于生成一個大于等于 0 小于 1 的隨機數字。利用這個函數，我們可以從表中返回隨機記錄。例如：
SELECT emp_id, emp_nameFROM employee ORDER BY RAND()LIMIT 1; emp_id|emp_name|------|--------| 19|龐統 |再次執行以上語句將會返回其他員工。我們也可以一次返回多名隨機的員工：
SELECT emp_id, emp_nameFROM 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_winSELECT emp_id, emp_name, '三等獎'FROM employeeWHERE 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_winSELECT emp_id, emp_name, '二等獎'FROM employeeWHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經中獎的員工ORDER BY RAND()LIMIT 2; -- 一等獎1名INSERT INTO emp_winSELECT emp_id, emp_name, '一等獎'FROM employeeWHERE 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，可以參考這篇文章：
https://tonydong.blog.csdn.net/article/details/108083399
Microsoft SQL Server
Microsoft SQL Server 提供了一個系統函數?NEWID，可以用于生成一個隨機的 GUID。利用這個函數，我們可以從表中返回隨機的數據行。例如：
SELECT TOP(1) emp_id, emp_nameFROM employee ORDER BY NEWID(); emp_id|emp_name|------|--------| 25|孫乾 |再次執行以上語句將會返回其他員工。我們也可以一次返回多名隨機員工：
SELECT TOP(3) emp_id, emp_nameFROM employee ORDER BY NEWID(); emp_id|emp_name|------|--------| 23|鄧芝 | 1|劉備 | 21|黃權 |雖然 Microsoft SQL Server 提供了一個返回隨機數字的 RAND 函數，但是該函數對于所有的數據行都返回相同的結果，因此不能用于返回表中的隨機記錄。例如：
SELECT TOP(3) emp_id, emp_name, RAND() AS rdFROM 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_winSELECT TOP(3) emp_id, emp_name, '三等獎'FROM employeeWHERE 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_winSELECT TOP(2) emp_id, emp_name, '二等獎'FROM employeeWHERE emp_id NOT IN (SELECT emp_id FROM emp_win)ORDER BY NEWID(); -- 一等獎1名INSERT INTO emp_winSELECT TOP(1) emp_id, emp_name, '一等獎'FROM employeeWHERE 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)ASBEGIN 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_winEND; EXEC luck_draw '特等獎', 1; emp_id|emp_name|grade|------|--------|-----| 14|張苞 |三等獎| 15|趙統 |一等獎| 17|馬岱 |三等獎| 18|法正 |二等獎| 21|黃權 |三等獎| 22|糜竺 |二等獎| 23|鄧芝 |特等獎|關于 Microsoft SQL Server 中如何生成隨機數字、字符串、日期、驗證碼以及 UUID，可以參考這篇文章：
https://tonydong.blog.csdn.net/article/details/111089346
PostgreSQL
PostgreSQL 提供了一個系統函數?RANDOM，可以用于生成一個大于等于 0 小于 1 的隨機數字。利用這個函數，我們可以從表中返回隨機記錄。例如：
SELECT emp_id, emp_nameFROM employee ORDER BY RANDOM()LIMIT 1; emp_id|emp_name|------|--------| 22|糜竺 |再次執行以上語句將會返回其他員工。我們也可以一次返回多名隨機的員工：
SELECT emp_id, emp_nameFROM 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_winSELECT emp_id, emp_name, '三等獎'FROM employeeWHERE 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_winSELECT emp_id, emp_name, '二等獎'FROM employeeWHERE emp_id NOT IN (SELECT emp_id FROM emp_win)ORDER BY RANDOM()LIMIT 2; -- 一等獎1名INSERT INTO emp_winSELECT emp_id, emp_name, '一等獎'FROM employeeWHERE 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 plpgsqlAS $$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，可以參考這篇文章：
https://tonydong.blog.csdn.net/article/details/109215148
SQLite
SQLite 中的?RANDOM?函數可以用于生成一個大于等于 -9223372036854775808 小于 9223372036854775807 的隨機整數。利用這個函數，我們可以從表中返回隨機的數據行。例如：
SELECT emp_id, emp_nameFROM employeeORDER BY RANDOM()LIMIT 1; emp_id|emp_name|------|--------| 4|諸葛亮 |再次執行以上語句將會返回其他員工。我們也可以一次返回多名隨機員工：
SELECT emp_id, emp_nameFROM employeeORDER 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_winSELECT emp_id, emp_name, '三等獎'FROM employeeWHERE 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_winSELECT emp_id, emp_name, '二等獎'FROM employeeWHERE emp_id NOT IN (SELECT emp_id FROM emp_win)ORDER BY RANDOM()LIMIT 2; -- 一等獎1名INSERT INTO emp_winSELECT emp_id, emp_name, '一等獎'FROM employeeWHERE 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，可以參考這篇文章：
https://tonydong.blog.csdn.net/article/details/111769715
總結
我們通過數據庫系統提供的隨機數函數返回表中的隨機記錄，從而實現年會抽獎的功能。
作者簡介：不剪發的 Tony 老師，CSDN 博客專家，CSDN 學院簽約講師， GitChat 專欄作者。十余年數據庫管理與開發經驗。目前在一家全球性的游戲公司從事數據庫架構設計和開發工作，擅長各種數據庫管理與 SQL 開發，擁有Oracle OCP 和 Redhat RHCE 證書。
更多閱讀推薦
都 2021 年了，Serverless 能取代微服務嗎？
企業使用云計算低效益怎么辦？區塊鏈或成良藥
創業公司用 Serverless，到底香不香？
45 年編程經驗告訴我的技術真相
AI 全自動翻譯漫畫文字，二次元界福音
總結
以上是生活随笔為你收集整理的使用 SQL 语句实现一个年会抽奖程序的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 征战云时代，为什么安全是关键命题？
- 下一篇： 开源项目如何挣钱？ Spark 商业化公