目錄
- Oracle
- MySQL
- Microsoft SQL Server
- PostgreSQL
- SQLite
- 總結(jié)
年關將近,抽獎想必是大家在公司年會上最期待的活動了。如果老板讓你做一個年會抽獎的程序,你會怎么實現(xiàn)呢?今天給大家介紹一下如何通過 SQL 語句來實現(xiàn)這個功能。實現(xiàn)的原理其實非常簡單,就是通過函數(shù)為每個人分配一個隨機數(shù),然后取最大或者最小的 N 個隨機數(shù)對應的員工。
📝本文使用的示例表可以點此下載。
Oracle
Oracle 提供了一個系統(tǒng)程序包DBMS_RANDOM,可以用于生成隨機數(shù)據(jù),包括隨機數(shù)字和隨機字符串等。其中,DBMS_RANDOM.VALUE 函數(shù)可以用于生成一個大于等于 0 小于 1 的隨機數(shù)字。利用這個函數(shù),我們可以從表中返回隨機的數(shù)據(jù)行。例如:
SELECT emp_id, emp_name
FROM employee
ORDER BY dbms_random.value
FETCH FIRST 1 ROWS ONLY;
EMP_ID|EMP_NAME|
------|--------|
3|張飛 |
再次執(zhí)行以上查詢將會返回其他員工。我們也可以一次返回多名隨機員工:
SELECT emp_id, emp_name
FROM employee
ORDER BY dbms_random.value
FETCH FIRST 3 ROWS ONLY;
EMP_ID|EMP_NAME|
------|--------|
6|魏延 |
21|黃權 |
9|趙云 |
為了避免同一個員工中獎多次,可以創(chuàng)建一個存儲已中獎員工的表:
每次開獎時
-- 中獎員工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 員工編號
emp_name varchar(50) NOT NULL, -- 員工姓名
grade varchar(50) NOT NULL -- 中獎級別
);
將中獎員工和級別存入 emp_win 表中,同時每次開獎時排除已經(jīng)中獎的員工。例如,以下語句可以抽出 3 名三等獎:
INSERT INTO emp_win
SELECT emp_id, emp_name, '三等獎'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎的員工
ORDER BY dbms_random.value
FETCH FIRST 3 ROWS ONLY;
SELECT * FROM emp_win;
EMP_ID|EMP_NAME|GRADE |
------|--------|--------|
8|孫丫鬟 |三等獎 |
3|張飛 |三等獎 |
9|趙云 |三等獎 |
繼續(xù)抽出 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 中如何生成隨機數(shù)字、字符串、日期、驗證碼以及 UUID,可以參考這篇文章。
MySQL
MySQL 提供了一個系統(tǒng)函數(shù)RAND,可以用于生成一個大于等于 0 小于 1 的隨機數(shù)字。利用這個函數(shù),我們可以從表中返回隨機記錄。例如:
SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 1;
emp_id|emp_name|
------|--------|
19|龐統(tǒng) |
再次執(zhí)行以上語句將會返回其他員工。我們也可以一次返回多名隨機的員工:
SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 3;
emp_id|emp_name|
------|--------|
1|劉備 |
20|蔣琬 |
23|鄧芝 |
為了避免同一個員工中獎多次,我們可以創(chuàng)建一個存儲已中獎員工的表:
-- 中獎員工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 員工編號
emp_name varchar(50) NOT NULL, -- 員工姓名
grade varchar(50) NOT NULL -- 中獎級別
);
每次開獎時將中獎員工和級別存入 emp_win 表中,同時每次開獎時排除已經(jīng)中獎的員工。例如,以下語句可以抽出 3 名三等獎:
INSERT INTO emp_win
SELECT emp_id, emp_name, '三等獎'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎的員工
ORDER BY RAND()
LIMIT 3;
SELECT * FROM emp_win;
emp_id|emp_name|grade |
------|--------|-------|
18|法正 |三等獎 |
23|鄧芝 |三等獎 |
24|簡雍 |三等獎 |
我們繼續(xù)抽出 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) -- 排除已經(jīng)中獎的員工
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) -- 排除已經(jīng)中獎的員工
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 中如何生成隨機數(shù)字、字符串、日期、驗證碼以及 UUID,可以參考這篇文章。
Microsoft SQL Server
Microsoft SQL Server 提供了一個系統(tǒng)函數(shù)NEWID,可以用于生成一個隨機的 GUID。利用這個函數(shù),我們可以從表中返回隨機的數(shù)據(jù)行。例如:
SELECT TOP(1) emp_id, emp_name
FROM employee
ORDER BY NEWID();
emp_id|emp_name|
------|--------|
25|孫乾 |
再次執(zhí)行以上語句將會返回其他員工。我們也可以一次返回多名隨機員工:
SELECT TOP(3) emp_id, emp_name
FROM employee
ORDER BY NEWID();
emp_id|emp_name|
------|--------|
23|鄧芝 |
1|劉備 |
21|黃權 |
雖然 Microsoft SQL Server 提供了一個返回隨機數(shù)字的 RAND 函數(shù),但是該函數(shù)對于所有的數(shù)據(jù)行都返回相同的結(jié)果,因此不能用于返回表中的隨機記錄。例如:
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|
為了避免同一個員工中獎多次,我們可以創(chuàng)建一個存儲已中獎員工的表:
-- 中獎員工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 員工編號
emp_name varchar(50) NOT NULL, -- 員工姓名
grade varchar(50) NOT NULL -- 中獎級別
);
我們在每次開獎時將中獎員工和級別存入 emp_win 表中,同時每次開獎時排除已經(jīng)中獎的員工。例如,以下語句可以抽出 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) -- 排除已經(jīng)中獎的員工
ORDER BY NEWID();
SELECT * FROM emp_win;
emp_id|emp_name|grade|
------|--------|-----|
14|張苞 |三等獎|
17|馬岱 |三等獎|
21|黃權 |三等獎|
繼續(xù)抽出 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|趙統(tǒng) |一等獎|
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|趙統(tǒng) |一等獎|
17|馬岱 |三等獎|
18|法正 |二等獎|
21|黃權 |三等獎|
22|糜竺 |二等獎|
23|鄧芝 |特等獎|
關于 Microsoft SQL Server 中如何生成隨機數(shù)字、字符串、日期、驗證碼以及 UUID,可以參考這篇文章。
PostgreSQL
PostgreSQL 提供了一個系統(tǒng)函數(shù) RANDOM,可以用于生成一個大于等于 0 小于 1 的隨機數(shù)字。利用這個函數(shù),我們可以從表中返回隨機記錄。例如:
SELECT emp_id, emp_name
FROM employee
ORDER BY RANDOM()
LIMIT 1;
emp_id|emp_name|
------|--------|
22|糜竺 |
再次執(zhí)行以上語句將會返回其他員工。我們也可以一次返回多名隨機的員工:
SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 3;
emp_id|emp_name|
------|--------|
8|孫丫鬟 |
4|諸葛亮 |
9|趙云 |
為了避免同一個員工中獎多次,我們可以創(chuàng)建一個存儲已中獎員工的表:
-- 中獎員工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 員工編號
emp_name varchar(50) NOT NULL, -- 員工姓名
grade varchar(50) NOT NULL -- 中獎級別
);
每次開獎時將中獎員工和級別存入 emp_win 表中,同時每次開獎時排除已經(jīng)中獎的員工。例如,以下語句可以抽出 3 名三等獎:
INSERT INTO emp_win
SELECT emp_id, emp_name, '三等獎'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎的員工
ORDER BY RANDOM()
LIMIT 3;
SELECT * FROM emp_win;
emp_id|emp_name|grade|
------|--------|-----|
23|鄧芝 |三等獎|
15|趙統(tǒng) |三等獎|
24|簡雍 |三等獎|
我們繼續(xù)抽出 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|趙統(tǒng) |三等獎|
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 中如何生成隨機數(shù)字、字符串、日期、驗證碼以及 UUID,可以參考這篇文章。
SQLite
SQLite 中的RANDOM 函數(shù)可以用于生成一個大于等于 -9223372036854775808 小于 9223372036854775807 的隨機整數(shù)。利用這個函數(shù),我們可以從表中返回隨機的數(shù)據(jù)行。例如:
SELECT emp_id, emp_name
FROM employee
ORDER BY RANDOM()
LIMIT 1;
emp_id|emp_name|
------|--------|
4|諸葛亮 |
再次執(zhí)行以上語句將會返回其他員工。我們也可以一次返回多名隨機員工:
SELECT emp_id, emp_name
FROM employee
ORDER BY RANDOM()
LIMIT 3;
emp_id|emp_name|
------|--------|
16|周倉 |
15|趙統(tǒng) |
11|關平 |
為了避免同一個員工中獎多次,我們可以創(chuàng)建一個存儲已中獎員工的表:
-- 中獎員工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 員工編號
emp_name varchar(50) NOT NULL, -- 員工姓名
grade varchar(50) NOT NULL -- 中獎級別
);
我們在每次開獎時將中獎員工和級別存入 emp_win 表中,同時每次開獎時排除已經(jīng)中獎的員工。例如,以下語句可以抽出 3 名三等獎:
INSERT INTO emp_win
SELECT emp_id, emp_name, '三等獎'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎的員工
ORDER BY RANDOM()
LIMIT 3;
SELECT * FROM emp_win;
emp_id|emp_name|grade|
------|--------|-----|
2|關羽 |三等獎|
3|張飛 |三等獎|
8|孫丫鬟 |三等獎|
繼續(xù)抽出 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 中如何生成隨機數(shù)字、字符串、日期、驗證碼以及 UUID,可以參考這篇文章。
總結(jié)
我們通過數(shù)據(jù)庫系統(tǒng)提供的隨機數(shù)函數(shù)返回表中的隨機記錄,從而實現(xiàn)年會抽獎的功能。
到此這篇關于使用 SQL 語句實現(xiàn)一個年會抽獎程序的文章就介紹到這了,更多相關sql年會抽獎程序內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
您可能感興趣的文章:- jQuery+PHP+Mysql實現(xiàn)抽獎程序
- .net+mssql制作抽獎程序思路及源碼