在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

使用 SQL 語(yǔ)句實(shí)現(xiàn)一個(gè)年會(huì)抽獎(jiǎng)程序

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        Oracle
        MySQL
        Microsoft SQL Server
        PostgreSQL
        SQLite
        總結(jié)



年關(guān)將近，抽獎(jiǎng)想必是大家在公司年會(huì)上最期待的活動(dòng)了。如果老板讓你做一個(gè)年會(huì)抽獎(jiǎng)的程序，你會(huì)怎么實(shí)現(xiàn)呢？今天給大家介紹一下如何通過 SQL 語(yǔ)句來實(shí)現(xiàn)這個(gè)功能。實(shí)現(xiàn)的原理其實(shí)非常簡(jiǎn)單，就是通過函數(shù)為每個(gè)人分配一個(gè)隨機(jī)數(shù)，然后取最大或者最小的 N 個(gè)隨機(jī)數(shù)對(duì)應(yīng)的員工。



Oracle

Oracle 提供了一個(gè)系統(tǒng)程序包 DBMS_RANDOM，可以用于生成隨機(jī)數(shù)據(jù)，包括隨機(jī)數(shù)字和隨機(jī)字符串等。其中，DBMS_RANDOM.VALUE 函數(shù)可以用于生成一個(gè)大于等于 0 小于 1 的隨機(jī)數(shù)字。利用這個(gè)函數(shù)，我們可以從表中返回隨機(jī)的數(shù)據(jù)行。例如：

SELECT emp_id, emp_name
FROM employee
ORDER BY dbms_random.value
FETCH FIRST 1 ROWS ONLY;

EMP_ID|EMP_NAME|
------|--------|
     3|張飛    |

   

再次執(zhí)行以上查詢將會(huì)返回其他員工。我們也可以一次返回多名隨機(jī)員工：

SELECT emp_id, emp_name
FROM employee
ORDER BY dbms_random.value
FETCH FIRST 3 ROWS ONLY;

EMP_ID|EMP_NAME|
------|--------|
     6|魏延    |
    21|黃權(quán)    |
     9|趙云    |

   

為了避免同一個(gè)員工中獎(jiǎng)多次，可以創(chuàng)建一個(gè)存儲(chǔ)已中獎(jiǎng)員工的表：

-- 中獎(jiǎng)員工表
CREATE TABLE emp_win(
  emp_id integer PRIMARY KEY, -- 員工編號(hào)
  emp_name varchar(50) NOT NULL, -- 員工姓名
  grade varchar(50) NOT NULL -- 中獎(jiǎng)級(jí)別
);

   

每次開獎(jiǎng)時(shí)將中獎(jiǎng)員工和級(jí)別存入 emp_win 表中，同時(shí)每次開獎(jiǎng)時(shí)排除已經(jīng)中獎(jiǎng)的員工。例如，以下語(yǔ)句可以抽出 3 名三等獎(jiǎng)：

INSERT INTO emp_win
SELECT emp_id, emp_name, '三等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎(jiǎng)的員工
ORDER BY dbms_random.value
FETCH FIRST 3 ROWS ONLY;

SELECT * FROM emp_win;

EMP_ID|EMP_NAME|GRADE   |
------|--------|--------|
     8|孫丫鬟   |三等獎(jiǎng)  |
     3|張飛     |三等獎(jiǎng)  |
     9|趙云     |三等獎(jiǎng)  |

    
繼續(xù)抽出 2 名二等獎(jiǎng)和 1 名一等獎(jiǎng)：

-- 二等獎(jiǎng)2名
INSERT INTO emp_win
SELECT emp_id, emp_name, '二等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win)
ORDER BY dbms_random.value
FETCH FIRST 2 ROWS ONLY;

