sql - 使用 Case 的 SQL Window 函数

Question

我正在尝试返回 ID 和状态可以相同的记录，只要支付日期不同。如果一个 ID 有多个状态，那么它只需要返回一条记录，其顺序优先级为 Approved、Denied、Pending、First Notice。但是，如果一个 ID 具有多个不同日期的 Approved 状态，则它需要返回两条记录。

   select *, row_number()over(partition by id, paid_date order by
                              CASE when status ='Approved' then 1
                                   when status = 'Denied' then 2
                                   when status = 'Pending' then 3
                                   when status = 'First Notice then 4 end                                            
                                          ) as rownum
   from @t

这是最终输出的显示方式：

   ID     Paid_Date      Status
   1      2020-01-01      Approved
   1      2020-02-01      Approved
   2      2020-02-03      Approved
   3      2020-02-04      Denied
   4      2020-02-04      Denied
   5      2020-02-05      Approved

以下是一些示例数据：

   declare @t table(id int, paid_date date, status varchar(25))

insert into @t
values(1, '2020-01-01', 'Approved'),
(1, '2020-02-01', 'Approved'),
(1, NULL, 'First Notice'),
(2, '2020-02-03', 'Approved'),
(3, '2020-02-03', 'Pending'),
(3, '2020-02-04', 'Denied'),
(4, '2020-02-04', 'Denied'),
(5, '2020-02-05',  'Approved'),
(5, '2020-02-05', 'Approved')

select * from @t

对此的任何帮助将不胜感激。我觉得我很接近，但无法走到尽头。

标签： sqlsql-servertsqlcase

Answer 1

添加一秒钟row_number以检测多个已批准但日期不同的情况，例如

with cte as (
    select id, paid_date, [status]
        , row_number() over (partition by id order by
            case when status ='Approved' then 1
            when status = 'Denied' then 2
            when status = 'Pending' then 3
            when status = 'First Notice' then 4 end asc
        ) as rownum1
        , case when [status] = 'Approved' then row_number() over (partition by id, paid_date order by paid_date) else null end as rownum2
    from @t
    where paid_date is not null
)
select id, paid_date, [status]
from cte
where rownum1 = 1 /* Highest priority as defined */ or rownum2 = 1 /* Or approved but different date */
order by id, paid_date, [status];

回报：

ID	支付日期	地位
1	2020-01-01	得到正式认可的
1	2020-02-01	得到正式认可的
2	2020-02-03	得到正式认可的
3	2020-02-04	拒绝
4	2020-02-04	拒绝
5	2020-02-05	得到正式认可的