SQL进阶技巧：如何生成一个员工及其有效门禁卡的列表？

标签：进阶门禁卡 issued emp SQL date badge id

0 需求描述

1 数据准备

2 问题分析

方法1:使用窗口函数row_number()

方法2:采用case when

3 小结

往期精彩

0 需求描述

目标：生成一个员工及其有效门禁卡的列表。
规则
员工可能有多个门禁卡。
每个员工在同一时间只能有一个有效的门禁卡。
默认最新的门禁卡是有效的（因为是在新工作场所发出的）。
门禁卡号是随机的。
用 A 表示门禁卡有效（active），I 表示失效（inactive）。

1 数据准备

1、创建用户表
create table personnel
( emp_id integer not null primary key ,
emp_name char ( 30 ) not null
)
--2、创建门禁卡状态表
create table Badges
(
badge_nbr integer not null primary key ,
emp_id integer not null references personnel ( emp_id ),
issued_date date not null,
badge_status char ( 1 ) not null check ( badge_status in ( 'A' , 'I' )),
)
--3、插入初始化数据
insert into personnel ( emp_id , emp_name )
values ( 1 , 'Backer' ),( 2 , 'Tal' ),( 3 , 'Cak' ),( 4 , 'Dake' )                                         
insert into Badges ( badge_nbr , emp_id , issued_date , badge_status )
values ( 1 , 1 , '2017-11-08' , 'A' ),
( 2 , 1 , '2017-11-09' , 'A' ),
( 3 , 1 , '2017-11-10' , 'A' ),
( 4 , 1 , '2017-11-11' , 'A' ),
( 5 , 1 , '2017-11-12' , 'A' ),
( 6 , 1 , '2017-11-13' , 'A' ),
( 7 , 2 , '2017-11-08' , 'A' ),
( 8 , 2 , '2017-11-09' , 'A' ),
( 9 , 2 , '2017-11-10' , 'A' ),
( 10 , 3 , '2017-11-08' , 'A' ),
( 11 , 3 , '2017-11-09' , 'A' ),
( 12 , 4 , '2017-11-08' , 'A' )

2 问题分析

方法1:使用窗口函数row_number()

-- 使用窗口函数和子查询为每个员工的门禁卡分配一个行号，最新的为 1
WITH RankedBadges AS (
    SELECT 
        badge_nbr, 
        emp_id, 
        issued_date, 
        badge_status,
        ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY issued_date DESC) AS rn
    FROM Badges
)
-- 更新门禁卡状态，将最新的设为 'A'，其余设为 'I'
SELECT 
    badge_nbr,
    emp_id,
    issued_date,
    CASE WHEN rn = 1 THEN 'A' ELSE 'I' END AS badge_status
FROM RankedBadges;

方法2:采用case when

-- 创建临时表存储每个员工的最新门禁卡的 issued_date
with LatestBadges AS
(SELECT 
    emp_id, 
    MAX(issued_date) AS latest_issued_date
FROM Badges
GROUP BY emp_id) 

-- 更新门禁卡状态，将最新的设为 'A'，其余设为 'I'
SELECT 
    b.badge_nbr,
    b.emp_id,
    b.issued_date,
    CASE WHEN b.issued_date = lb.latest_issued_date THEN 'A' ELSE 'I' END AS badge_status
FROM Badges b
JOIN LatestBadges lb ON b.emp_id = lb.emp_id;

3 小结

如果有多个员工在同一天发放门禁卡，该如何处理？

如果你希望将同一天发放的多个门禁卡都标记为有效，可以使用 DENSE_RANK() 函数替代 ROW_NUMBER() 函数。DENSE_RANK() 函数会给相同的数据分配相同的排名，并且排名是连续的，不会跳过任何排名。以下是修改后的代码：

-- 使用 DENSE_RANK 函数更新门禁卡状态
WITH RankedBadges AS (
    SELECT 
        badge_nbr,
        emp_id,
        issued_date,
        badge_status,
        DENSE_RANK() OVER (PARTITION BY emp_id ORDER BY issued_date DESC) AS rn
    FROM Badges
)
UPDATE Badges b
JOIN RankedBadges rb ON b.badge_nbr = rb.badge_nbr
SET b.badge_status = CASE 
                      WHEN rb.rn = 1 THEN 'A' 
                      ELSE 'I' 
                  END;

或者

-- 使用窗口函数更新门禁卡状态，将同一天发放的都标记为有效
WITH RankedBadges AS (
    SELECT 
        badge_nbr,
        emp_id,
        issued_date,
        badge_status,
        CASE 
            WHEN issued_date = MAX(issued_date) OVER (PARTITION BY emp_id) THEN 'A'
            ELSE 'I'
        END AS new_badge_status
    FROM Badges
)
UPDATE Badges b
JOIN RankedBadges rb ON b.badge_nbr = rb.badge_nbr
SET b.badge_status = rb.new_badge_status;