-- 一等獎(jiǎng)1名
INSERT INTO emp_win
SELECT emp_id, emp_name, '一等獎(jiǎng)'
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|孫丫鬟  |三等獎(jiǎng)  |
     3|張飛    |三等獎(jiǎng)  |
     9|趙云    |三等獎(jiǎng)  |
     6|魏延    |二等獎(jiǎng)  |
    22|糜竺    |二等獎(jiǎng)  |
    10|廖化    |一等獎(jiǎng)  |


我們可以進(jìn)一步將以上語(yǔ)句封裝成一個(gè)存儲(chǔ)過程：

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('特等獎(jiǎng)', 1);

SELECT * FROM emp_win WHERE grade = '特等獎(jiǎng)';

EMP_ID|EMP_NAME|GRADE  |
------|--------|-------|
    25|孫乾    |特等獎(jiǎng)  |

   

關(guān)于 Oracle 中如何生成隨機(jī)數(shù)字、字符串、日期、驗(yàn)證碼以及 UUID，可以參考這篇文章。
MySQL

MySQL 提供了一個(gè)系統(tǒng)函數(shù) RAND，可以用于生成一個(gè)大于等于 0 小于 1 的隨機(jī)數(shù)字。利用這個(gè)函數(shù)，我們可以從表中返回隨機(jī)記錄。例如：

SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 1;

emp_id|emp_name|
------|--------|
    19|龐統(tǒng)    |

 
再次執(zhí)行以上語(yǔ)句將會(huì)返回其他員工。我們也可以一次返回多名隨機(jī)的員工：

SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 3;

emp_id|emp_name|
------|--------|
     1|劉備    |
    20|蔣琬    |
    23|鄧芝    |

    

為了避免同一個(gè)員工中獎(jiǎng)多次，我們可以創(chuàng)建一個(gè)存儲(chǔ)已中獎(jiǎng)員工的表：

-- 中獎(jiǎng)員工表
CREATE TABLE emp_win(
  emp_id integer PRIMARY KEY, -- 員工編號(hào)
  emp_name varchar(50) NOT NULL, -- 員工姓名
  grade varchar(50) NOT NULL -- 中獎(jiǎng)級(jí)別
);

   
每次開獎(jiǎng)時(shí)將中獎(jiǎng)員工和級(jí)別存入 emp_win 表中，同時(shí)每次開獎(jiǎng)時(shí)排除已經(jīng)中獎(jiǎng)的員工。例如，以下語(yǔ)句可以抽出 3 名三等獎(jiǎng)：

INSERT INTO emp_win
SELECT emp_id, emp_name, '三等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎(jiǎng)的員工
ORDER BY RAND()
LIMIT 3;

SELECT * FROM emp_win;

emp_id|emp_name|grade  |
------|--------|-------|
    18|法正    |三等獎(jiǎng)  |
    23|鄧芝    |三等獎(jiǎng)  |
    24|簡(jiǎn)雍    |三等獎(jiǎng)  |

 

我們繼續(xù)抽出 2 名二等獎(jiǎng)和 1 名一等獎(jiǎng)：

-- 二等獎(jiǎng)2名
INSERT INTO emp_win
SELECT emp_id, emp_name, '二等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎(jiǎng)的員工
ORDER BY RAND()
LIMIT 2;

-- 一等獎(jiǎng)1名
INSERT INTO emp_win
SELECT emp_id, emp_name, '一等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎(jiǎng)的員工
ORDER BY RAND()
LIMIT 1;

SELECT * FROM emp_win;

emp_id|emp_name|grade  |
------|--------|-------|
     2|關(guān)羽    |二等獎(jiǎng)  |
    18|法正    |三等獎(jiǎng)  |
    20|蔣琬    |一等獎(jiǎng)  |
    23|鄧芝    |三等獎(jiǎng)  |
    24|簡(jiǎn)雍    |三等獎(jiǎng)  |
    25|孫乾    |二等獎(jiǎng)  |

 
我們可以進(jìn)一步將以上語(yǔ)句封裝成一個(gè)存儲(chǔ)過程：

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('特等獎(jiǎng)', 1);

