使用 SQL 陳述句實作一個年會抽獎程式-有解無憂

文章目錄

- Oracle
- MySQL
- Microsoft SQL Server
- PostgreSQL
- SQLite
- 總結

大家好，我是只談技術不剪發的 Tony 老師，

年關將近，抽獎想必是大家在公司年會上最期待的活動了，如果老板讓你做一個年會抽獎的程式，你會怎么實作呢？今天給大家介紹一下如何通過 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，可以參考這篇文章，

總結

我們通過資料庫系統提供的亂數函式回傳表中的隨機記錄，從而實作年會抽獎的功能，

不剪發的Tony老師

CSDN認證博客專家資料庫架構師

畢業于北京航空航天大學，十多年資料庫管理與開發經驗，擁有OCP以及RHCE證書，目前在一家全球性的游戲公司從事資料庫架構設計和開發，CSDN學院簽約講師以及GitChat專欄作者，

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/247604.html

標籤：AI

上一篇：Jetpack框架探究01：Lifecycle組件的使用與原始碼分析

下一篇：攻防世界SignIn