SQL需求：查询15分钟内跨多设施提交记录的员工

核心思路 #

要解决这个问题，关键是关联同一员工的多条记录，同时满足两个条件：

• 记录来自不同设施（FAC_NUM不同）
• 两条记录的时间间隔在15分钟以内

推荐两种实现方式：窗口函数法（高效，适合现代数据库）和自连接法（兼容老版本数据库）。

一、窗口函数法（推荐） #

利用LAG()窗口函数获取同一员工的上一条记录的设施和时间，直接在子查询中判断时间差和设施差异。

示例代码（MySQL） #

SELECT 
    CI_NUM,
    INSP_DTE,
    FAC_NUM,
    prev_fac_num,
    prev_insp_dte
FROM (
    SELECT 
        CI_NUM,
        INSP_DTE,
        FAC_NUM,
        -- 获取上一条记录的设施编号
        LAG(FAC_NUM) OVER (
            PARTITION BY CI_NUM 
            ORDER BY STR_TO_DATE(INSP_DTE, '%m/%d/%Y %h:%i:%s %p')
        ) AS prev_fac_num,
        -- 获取上一条记录的提交时间（原字符串格式）
        LAG(INSP_DTE) OVER (
            PARTITION BY CI_NUM 
            ORDER BY STR_TO_DATE(INSP_DTE, '%m/%d/%Y %h:%i:%s %p')
        ) AS prev_insp_dte,
        -- 计算与上一条记录的时间差（分钟）
        TIMESTAMPDIFF(MINUTE, 
            LAG(STR_TO_DATE(INSP_DTE, '%m/%d/%Y %h:%i:%s %p')) OVER (
                PARTITION BY CI_NUM 
                ORDER BY STR_TO_DATE(INSP_DTE, '%m/%d/%Y %h:%i:%s %p')
            ),
            STR_TO_DATE(INSP_DTE, '%m/%d/%Y %h:%i:%s %p')
        ) AS time_diff_minutes
    FROM your_table_name -- 替换为你的表名
) t
WHERE 
    prev_fac_num IS NOT NULL -- 排除员工的第一条记录
    AND FAC_NUM != prev_fac_num -- 设施不同
    AND time_diff_minutes <= 15 -- 时间差≤15分钟
    AND time_diff_minutes >= 0 -- 确保是后续记录，避免负时间差

其他数据库适配说明 #

• SQL Server：替换时间转换和时间差函数
  • 时间转换：CONVERT(DATETIME, INSP_DTE, 100)（匹配m/d/yyyy h:mm:ss AM/PM格式）
  • 时间差：DATEDIFF(MINUTE, 上一条时间, 当前时间)
• PostgreSQL：
  • 时间转换：TO_TIMESTAMP(INSP_DTE, 'MM/DD/YYYY HH12:MI:SS AM')
  • 时间差：EXTRACT(MINUTE FROM (当前时间 - 上一条时间)) <= 15

二、自连接法（兼容老版本数据库） #

通过自连接关联同一员工的两条记录，直接筛选时间差和设施条件。

示例代码（MySQL） #

SELECT 
    t1.CI_NUM,
    t1.INSP_DTE AS first_submit_time,
    t1.FAC_NUM AS first_facility,
    t2.INSP_DTE AS second_submit_time,
    t2.FAC_NUM AS second_facility
FROM your_table_name t1
JOIN your_table_name t2 
    ON t1.CI_NUM = t2.CI_NUM -- 同一员工
    AND t1.FAC_NUM != t2.FAC_NUM -- 不同设施
    -- 确保t2是t1之后的记录
    AND STR_TO_DATE(t1.INSP_DTE, '%m/%d/%Y %h:%i:%s %p') < STR_TO_DATE(t2.INSP_DTE, '%m/%d/%Y %h:%i:%s %p')
    -- 时间差≤15分钟
    AND TIMESTAMPDIFF(MINUTE, STR_TO_DATE(t1.INSP_DTE, '%m/%d/%Y %h:%i:%s %p'), STR_TO_DATE(t2.INSP_DTE, '%m/%d/%Y %h:%i:%s %p')) <= 15
ORDER BY t1.CI_NUM, t1.INSP_DTE

扩展需求：统计符合条件的员工数量 #

如果只需要计数，在上述查询外层套一个COUNT(DISTINCT CI_NUM)即可：

SELECT COUNT(DISTINCT CI_NUM) AS qualified_employee_count
FROM (
    -- 这里放入窗口函数法的子查询部分
    SELECT 
        CI_NUM,
        TIMESTAMPDIFF(MINUTE, 
            LAG(STR_TO_DATE(INSP_DTE, '%m/%d/%Y %h:%i:%s %p')) OVER (PARTITION BY CI_NUM ORDER BY STR_TO_DATE(INSP_DTE, '%m/%d/%Y %h:%i:%s %p')),
            STR_TO_DATE(INSP_DTE, '%m/%d/%Y %h:%i:%s %p')
        ) AS time_diff_minutes,
        FAC_NUM,
        LAG(FAC_NUM) OVER (PARTITION BY CI_NUM ORDER BY STR_TO_DATE(INSP_DTE, '%m/%d/%Y %h:%i:%s %p')) AS prev_fac_num
    FROM your_table_name
) t
WHERE 
    prev_fac_num IS NOT NULL
    AND FAC_NUM != prev_fac_num
    AND time_diff_minutes <= 15
    AND time_diff_minutes >= 0

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