Oracle LAG Function

最新推荐文章于 2025-08-01 15:48:18 发布

转载最新推荐文章于 2025-08-01 15:48:18 发布 · 197 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/kevinkim/archive/2012/02/07/2341686.html

文章标签：

#数据库

本文介绍了一个复杂的SQL查询案例，利用窗口函数LAG进行历史操作代码对比，筛选特定工厂模块下，满足特定条件的Lot历史记录。通过比较前两次和当前的操作代码，筛选出符合特定流程的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/* Formatted on 2012-02-07 오후 4:51:00 (QP5 v5.149.1003.31008) */
SELECT *
FROM ( SELECT
                 a.lot_id,
                 a.shift_start_timekey,
                 a.event_name,
                 a.old_oper_code,
                 a.oper_code,
                 '-',
                 LAG (old_oper_code, 2) OVER (ORDER BY lot_id, event_timekey)
                    PREVIOUS_2,
                 LAG (old_oper_code, 1) OVER (ORDER BY lot_id, event_timekey)
                    PREVIOUS_1,
                 a.old_oper_code PREVIOUS_0
            FROM eds_lot_hist a
           WHERE     a.factory = 'MODULE'         --AND OLD_OPER_CODE = 'R510'
                 --                 AND a.lot_id = '8AWX1Z0277B1C6'
                 AND shift_start_timekey = '20120101 060000'
                 AND a.old_oper_code <> a.oper_code
                 AND a.event_name IN ('TrackOut', 'TrackOutRework', 'FA')
        ORDER BY lot_id, event_timekey)
WHERE     PREVIOUS_2 IN ('R540', 'R530', 'R550', 'R510')
       AND PREVIOUS_1 = 'R551'
       AND PREVIOUS_0 = 'R510';