emp_id|emp_name|grade  |
------|--------|-------|
     2|關(guān)羽    |二等獎(jiǎng)  |
     8|孫丫鬟  |特等獎(jiǎng)  |
    18|法正    |三等獎(jiǎng)  |
    20|蔣琬    |一等獎(jiǎng)  |
    23|鄧芝    |三等獎(jiǎng)  |
    24|簡(jiǎn)雍    |三等獎(jiǎng)  |
    25|孫乾    |二等獎(jiǎng)  |



關(guān)于 MySQL 中如何生成隨機(jī)數(shù)字、字符串、日期、驗(yàn)證碼以及 UUID，可以參考這篇文章。
Microsoft SQL Server

Microsoft SQL Server 提供了一個(gè)系統(tǒng)函數(shù) NEWID，可以用于生成一個(gè)隨機(jī)的 GUID。利用這個(gè)函數(shù)，我們可以從表中返回隨機(jī)的數(shù)據(jù)行。例如：

SELECT TOP(1) emp_id, emp_name
FROM employee
ORDER BY NEWID();

emp_id|emp_name|
------|--------|
    25|孫乾    |

    

再次執(zhí)行以上語(yǔ)句將會(huì)返回其他員工。我們也可以一次返回多名隨機(jī)員工：

SELECT TOP(3) emp_id, emp_name
FROM employee
ORDER BY NEWID();

emp_id|emp_name|
------|--------|
    23|鄧芝    |
     1|劉備    |
    21|黃權(quán)    |

   

雖然 Microsoft SQL Server 提供了一個(gè)返回隨機(jī)數(shù)字的 RAND 函數(shù)，但是該函數(shù)對(duì)于所有的數(shù)據(jù)行都返回相同的結(jié)果，因此不能用于返回表中的隨機(jī)記錄。例如：

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|關(guān)平    |0.8623555267583647|

    

為了避免同一個(gè)員工中獎(jiǎng)多次，我們可以創(chuàng)建一個(gè)存儲(chǔ)已中獎(jiǎng)員工的表：

-- 中獎(jiǎng)員工表
CREATE TABLE emp_win(
  emp_id integer PRIMARY KEY, -- 員工編號(hào)
  emp_name varchar(50) NOT NULL, -- 員工姓名
  grade varchar(50) NOT NULL -- 中獎(jiǎng)級(jí)別
);

    

我們?cè)诿看伍_獎(jiǎng)時(shí)將中獎(jiǎng)員工和級(jí)別存入 emp_win 表中，同時(shí)每次開獎(jiǎng)時(shí)排除已經(jīng)中獎(jiǎng)的員工。例如，以下語(yǔ)句可以抽出 3 名三等獎(jiǎng)：

INSERT INTO emp_win
SELECT TOP(3) emp_id, emp_name, '三等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎(jiǎng)的員工
ORDER BY NEWID();

SELECT * FROM emp_win;

emp_id|emp_name|grade|
------|--------|-----|
    14|張苞    |三等獎(jiǎng)|
    17|馬岱    |三等獎(jiǎng)|
    21|黃權(quán)    |三等獎(jiǎng)|

   

繼續(xù)抽出 2 名二等獎(jiǎng)和 1 名一等獎(jiǎng)：

-- 二等獎(jiǎng)2名
INSERT INTO emp_win
SELECT TOP(2) emp_id, emp_name, '二等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win)
ORDER BY NEWID();

-- 一等獎(jiǎng)1名
INSERT INTO emp_win
SELECT TOP(1) emp_id, emp_name, '一等獎(jiǎng)'
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|張苞    |三等獎(jiǎng)|
    15|趙統(tǒng)    |一等獎(jiǎng)|
    17|馬岱    |三等獎(jiǎng)|
    18|法正    |二等獎(jiǎng)|
    21|黃權(quán)    |三等獎(jiǎng)|
    22|糜竺    |二等獎(jiǎng)|


