我有一个下表
ID,DATE,Status
359,2021-05-01,M
359,2021-05-01,R
359,2021-04-01,M
759,2021-05-01,R
759,2021-04-01,O
123,2021-05-01,M
123,2021-04-01,O
123,2021-03-31,U
我想要下面的结果
359,2021-05-01,R
759,2021-05-01,R
123,2021-05-01,M
日期-最大/最晚日期
状态:
- 第一个首选项应该是R(如果找到R则退出(
- 如果R不存在,那么M
- 如果M不存在,那么O
- 如果O不存在,则U等
有人能帮我吗?
提前感谢
一种方法是row_number()
:
select t.*
from (select t.*,
row_number() over (partition by id order by case status when 'R' then 1 when 'M' then 2 when 'O' then 3 when 'U' then 4 else 5 end
) as seqnum
from t
) t
where seqnum = 1;
如果你知道所有的状态,一个快捷方式是:
select t.*
from (select t.*,
row_number() over (partition by id order by charindex('RMOU', status)) as seqnum
from t
) t
where seqnum = 1;