比较sql server中的下一行。

Question

我在下面的桌子上有一幅风景

1)列名: Operation_Name =‘种子器’，下一行Operation_Name = 'AUSEAL‘的意思

2)在下表中，我需要再添加一个列名Process_Time应该是AUSEAL的timestamp2 --播种机的timestamp2应该显示为process_Time

3)当Operation_Name‘播种机’和'AUSEAL‘相继出现时，只需计算process_time，否则Process_time应为'0’

下表的Select语句是：

select * from (
    select
        ROW_NUMBER() over(Order by timestamp2) ROW ,*
   from [dbo].[HIST_ACTIVITY_LOG3] where serial_no ='WCC1S8530252') t 



    Row   Serialno     timestamp2   Route_name      Operation_Name    WorkStat  Dis_Name host_Name 
    25  WCC1S8530252    2014-03-15 04:18:11 R1044   SEEDER  IN_PROCESS  PULL    MTB4AS-STW01    ASSPW 2.09.01   START   2014-03-14 12:06:58 STW 1049D   PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 9311B   58273
    26  WCC1S8530252    2014-03-15 04:26:09 R1044   AUSEAL  QUEUED  PASS    MTB4AS-STW02    ASSPW 2.09.01   COMPLETE    2014-03-15 04:18:11 SEEDER  1049D   PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 1049D   478
    27  WCC1S8530252    2014-03-15 05:15:48 R1044   INL_LEAK    QUEUED  PASS    MTB4BCI-ATS01   BCI 9.38.00 COMPLETE    2014-03-15 04:26:09 AUSEAL  9304B   PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 1049D   2979
    28  WCC1S8530252    2014-03-15 05:16:08 R1044   INL_LEAK    IN_PROCESS  NULL    MTB4AS-LKT02    ASSML 2.01.00   START   2014-03-15 05:15:47 INL_LEAK    RT-3713 PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 9304B   20
    29  WCC1S8530252    2014-03-15 05:16:17 R1044   PCBA    QUEUED  PASS    MTB4AS-LKT02    ASSML 2.01.00   COMPLETE    2014-03-15 05:16:17 INL_LEAK    RT-3713 PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 RT-3713 0
    30  WCC1S8530252    2014-03-15 05:16:17 R1044   INL_LEAK    IN_PROCESS  NULL    MTB4AS-LKT02    ASSML 2.01.00   START   2014-03-15 05:16:08 INL_LEAK    RT-3713 PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 RT-3713 9
    31  WCC1S8530252    2014-03-15 05:52:10 R1044   PCBA    IN_PROCESS  NULL    MTB4AS-PCB04    ASCR 2.54.00    START   2014-03-15 05:16:17 PCBA    9304B   PRIME   WD10EZEX-00ZF5  TRESSELS    DRV-B42 WD10EZEX-00ZF5  HDA-B43 RT-3713 2153
    32  WCC1S8530252    2014-03-15 05:52:29 R1044   XFILLER QUEUED  MOVE    MTB4AS-PCB04    ASCR 2.54.00    MOVE    2014-03-15 05:52:29 PCBA    9304B   PRIME   WD10EZEX-00ZF5  TRESSELS    DRV-B42 WD10EZEX-00ZF5  DRV-B42 9304B   19
    33  WCC1S8530252    2014-03-15 06:17:26 R1044   HELCRG  IN_PROCESS  PULL    MTB4AS-HEL02    AS 1.98.05  START   2014-03-15 05:52:29 XFILLER RT-02111    PRIME   WD10EZEX-00ZF5  TRESSELS    DRV-B42 WD10EZEX-00ZF5  DRV-B42 9304B   1497
    34  WCC1S8530252    2014-03-15 06:19:58 R1044   HELSEALI    QUEUED  PASS    MTB4AS-HEL02    AS 1.98.05  COMPLETE    2014-03-15 06:17:26 HELCRG  RT-02111    PRIME   WD10EZEX-00ZF5  TRESSELS    DRV-B42 WD10EZEX-00ZF5  DRV-B42 RT-02111    152
    35  WCC1S8530252    2014-03-15 06:27:02 R1044   HELSEALI    IN_PROCESS  NULL    MTB4AS-HEL04    AS 1.98.05  START   2014-03-15 06:19:58 HELSEALI    RT-0401 PRIME   WD10EZEX-00ZF5  TRESSELS    DRV-B42 WD10EZEX-00ZF5  DRV-B42 RT-02111    424

Answer 1

您可以在outer apply中获取下一个值。

select H1.Serialno,
       H1.timestamp2,
       H1.Route_name,
       H1.Operation_Name,
       isnull(datediff(second, H1.timestamp2, LeadH.timestamp2), 0) as TimeStampDiffSec
from HIST_ACTIVITY_LOG3 as H1
  outer apply (
              select top(1) H2.timestamp2
              from HIST_ACTIVITY_LOG3 as H2
              where H2.Operation_Name = 'AUSEAL' and
                    H1.Operation_Name = 'SEEDER' and
                    H2.timestamp2 > H1.timestamp2
              order by H2.timestamp2
              ) as LeadH

结果：

Serialno     timestamp2              Route_name Operation_Name TimeStampDiffSec
------------ ----------------------- ---------- -------------- ----------------
WCC1S8530252 2014-03-15 04:18:11.000 R1044      SEEDER         478
WCC1S8530252 2014-03-15 04:26:09.000 R1044      AUSEAL         0
WCC1S8530252 2014-03-15 05:15:48.000 R1044      INL_LEAK       0
WCC1S8530252 2014-03-15 05:16:08.000 R1044      INL_LEAK       0
WCC1S8530252 2014-03-15 05:16:17.000 R1044      PCBA           0
WCC1S8530252 2014-03-15 05:16:17.000 R1044      INL_LEAK       0
WCC1S8530252 2014-03-15 05:52:10.000 R1044      PCBA           0
WCC1S8530252 2014-03-15 05:52:29.000 R1044      XFILLER        0
WCC1S8530252 2014-03-15 06:17:26.000 R1044      HELCRG         0
WCC1S8530252 2014-03-15 06:19:58.000 R1044      HELSEALI       0
WCC1S8530252 2014-03-15 06:27:02.000 R1044      HELSEALI       0