我們可以進(jìn)一步將以上語(yǔ)句封裝成一個(gè)存儲(chǔ)過程：

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 '特等獎(jiǎng)', 1;

emp_id|emp_name|grade|
------|--------|-----|
    14|張苞    |三等獎(jiǎng)|
    15|趙統(tǒng)    |一等獎(jiǎng)|
    17|馬岱    |三等獎(jiǎng)|
    18|法正    |二等獎(jiǎng)|
    21|黃權(quán)    |三等獎(jiǎng)|
    22|糜竺    |二等獎(jiǎng)|
    23|鄧芝    |特等獎(jiǎng)|

 
關(guān)于 Microsoft SQL Server 中如何生成隨機(jī)數(shù)字、字符串、日期、驗(yàn)證碼以及 UUID，可以參考這篇文章。
PostgreSQL

PostgreSQL 提供了一個(gè)系統(tǒng)函數(shù) RANDOM，可以用于生成一個(gè)大于等于 0 小于 1 的隨機(jī)數(shù)字。利用這個(gè)函數(shù)，我們可以從表中返回隨機(jī)記錄。例如：

SELECT emp_id, emp_name
FROM employee
ORDER BY RANDOM()
LIMIT 1;

emp_id|emp_name|
------|--------|
    22|糜竺    |

    

再次執(zhí)行以上語(yǔ)句將會(huì)返回其他員工。我們也可以一次返回多名隨機(jī)的員工：

SELECT emp_id, emp_name
FROM employee
ORDER BY RAND()
LIMIT 3;

emp_id|emp_name|
------|--------|
     8|孫丫鬟   |
     4|諸葛亮   |
     9|趙云     |

 

為了避免同一個(gè)員工中獎(jiǎng)多次，我們可以創(chuàng)建一個(gè)存儲(chǔ)已中獎(jiǎng)員工的表：

-- 中獎(jiǎng)員工表
CREATE TABLE emp_win(
  emp_id integer PRIMARY KEY, -- 員工編號(hào)
  emp_name varchar(50) NOT NULL, -- 員工姓名
  grade varchar(50) NOT NULL -- 中獎(jiǎng)級(jí)別
);

   

每次開獎(jiǎng)時(shí)將中獎(jiǎng)員工和級(jí)別存入 emp_win 表中，同時(shí)每次開獎(jiǎng)時(shí)排除已經(jīng)中獎(jiǎng)的員工。例如，以下語(yǔ)句可以抽出 3 名三等獎(jiǎng)：

INSERT INTO emp_win
SELECT emp_id, emp_name, '三等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎(jiǎng)的員工
ORDER BY RANDOM()
LIMIT 3;

SELECT * FROM emp_win;

emp_id|emp_name|grade|
------|--------|-----|
    23|鄧芝    |三等獎(jiǎng)|
    15|趙統(tǒng)    |三等獎(jiǎng)|
    24|簡(jiǎn)雍    |三等獎(jiǎng)|

   
我們繼續(xù)抽出 2 名二等獎(jiǎng)和 1 名一等獎(jiǎng)：

-- 二等獎(jiǎng)2名
INSERT INTO emp_win
SELECT emp_id, emp_name, '二等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win)
ORDER BY RANDOM()
LIMIT 2;

-- 一等獎(jiǎng)1名
INSERT INTO emp_win
SELECT emp_id, emp_name, '一等獎(jiǎng)'
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|鄧芝    |三等獎(jiǎng)|
    15|趙統(tǒng)    |三等獎(jiǎng)|
    24|簡(jiǎn)雍    |三等獎(jiǎng)|
     1|劉備    |二等獎(jiǎng)|
    21|黃權(quán)    |二等獎(jiǎng)|
    22|糜竺    |一等獎(jiǎng)|

我們可以進(jìn)一步將以上語(yǔ)句封裝成一個(gè)存儲(chǔ)過程：

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('特等獎(jiǎng)', 1);

