使用SQL语句有条件排名

Question

我必须在两个条件下对行进行排名。

| CBorderIID  |  CustomerID | CBDate  | orderID    |OrderDate  |
+-------------+-------------+---------+------------+-----------+
|  355253780  |      5314   |1/29/2017| 355253780  | 1/29/2017 |
|  355253780  |      5314   |1/29/2017| 359324061  | 4/5/2017  |
|  355253780  |      5314   |1/29/2017| 368700530  | 5/16/2017 |
|  355253780  |      5314   |1/29/2017| 375164457  | 9/11/2017 |
|  482387847  |      5384   |1/29/2017| 446421606  | 7/27/2018 |
|  482387847  |      5384   |1/29/2017| 449212357  | 8/5/2018  |
|  482387847  |     5384    |1/29/2017| 482387847  | 1/12/2019 |
|  482387847  |      5384   |1/29/2017| 489428993  | 2/23/2019 |

我想用cborderiid = orderID将行排名为1，然后在cborderiid＆lt;> orderiD和cbdate＆lt;当cborderiid＆lt;o. orderdate然后排名增量

所需的输出

| CBorderIID  |  CustomerID | CBDate  | orderID    |OrderDate  | Rank
+-------------+-------------+---------+------------+-----------+------
|  355253780  |      5314   |1/29/2017| 355253780  | 1/29/2017 | 1
|  355253780  |      5314   |1/29/2017| 359324061  | 4/5/2017  | 2
|  355253780  |      5314   |1/29/2017| 368700530  | 5/16/2017 | 3
|  355253780  |      5314   |1/29/2017| 375164457  | 9/11/2017 | 4
|  482387847  |      5384   |1/29/2017| 446421606  | 7/27/2018 | NULL
|  482387847  |      5384   |1/29/2017| 449212357  | 8/5/2018  | NULL
|  482387847  |     5384    |1/29/2017| 482387847  | 1/12/2019 | 1
|  482387847  |      5384   |1/29/2017| 489428993  | 2/23/2019 | 2

我尝试使用OrderID上的分区的条件进行排名，但行不通。

Answer 1

我认为这是您想要的逻辑：

select t.*
       (case when CBdate < O.orderdate
             then rank() over (partition by CBorderIID
                               order by ((case when CBorderIID = orderID then 1 else 2 end),
                                         OrderDate
                                        ) as rank
        end)
from t;

注意：如果没有什么与CBorderIID = orderID匹配，则第一阶日期的等级为1。如果多次匹配，则所有等级的等级为1。

这些与您如何提出问题一致，但还有其他可能性。

Answer 2

您可以将row_number()与定义的组使用：

select t.*, 
      (case when grp = 1 and CBdate <= orderdate 
            then row_number() over (partition by CBorderIID, CustomerID, grp order by OrderDate)
       end) as Rank
from (select t.*, 
             sum(case when CBorderIID = orderID then 1 else 0 end) over (partition by CBorderIID, CustomerID order by orderdate) as grp
      from table t
     ) t;