sql查找列停止递增的行

Question

我有一个类似这样的表。

creation_time           po_S            count_out_I
2012-08-03 02:45:02.133 000002029382    15974
2012-08-03 02:48:02.083 000002029382    9475
2012-08-03 02:51:02.097 000002029382    15978
2012-08-03 02:54:02.120 000002029382    15990

我需要找到按 creation_time 排序时 count_out_I 小于上一行的行。数据库是 SQL Server 2008。感谢帮助。

Answer 1

这将报告第一行 count_out_I 递减:

; with  numbered as
        (
        select  row_number() over (order by creation_time) as rn
        ,       *
        from    YourTable
        )
select  top 1 cur.*
from    numbered cur
join    numbered prev
on      prev.rn + 1 = cur.rn
where   cur.count_out_I < prev.count_out_I
order by
        cur.rn