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

文章目录

        Oracle
        MySQL
        Microsoft SQL Server
        PostgreSQL
        SQLite
        总结



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

 
再次执行以上语句将会返回其他员工。我们也可以一次返回多名随机的员工：
 

更多请见：http://www.mark-to-win.com/tutorial/51650.html