酷站(www.ku0.com)-致力于为互联网从业者提供动力!

热门关键词:  企业  as  baidu  c4rp3nt3r  美女
阿里云爆款特惠,精选爆款产品低至0.55折
酷站

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

来源:转载 作者:秩名 人气: 发布时间:2021-02-04
本篇文章主要介绍了使用SQL语句实现一个年会抽奖程序的代码,对大家的学习或者工作具有一定的参考学习价值,感兴趣的小伙伴们可以参考一下,也感谢大家对酷站(ku0.com)的支持。

年关将近,抽奖想必是大家在公司年会上最期待的活动了。如果老板让你做一个年会抽奖的程序,你会怎么实现呢?今天给大家介绍一下如何通过 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,可以参考这篇文章

总结

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

版权声明:本文内容来源于互联网或用户自行发布贡献,该文观点仅代表原作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 959677720#qq.cn(#换@) 举报,一经查实,本站将立刻删除。

您可能感兴趣的文章:

原文链接:https://blog.csdn.net/horses/article/details/111084926

相关文章

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

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

    年关将近,抽奖想必是大家在公司年会上最期待的活动了。如果老板让你做一个年会抽奖的程序,你会怎么实现呢?今天给大家介绍一下如何通过 SQL 语句来实现这个功能。实现的原理其实非常简单,就是通过函数为每个人分配一个随机数,然后取......
    02-04
  • mysql update case更新字段值不固定的操作

    mysql update case更新字段值不固定的操作

    在处理批量更新某些数据的时候,如果跟你更新的字段的值都一样,比如某个状态都更新为某个固定值, 直接用update table set xxx=xxx where xxx=xxx 这种即可 如果要更新的字段的值是不固定的,用下面的update case when where 这种方式就......
    02-04
  • mysql密码中有特殊字符&在命令行下登录的操作

    mysql密码中有特殊字符&在命令行下登录的操作

    在服务器上,通常为了快速登录数据库,我们会使用mysql -hhost -uusername -ppassword db的方式登录数据库,如果密码中没有特殊字符,会直接进入数据库sql命令行下,如果有特殊字符,那么就会出现如下提示: -bash: syntax error near un......
    02-04
  • MySQL修改字符集的教程

    MySQL修改字符集的教程

    在 MySQL 中,系统支持诸多字符集,不同字符集之间也略有区别。目前最常用的字符集应该是 utf8 和 utf8mb4 了,相比于 utf8 ,utf8mb4 支持存储 emoji 表情,使用范围更广。本篇文章将会介绍 utf8 修改成 utf8mb4 字符集的方法。 1. utf8......
    01-26
  • 详解SQL函数将某个字段合并在一起的操作

    详解SQL函数将某个字段合并在一起的操作

    最近遇到需要将关联表中的某个字段全部查询出来并且重新组合为一个字段,这个时候普通的连接查询就满足不了需求了,需要用到SQL函数来完成: ALTER function dbo.getResCodesByOwnerId(@OwnerId INT)returns nvarchar(2000)asbeginDECLAR......
    01-22
  • 详解Mysql合并结果接横向拼接字段

    详解Mysql合并结果接横向拼接字段

    前言: 近日在做一个报表功能里面有一个这样的需求是统计各部门在某一月入职和离职的人数 我的步骤 先查出入职的人数 1 2 3 4 5 SELECT dept , COUNT (1) rcNumber FROM 员工表 WHERE ( 入职时间 != OR 入职时间 IS NOT NULL ) and DATE_......
    01-15
  • MySQL explain获取查询指令信息原理介绍

    MySQL explain获取查询指令信息原理介绍

    explain用于获取查询执行计划信息, 一、语法 只需要在select前加上explain即可,如: mysql explain select 1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------......
    05-15
  • mysql查询每小时数据和上小时数据的差值的实现方法

    mysql查询每小时数据和上小时数据的差值的实现方法

    一、前言 需求是获取某个时间范围内每小时数据和上小时数据的差值以及比率。本来以为会是一个很简单的 sql ,结果思考两分钟发现并不简单,网上也没找到参考的方案,那就只能自己慢慢分析了。 刚开始没思路,就去问 DBA 同学,结果 DBA ......
    04-25
  • mysql条件查询and or使用方法及优先级

    mysql条件查询and or使用方法及优先级

    mysql and与or介绍 AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。 使用OR关键字时: 只要符合这几个查询条件的其中一个条件,这样的记录就会被查询出来。 如果不符合这些查询条件中的任何一条,这样的记录将被排除掉。 使用a......
    04-23
  • MySQL表中非主键列溢出情况监控的介绍

    MySQL表中非主键列溢出情况监控的介绍

    这次遇到的坑,更加的隐蔽。 是一个log表里面的一个int signed类型的列写满了。快速的解决方法当然还是只能切新表来救急了,然后搬迁老表的部分历史数据到热表。 亡羊补牢,处理完故障后,赶紧写脚本把生产的其他表都捋一遍。 下面是我暂......
    04-20

最新更新