Oracle LAG Function

/* 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';

转载于:https://www.cnblogs.com/kevinkim/archive/2012/02/07/2341686.html

评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符 “速评一下”
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页