使用 SQL 语句实现一个年会抽奖程序的代码

<p>年关将近，抽奖想必是大家在公司年会上最期待的活动了。如果老板让你做一个年会抽奖的程序，你会怎么实现呢？今天给大家介绍一下如何通过 SQL 语句来实现这个功能。实现的原理其实非常简单，就是通过函数为每个人分配一个随机数，然后取最大或者最小的 N 个随机数对应的员工。</p>
<blockquote>
<p>📝本文使用的示例表<a href="https://github.com/dongxuyang1985/thinking_in_sql" rel="external nofollow" target="_blank">可以点此下载</a>。</p>
</blockquote>
<h2>Oracle</h2>
<p>Oracle 提供了一个系统程序包<a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_RANDOM.html" rel="external nofollow" target="_blank">DBMS_RANDOM</a>，可以用于生成随机数据，包括随机数字和随机字符串等。其中，DBMS_RANDOM.VALUE 函数可以用于生成一个大于等于 0 小于 1 的随机数字。利用这个函数，我们可以从表中返回随机的数据行。例如：</p>
<div class="blockcode">
<pre class="brush:sql;">
SELECT emp_id, emp_name
FROM employee
ORDER BY dbms_random.value
FETCH FIRST 1 ROWS ONLY;

EMP_ID|EMP_NAME|
------|--------|
3|张飞 |</pre>
</div>
<p>再次执行以上查询将会返回其他员工。我们也可以一次返回多名随机员工：</p>
<div class="blockcode">
<pre class="brush:sql;">
SELECT emp_id, emp_name
FROM employee
ORDER BY dbms_random.value
FETCH FIRST 3 ROWS ONLY;

EMP_ID|EMP_NAME|
------|--------|
6|魏延 |
21|黄权 |
9|赵云 |</pre>
</div>
<p>为了避免同一个员工中奖多次，可以创建一个存储已中奖员工的表：</p>
<p>每次开奖时</p>
<div class="blockcode">
<pre class="brush:sql;">
-- 中奖员工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 员工编号
emp_name varchar(50) NOT NULL, -- 员工姓名
grade varchar(50) NOT NULL -- 中奖级别
);</pre>
</div>
<p>将中奖员工和级别存入 emp_win 表中，同时每次开奖时排除已经中奖的员工。例如，以下语句可以抽出 3 名三等奖：</p>
<div class="blockcode">
<pre class="brush:sql;">
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|赵云 |三等奖 |</pre>
</div>
<p>继续抽出 2 名二等奖和 1 名一等奖：</p>
<div class="blockcode">
<pre class="brush:sql;">
-- 二等奖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|廖化 |一等奖 |</pre>
</div>
<p>我们可以进一步将以上语句封装成一个存储过程：</p>
<div class="blockcode">
<pre class="brush:sql;">
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|孙乾 |特等奖 |</pre>
</div>
<p>关于 Oracle 中如何生成随机数字、字符串、日期、验证码以及 UUID，可以<a href="https://www.jb51.net/article/205366.htm" target="_blank">参考这篇文章。</a></p>
<h2>MySQL</h2>
<p>MySQL 提供了一个系统函数<a href="https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_rand" rel="external nofollow" target="_blank">RAND</a>，可以用于生成一个大于等于 0 小于 1 的随机数字。利用这个函数，我们可以从表中返回随机记录。例如：</p>
<div class="blockcode">
<pre class="brush:sql;">
SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 1;

emp_id|emp_name|
------|--------|
19|庞统 |</pre>
</div>
<p>再次执行以上语句将会返回其他员工。我们也可以一次返回多名随机的员工：</p>
<div class="blockcode">
<pre class="brush:sql;">
SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 3;

emp_id|emp_name|
------|--------|
1|刘备 |
20|蒋琬 |
23|邓芝 |</pre>
</div>
<p>为了避免同一个员工中奖多次，我们可以创建一个存储已中奖员工的表：</p>
<div class="blockcode">
<pre class="brush:sql;">
-- 中奖员工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 员工编号
emp_name varchar(50) NOT NULL, -- 员工姓名
grade varchar(50) NOT NULL -- 中奖级别
);</pre>
</div>
<p>每次开奖时将中奖员工和级别存入 emp_win 表中，同时每次开奖时排除已经中奖的员工。例如，以下