乘风原创程序

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

    📝本文使用的示例表可以点此下载

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

    总结

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