如何在sql中通过两列匹配生成行号分区

Question

Tbl1

---------------------------------------------------------
Id      Date        Qty      ReOrder
---------------------------------------------------------
1       1-1-18      1        3
2       2-1-18      0        3
3       3-1-18      2        3
4       4-1-18      3<      >3
5       5-1-18      2        3
6       6-1-18      0        3
7       7-1-18      1        3
8       8-1-18      0        3
---------------------------------------------------------

我想要如下结果

---------------------------------------------------------
Id      Date        Qty      ReOrder
---------------------------------------------------------
1       1-1-18      1        3
5       5-1-18      2        3
---------------------------------------------------------

如果重新排序与数量不同，则在重新排序=数量之后的日期将相同

Answer 1

您可以将累积方法与row_number()函数一起使用：

select top (1) with ties *
from (select *, max(case when qty = reorder then 'v' end) over (order by id desc) grp
from table
) t
order by row_number() over(partition by grp order by id); 

不幸的是，这将需要SQL Server，但您也可以执行以下操作：

select *
from (select *, row_number() over(partition by grp order by id) seq
from (select *, max(case when qty = reorder then 'v' end) over (order by id desc) grp
from table
) t
) t
where seq = 1;