年关将近，抽奖想必是大家在公司年会上最期待的活动了。如果老板让你做一个年会抽奖的程序，你会怎么实现呢？今天给大家介绍一下如何通过 SQL 语句来实现这个功能。实现的原理其实非常简单，就是通过函数为每个人分配一个随机数，然后取最大或者最小的 N 个随机数对应的员工。
本文使用的示例表可以点此下载。
Oracle
Oracle 提供了一个系统程序包DBMS_RANDOM，可以用于生成随机数据，包括随机数字和随机字符串等。其中，DBMS_RANDOM.VALUE 函数可以用于生成一个大于等于 0 小于 1 的随机数字。利用这个函数，我们可以从表中返回随机的数据行。例如：
|
1
2
3
4
5
6
7
8
|
SELECT emp_id, emp_name
FROM employee
ORDER BY dbms_random.value
FETCH FIRST 1 ROWS ONLY;
EMP_ID|EMP_NAME|
------|--------|
3|张飞 |
|
再次执行以上查询将会返回其他员工。我们也可以一次返回多名随机员工：
|
1
2
3
4
5
6
7
8
9
10
|
SELECT emp_id, emp_name
FROM employee
ORDER BY dbms_random.value
FETCH FIRST 3 ROWS ONLY;
EMP_ID|EMP_NAME|
------|--------|
6|魏延 |
21|黄权 |
9|赵云 |
|
为了避免同一个员工中奖多次，可以创建一个存储已中奖员工的表：
每次开奖时
|
1
2
3
4
5
6
|
-- 中奖员工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 员工编号
emp_name varchar(50) NOT NULL, -- 员工姓名
grade varchar(50) NOT NULL -- 中奖级别
);
|
将中奖员工和级别存入 emp_win 表中，同时每次开奖时排除已经中奖的员工。例如，以下语句可以抽出 3 名三等奖：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 名一等奖：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
-- 二等奖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|廖化 |一等奖 |
|
我们可以进一步将以上语句封装成一个存储过程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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 的随机数字。利用这个函数，我们可以从表中返回随机记录。例如：
|
1
2
3
4
5
6
7
8
|
SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 1;
emp_id|emp_name|
------|--------|
19|庞统 |
|
再次执行以上语句将会返回其他员工。我们也可以一次返回多名随机的员工：
|
1
2
3
4
5
6
7
8
9
10
|
SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 3;
emp_id|emp_name|
------|--------|
1|刘备 |
20|蒋琬 |
23|邓芝 |
|
为了避免同一个员工中奖多次，我们可以创建一个存储已中奖员工的表：
|
1
2
3
4
5
6
|
-- 中奖员工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 员工编号
emp_name varchar(50) NOT NULL, -- 员工姓名
grade varchar(50) NOT NULL -- 中奖级别
);
|
每次开奖时将中奖员工和级别存入 emp_win 表中，同时每次开奖时排除已经中奖的员工。例如，以下语句可以抽出 3 名三等奖：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 名一等奖：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
-- 二等奖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|孙乾 |二等奖 |
|
我们可以进一步将以上语句封装成一个存储过程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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。利用这个函数，我们可以从表中返回随机的数据行。例如：
|
1
2
3
4
5
6
7
|
SELECT TOP(1) emp_id, emp_name
FROM employee
ORDER BY NEWID();
emp_id|emp_name|
------|--------|
25|孙乾 |
|
再次执行以上语句将会返回其他员工。我们也可以一次返回多名随机员工：
|
1
2
3
4
5
6
7
8
9
|
SELECT TOP(3) emp_id, emp_name
FROM employee
ORDER BY NEWID();
emp_id|emp_name|
------|--------|
23|邓芝 |
1|刘备 |
21|黄权 |
|
虽然 Microsoft SQL Server 提供了一个返回随机数字的 RAND 函数，但是该函数对于所有的数据行都返回相同的结果，因此不能用于返回表中的随机记录。例如：
|
1
2
3
4
5
6
7
8
9
|
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|
|
为了避免同一个员工中奖多次，我们可以创建一个存储已中奖员工的表：
|
1
2
3
4
5
6
|
-- 中奖员工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 员工编号
emp_name varchar(50) NOT NULL, -- 员工姓名
grade varchar(50) NOT NULL -- 中奖级别
);
|
我们在每次开奖时将中奖员工和级别存入 emp_win 表中，同时每次开奖时排除已经中奖的员工。例如，以下语句可以抽出 3 名三等奖：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 名一等奖：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
-- 二等奖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|糜竺 |二等奖|
|
我们可以进一步将以上语句封装成一个存储过程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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 的随机数字。利用这个函数，我们可以从表中返回随机记录。例如：
|
1
2
3
4
5
6
7
8
|
SELECT emp_id, emp_name
FROM employee
ORDER BY RANDOM()
LIMIT 1;
emp_id|emp_name|
------|--------|
22|糜竺 |
|
再次执行以上语句将会返回其他员工。我们也可以一次返回多名随机的员工：
|
1
2
3
4
5
6
7
8
9
10
|
SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 3;
emp_id|emp_name|
------|--------|
8|孙丫鬟 |
4|诸葛亮 |
9|赵云 |
|
为了避免同一个员工中奖多次，我们可以创建一个存储已中奖员工的表：
|
1
2
3
4
5
6
|
-- 中奖员工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 员工编号
emp_name varchar(50) NOT NULL, -- 员工姓名
grade varchar(50) NOT NULL -- 中奖级别
);
|
每次开奖时将中奖员工和级别存入 emp_win 表中，同时每次开奖时排除已经中奖的员工。例如，以下语句可以抽出 3 名三等奖：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 名一等奖：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
-- 二等奖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|糜竺 |一等奖|
|
我们可以进一步将以上语句封装成一个存储过程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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 的随机整数。利用这个函数，我们可以从表中返回随机的数据行。例如：
|
1
2
3
4
5
6
7
8
|
SELECT emp_id, emp_name
FROM employee
ORDER BY RANDOM()
LIMIT 1;
emp_id|emp_name|
------|--------|
4|诸葛亮 |
|
再次执行以上语句将会返回其他员工。我们也可以一次返回多名随机员工：
|
1
2
3
4
5
6
7
8
9
10
|
SELECT emp_id, emp_name
FROM employee
ORDER BY RANDOM()
LIMIT 3;
emp_id|emp_name|
------|--------|
16|周仓 |
15|赵统 |
11|关平 |
|
为了避免同一个员工中奖多次，我们可以创建一个存储已中奖员工的表：
|
1
2
3
4
5
6
|
-- 中奖员工表
CREATE TABLE emp_win(
emp_id integer PRIMARY KEY, -- 员工编号
emp_name varchar(50) NOT NULL, -- 员工姓名
grade varchar(50) NOT NULL -- 中奖级别
);
|
我们在每次开奖时将中奖员工和级别存入 emp_win 表中，同时每次开奖时排除已经中奖的员工。例如，以下语句可以抽出 3 名三等奖：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 名一等奖：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
-- 二等奖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年会抽奖程序内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://blog.csdn.net/horses/article/details/111084926