如何在SQL Server中使用行号获取匹配数据？

Question

如何在SQL Server中使用行号获取匹配数据？

sqlsql-serversql-server-2008-r2

3

我是一名新手SQL Server，不知道该如何表达我的问题。我有种感觉这可能会重复。如果你知道，请标记为重复。我将通过数据解释我想要实现的内容。

表数据 - sometable

ID  TKID    Status    DateTimeStamp            RunMin
-----------------------------------------------------
215  6      Start   2009-10-29 09:48:14.243    NULL
261  6      Stop    2009-10-30 10:05:16.460    1457
356  6      Start   2009-11-03 14:11:05.097    NULL
357  6      Stop    2009-11-03 15:20:05.133    1526
358  6      Start   2009-11-03 16:14:45.863    NULL
574  19     Start   2009-11-12 13:12:11.827    NULL
575  19     Stop    2009-11-12 13:47:23.077    35
543 259     Start   2009-11-12 09:01:24.013    NULL 
620 259     Stop    2009-11-14 11:25:30.177    NULL 
623 259     Start   2009-11-14 16:47:32.913    NULL 
720 352     Start   2009-11-18 17:47:38.637    NULL 
730 352     Stop    2009-11-19 08:22:28.317    874  
773 352     Start   2009-11-20 10:00:11.320    NULL 
778 352     Stop    2009-11-20 11:51:59.853    985  
812 352     Start   2009-11-20 17:51:35.640    NULL 
813 352     Stop    2009-11-20 17:53:52.373    987  
822 352     Start   2009-11-23 08:13:23.030    NULL 
823 352     Stop    2009-11-23 08:17:33.063    991  
901 352     Start   2009-12-01 10:50:16.547    NULL 
910 352     Stop    2009-12-01 10:50:54.200    991

预期输出：

ID  TKID    Status    DateTimeStamp            RunMin      
-----------------------------------------------------
358  6      Start   2009-11-03 16:14:45.863    NULL     
623 259     Start   2009-11-14 16:47:32.913    NULL

基本上，我想获取具有“开始”状态但没有“停止”状态的记录。

现在我尝试过以下方法...

我尝试使用以下ROW_NUMBER函数：

;with cte as 
(
    select 
       *,
       ROW_NUMBER() OVER (PARTITION BY tkid 
                          ORDER BY tkid, DateTimeStamp) AS rn
    from Prog_Timer
)
SELECT * 
FROM 
   (SELECT * 
    FROM cte
    WHERE TkID IN (SELECT TkID
                   FROM cte 
                   GROUP BY TkID
                   HAVING COUNT(*)% 2 = 1)
) as d

它给我返回的结果是

然后尝试使用Odd计数获取tkid，并为该tkid获取数据。

ID  TKID    Status    DateTimeStamp            RunMin
-----------------------------------------------------
215  6      Start   2009-10-29 09:48:14.243    NULL
261  6      Stop    2009-10-30 10:05:16.460    1457
356  6      Start   2009-11-03 14:11:05.097    NULL
357  6      Stop    2009-11-03 15:20:05.133    1526
358  6      Start   2009-11-03 16:14:45.863    NULL
543 259     Start   2009-11-12 09:01:24.013    NULL 
620 259     Stop    2009-11-14 11:25:30.177    NULL 
623 259     Start   2009-11-14 16:47:32.913    NULL

我不知道如何从每个tkid的输出中只获取最后一行，仅选择最后一个开始行。我觉得我的方法相当复杂。必须有简单的方法来获得我想要的。如果您有新的方法，请随意发布。如果您对我的现有查询有任何意见，请随意发布。如有疑问，请评论。

- user2745246

2

要不按日期降序排序，然后选择行号=1呢？ - Tanner

5个回答

1

许多方法可以实现这个，其中之一如下：

SELECT T.*
FROM SOMETABLE T
JOIN (SELECT MAX(DATETIMESTAMP) MAXTIME, TKID
    FROM SOMETABLE 
    GROUP BY TKID) SRC
        ON SRC.MAXTIME = T.DATETIMESTAMP AND SRC.TKID = T.TKID
WHERE T.STATUS = 'Start'

根据实际数据，如果有可能某个进程同时停止和启动，您可能希望获取MAX（ID）。但是，在更新的情况下，旧的ID值可能具有更新的时间戳。

- Kahn

我从来没有想过使用JOIN（甚至从未考虑过）。但这绝对是一个非常不同和好的方法。谢谢你。 - user2745246

1

我认为这可以展示你的结果：

select *
from Prog_Timer st
where st.[Status] = 'Start' And
    isnull((select min(sti.DateTimeStamp) from Prog_Timer sti  
            where sti.[Status] = 'Stop' And sti.TKId = st.TKId And sti.DateTimeStamp > st.DateTimeStamp )
        , cast('2999/12/29' as datetime)) 
    > 
    isnull((select min(stii.LogDateTime) from Prog_Timer stii 
            where stii.[Status] = 'Start' And stii.TKId = st.TKId And stii.DateTimeStamp > st.DateTimeStamp)
        , cast('2999/12/29' as datetime))

但我正在等待最佳答案 ;)。

- shA.t

我讨厌为同一个目的使用多个子查询。但无论如何，感谢您的帮助。 - user2745246

我写这个的原因是最后一行将只显示当前正在运行的“开始”，而没有“停止”。我的意思是，如果有人当前已经开始记录，它将显示为失败的记录，并且我还会显示所有失败的记录。 - shA.t

哦！我实际上回答了另一个问题 =)。 - shA.t

0

这样怎么样：

;with cte as 
(
    select *
         , MAX(DateTimeStamp) OVER (PARTITION BY tkid) AS maxDt
    from Prog_Timer
)
SELECT * 
FROM cte
WHERE DateTimeStamp=maxDt and Status='Start'

- A ツ

-3

尝试这个查询：

SELECT * 
FROM tableName 
WHERE Status = 'start' 
  AND ID NOT IN (SELECT ID 
                 FROM tableName 
                 WHERE Status = 'stop')

- wins

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ASh · Accepted Answer

如果您将ROW_NUMBER() PARTITION修改为order by DateTimeStamp desc，则每个tkid的最新行将被赋予rn=1，从而为每个tkid提供最新状态。然后，您只需SELECT其中的行，其中rn = 1且Status = 'Start'即可获得所需的输出：

select * from
(select 
   *,
   ROW_NUMBER() OVER (PARTITION BY tkid order by DateTimeStamp desc) as rn
from Prog_Timer
)
T
where 
T.rn = 1 -- the latest status for each tkid 
and T.Status = 'Start' -- returns only started and not stopped timers
-- if timer is stopped, t.Status will be 'Stop' in the latest row