如何编写Oracle查询找出员工同一天使用多张不同卡片的情况

需求说明 #

现有员工卡片关联表与卡片使用记录表，规定员工同一天仅可使用一张卡片，需识别两类异常：

1. 员工同一天使用多张本人名下的卡片
2. 卡片被出借（即卡片被非持有人使用，依据是持有人当天已使用另一张自己的卡片）

数据表结构 #

员工卡片关联表 #

卡片使用记录表 #

Oracle查询语句 #

以下查询可一次性找出两类异常，同时处理日期字符串转Oracle日期类型的问题，避免格式错误：

WITH card_usage_with_owner AS (
    SELECT 
        cu.ID AS usage_id,
        cu.Card_Id,
        TO_DATE(cu.Date, 'DD.MM.YYYY') AS usage_date,
        pc.Personel_ID AS owner_id
    FROM 
        卡片使用记录表 cu
    JOIN 
        员工卡片关联表 pc ON cu.Card_Id = pc.Card_Id
)
-- 异常类型1：同一天使用多张本人卡片
SELECT 
    owner_id AS 人员ID,
    TO_CHAR(usage_date, 'DD.MM.YYYY') AS 使用日期,
    LISTAGG(Card_Id, ', ') WITHIN GROUP (ORDER BY Card_Id) AS 关联卡片ID,
    '同一天使用多张本人卡片' AS 异常类型,
    NULL AS 记录ID
FROM 
    card_usage_with_owner
GROUP BY 
    owner_id, usage_date
HAVING 
    COUNT(DISTINCT Card_Id) > 1

UNION ALL

-- 异常类型2：卡片出借异常
SELECT 
    cu.owner_id AS 人员ID,
    TO_CHAR(cu.usage_date, 'DD.MM.YYYY') AS 使用日期,
    cu.Card_Id AS 关联卡片ID,
    '卡片出借异常' AS 异常类型,
    cu.usage_id AS 记录ID
FROM 
    card_usage_with_owner cu
WHERE 
    EXISTS (
        SELECT 1 
        FROM card_usage_with_owner 
        WHERE owner_id = cu.owner_id AND usage_date = cu.usage_date AND Card_Id != cu.Card_Id
    )
ORDER BY 
    使用日期, 异常类型;

查询逻辑解释 #

1. CTE预关联数据：将卡片使用记录与员工卡片关联表对接，补充每张卡片的持有人信息，并将字符串格式的日期转换为Oracle日期类型，确保日期比较的准确性。
2. 识别多卡使用异常：按人员ID和使用日期分组，统计每组内的不同卡片数量，数量大于1则判定为违反“一天仅用一张卡”的规定。
3. 识别出借异常：通过EXISTS子句检查，若某张卡片的持有人在同一天还有其他卡片的使用记录，则说明该卡片被非持有人使用（因为持有人当天只能用一张），判定为出借异常。
4. 合并结果：用UNION ALL合并两类异常数据，按日期和异常类型排序，方便查看。

内容的提问来源于stack exchange，提问作者Kuzgun