年关将近,抽奖想必是大家在公司年会上最期待的活动了。如果老板让你做一个年会抽奖的程序,你会怎么实现呢?今天给大家介绍一下如何通过 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,可以参考这篇文章。
总结
我们通过数据库系统提供的随机数函数返回表中的随机记录,从而实现年会抽奖的功能。
P70系列延期,华为新旗舰将在下月发布
3月20日消息,近期博主@数码闲聊站 透露,原定三月份发布的华为新旗舰P70系列延期发布,预计4月份上市。
而博主@定焦数码 爆料,华为的P70系列在定位上已经超过了Mate60,成为了重要的旗舰系列之一。它肩负着重返影像领域顶尖的使命。那么这次P70会带来哪些令人惊艳的创新呢?
根据目前爆料的消息来看,华为P70系列将推出三个版本,其中P70和P70 Pro采用了三角形的摄像头模组设计,而P70 Art则采用了与上一代P60 Art相似的不规则形状设计。这样的外观是否好看见仁见智,但辨识度绝对拉满。
更新动态
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]