SELECT * FROM emp_win WHERE grade = '特等獎(jiǎng)';

emp_id|emp_name|grade|
------|--------|-----|
     5|黃忠    |特等獎(jiǎng)|

   

關(guān)于 PostgreSQL 中如何生成隨機(jī)數(shù)字、字符串、日期、驗(yàn)證碼以及 UUID，可以參考這篇文章。
SQLite

SQLite 中的 RANDOM 函數(shù)可以用于生成一個(gè)大于等于 -9223372036854775808 小于 9223372036854775807 的隨機(jī)整數(shù)。利用這個(gè)函數(shù)，我們可以從表中返回隨機(jī)的數(shù)據(jù)行。例如：

SELECT emp_id, emp_name
FROM employee
ORDER BY RANDOM()
LIMIT 1;

emp_id|emp_name|
------|--------|
     4|諸葛亮   |

 
再次執(zhí)行以上語(yǔ)句將會(huì)返回其他員工。我們也可以一次返回多名隨機(jī)員工：

SELECT emp_id, emp_name
FROM employee
ORDER BY RANDOM()
LIMIT 3;

emp_id|emp_name|
------|--------|
    16|周倉(cāng)    |
    15|趙統(tǒng)    |
    11|關(guān)平    |

   
為了避免同一個(gè)員工中獎(jiǎng)多次，我們可以創(chuàng)建一個(gè)存儲(chǔ)已中獎(jiǎng)員工的表：

-- 中獎(jiǎng)員工表
CREATE TABLE emp_win(
  emp_id integer PRIMARY KEY, -- 員工編號(hào)
  emp_name varchar(50) NOT NULL, -- 員工姓名
  grade varchar(50) NOT NULL -- 中獎(jiǎng)級(jí)別
);



我們?cè)诿看伍_獎(jiǎng)時(shí)將中獎(jiǎng)員工和級(jí)別存入 emp_win 表中，同時(shí)每次開獎(jiǎng)時(shí)排除已經(jīng)中獎(jiǎng)的員工。例如，以下語(yǔ)句可以抽出 3 名三等獎(jiǎng)：

INSERT INTO emp_win
SELECT emp_id, emp_name, '三等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win) -- 排除已經(jīng)中獎(jiǎng)的員工
ORDER BY RANDOM()
LIMIT 3;

SELECT * FROM emp_win;

emp_id|emp_name|grade|
------|--------|-----|
     2|關(guān)羽    |三等獎(jiǎng)|
     3|張飛    |三等獎(jiǎng)|
     8|孫丫鬟  |三等獎(jiǎng)|

 
繼續(xù)抽出 2 名二等獎(jiǎng)和 1 名一等獎(jiǎng)：

-- 二等獎(jiǎng)2名
INSERT INTO emp_win
SELECT emp_id, emp_name, '二等獎(jiǎng)'
FROM employee
WHERE emp_id NOT IN (SELECT emp_id FROM emp_win)
ORDER BY RANDOM()
LIMIT 2;

-- 一等獎(jiǎng)1名
INSERT INTO emp_win
SELECT emp_id, emp_name, '一等獎(jiǎng)'
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|關(guān)羽    |三等獎(jiǎng)|
     3|張飛    |三等獎(jiǎng)|
     4|諸葛亮  |一等獎(jiǎng)|
     8|孫丫鬟  |三等獎(jiǎng)|
    16|周倉(cāng)    |二等獎(jiǎng)|
    23|鄧芝    |二等獎(jiǎng)|



關(guān)于 SQLite 中如何生成隨機(jī)數(shù)字、字符串、日期、驗(yàn)證碼以及 UUID，可以參考這篇文章。
總結(jié)

我們通過數(shù)據(jù)庫(kù)系統(tǒng)提供的隨機(jī)數(shù)函數(shù)返回表中的隨機(jī)記錄，從而實(shí)現(xiàn)年會(huì)抽獎(jiǎng)的功能。