SQL SERVER 选择行 | 我想要的输出

本文介绍了SQL SERVER 选择行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如果我有下面这张表

   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 选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！