按时间排序的同时保持SQL排名

Question

考虑到这些数据

Type    Time    Outcome Wanted Result
1   8:00    1   1
1   9:00    1   1
1   10:00   1   1
0   11:00   2   2
0   12:00   2   2
0   13:00   2   2
1   14:00   1   3
1   15:00   1   3
0   16:00   2   4
1   17:00   1   5
0   18:00   2   6
1   19:00   1   7

第三列是我使用以下sql查询语句得到的当前结果：

SELECT Type, Time, DENSE_RANK() OVER (ORDER BY Type) as Outcome
FROM Tbl

我需要使用dense_rank函数在按类型排名数据的同时保持时间排序。第四列是期望的结果。

我正在使用SQL Server 2008。

- rprecioso

Answer 1

这有点棘手。你可以通过行号之差来完成。这种方法一开始可能有点难以理解，建议您运行子查询以了解其中的原理。当您查看结果时，很快就会“get it”：

select t.*,
       dense_rank() over (order by mintime) as desired_column
from (select t.*,
             min(time) over (partition by id, seqnum_t - seqnum_it) as mintime
      from (select t.*, 
                   row_number() over (order by time) as seqnum_t,
                   row_number() over (partition by id order by time) as seqnum_it
            from tbl t
           ) t
     ) t;

- Gordon Linoff

---

感谢@Gordon Linoff！我刚刚将 min(time) over (partition by seqnum_t - seqnum_it) as mintime 更改为 min(time) over (partition by seqnum_t - seqnum_it,id) as mintime - rprecioso