桃源资源网

使用 SQL 语句实现一个年会抽奖程序的代码
数据库 2024/12/27 佚名
3 2

年关将近，抽奖想必是大家在公司年会上最期待的活动了。如果老板让你做一个年会抽奖的程序，你会怎么实现呢？今天给大家介绍一下如何通过 SQL 语句来实现这个功能。实现的原理其实非常简单，就是通过函数为每个人分配一个随机数，然后取最大或者最小的 N 个随机数对应的员工。

"external nofollow" target="_blank" href="https://github.com/dongxuyang1985/thinking_in_sql">可以点此下载。

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，可以参考这篇文章。

总结

我们通过数据库系统提供的随机数函数返回表中的随机记录，从而实现年会抽奖的功能。

标签：
sql年会抽奖程序,sql抽奖程序
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
桃源资源网 Design By www.nqtax.com
www.nqtax.com 桃源资源网
19,905影音资源
44,626技术资源
1,817软件资源
378,645站长资源

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
桃源资源网 Design By www.nqtax.com