SQL SERVER 选择行 [英] SQL SERVER SELECTING ROW

问题描述

如果我有下面这张表

   id         time
   start      08.00 AM 
   stop       09.00 AM
   stop       09.30 AM
   start      09.30 AM
   start      11.00 AM
   start      11.30 AM
   stop       11.40 AM
   stop       12.00 PM

我想要的输出只需要每次第一次启动和每次停止后开始和最新停止

I want output that only take every first start and every start after stop and the latest stop

有什么解决办法吗?

这是我想要的输出:

   id         time
   start      08.00 AM 
   stop       09.00 AM
   start      09.30 AM
   stop       11.40 AM
   stop       12.00 PM

推荐答案

 ;WITH cte AS
 (
  SELECT id AS pr, [time], ROW_NUMBER() OVER(ORDER BY [time]) AS Id,
         COUNT(*) OVER() AS cnt        
  FROM dbo.test21 t1
  ), cte2 AS
 (
  SELECT Id, pr, [time], pr AS prStart, [time] AS StopTime, pr AS prStop, [time] AS StartTime
  FROM cte
  WHERE Id = 1
  UNION ALL
  SELECT c1.Id, 
         CASE WHEN c1.pr != c2.pr OR c1.Id = c1.cnt THEN c1.pr ELSE c2.pr END,
         CASE WHEN c1.pr != c2.pr OR c1.Id = c1.cnt THEN c1.[time] ELSE c2.[time] END,
         c1.pr, c1.[time], c2.pr, c2.[time]
  FROM cte c1 JOIN cte2 c2 ON c1.Id = c2.Id + 1
  )
  SELECT pr, [time], MIN(StartTime) AS StartTime,
         MAX(DATEDIFF(minute, StartTime, [time])) AS Interval
  FROM cte2
  GROUP BY pr, [time]

关于 SQLFiddle

这篇关于SQL SERVER 选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！