如何用MySQL查询存入后一周内未取出的物品数据？

要找出存入（Store）后未在一周内完成取出（Withdraw）的物品，可通过以下两种常用SQL方案实现：

方法1：自连接（兼容多数SQL数据库） #

通过将operation表自身关联，匹配同一articleNumber的存入与取出记录，筛选出无合规取出记录的存入数据：

SELECT 
    s.idOperation,
    s.articleNumber,
    s.articleLabel,
    s.userIdentifier AS store_user_id,
    s.userName AS store_user_name,
    s.dateTime AS store_time,
    w.idOperation AS withdraw_id,
    w.userIdentifier AS withdraw_user_id,
    w.userName AS withdraw_user_name,
    w.dateTime AS withdraw_time
FROM operation s
LEFT JOIN operation w 
    ON s.articleNumber = w.articleNumber
    AND w.actionName = 'Withdraw'
    AND w.dateTime <= DATE_ADD(s.dateTime, INTERVAL 7 DAY)
WHERE s.actionName = 'Store'
AND w.idOperation IS NULL;

逻辑说明 #

• 以所有存入记录作为主表（别名s）
• 左连接自身表（别名w），仅匹配同一物品、操作类型为取出且时间在存入后7天内的记录
• 最终筛选出未匹配到合规取出记录的存入数据，即为需排查的目标

方法2：窗口函数（支持窗口函数的数据库：MySQL 8.0+/PostgreSQL等） #

利用LEAD()窗口函数获取同一物品的下一条操作信息，直接判断是否为合规取出：

WITH operation_with_next_action AS (
    SELECT 
        *,
        LEAD(dateTime) OVER (PARTITION BY articleNumber ORDER BY dateTime) AS next_action_time,
        LEAD(actionName) OVER (PARTITION BY articleNumber ORDER BY dateTime) AS next_action_type
    FROM operation
    WHERE actionName IN ('Store', 'Withdraw')
)
SELECT 
    idOperation,
    articleNumber,
    articleLabel,
    userIdentifier,
    userName,
    dateTime AS store_time,
    next_action_time,
    next_action_type
FROM operation_with_next_action
WHERE actionName = 'Store'
AND (
    next_action_type IS NULL 
    OR next_action_type != 'Withdraw' 
    OR next_action_time > DATE_ADD(dateTime, INTERVAL 7 DAY)
);

逻辑说明 #

• 先用CTE给每条记录添加上同一物品的下一条操作时间与类型
• 筛选存入记录，且满足以下任一条件的即为目标数据：
  • 无后续操作
  • 后续操作不是取出
  • 后续取出时间超过存入时间7天

结果示例 #

执行上述任一语句，将返回Article_02的存入记录，即你需要排查的未在一周内取出的物品数据。

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