sql server 窗口函数学习

 

 

with t1 as (

SELECT [QC]

      ,[intime]

      ,[action]

      ,[info]

      ,[name]

  FROM .[PLCinfo] a with (nolock)

   where intime>'2020-08-17 13:00'

  and intime<'2020-08-18 14:50'

  and QC='QC10'

  and action='开闭锁'

  and info like '%LandSideIn%'

  ),

  t2 as (

  SELECT info,intime,pic

  FROM [openbayes_cps].[dbo].[cpsinfo]

  where intime>'2020-08-17 13:00'

  and intime<'2020-08-18 14:50'

   ),

   t3 as (

   select  t1.action as 陆侧开闭锁,t1.intime as 时间点, JSON_VALUE(t2.info, '$.CpsResult.result') as cps结果, JSON_VALUE(t2.info, '$.CpsResult.lane') as 车道,t2.intime,pic,

   ROW_NUMBER() OVER (PARTITION BY t1.action ,t1.intime  ORDER BY ABS(REPLACE(JSON_VALUE(t2.info, '$.CpsResult.result'),'%',''))) NUM

    from t1 left join t2

    on datediff(second, t2.intime,t1.intime)<30 and datediff(second, t2.intime,t1.intime)>1

)

select 陆侧开闭锁,时间点,cps结果 as 识别结果,车道,pic as 图片  from  t3  WHERE NUM=1

ORDER BY 陆侧开闭锁,时间点