根据多个字段的结果/ID计算最终结果
根据多个字段的结果/ID计算最终结果
提问于 2017-06-21 20:35:29
扩展此解决方案：Calculate Final outcome based on Results/ID
使用相同的业务逻辑，如何获得基于LA字段的另一个LA和基于Employment字段的Final Result？当涉及多个字段时，排序函数的工作方式明显不同。
表T1扩展如下：
+----------+-----------+-----------------+-----------------+
| PersonID | Date | Employment | LA |
+----------+-----------+-----------------+-----------------+
| 1 | 2/28/2017 | Stayed the same | Improved |
| 1 | 4/21/2017 | Stayed the same | Stayed the same |
| 1 | 5/18/2017 | Stayed the same | Improved |
| 2 | 3/7/2017 | Improved | Stayed the same |
| 2 | 4/1/2017 | Stayed the same | Stayed the same |
| 2 | 6/1/2017 | Stayed the same | Improved |
| 3 | 3/28/2016 | Improved | Improved |
| 3 | 5/4/2016 | Improved | Improved |
| 3 | 4/19/2017 | Worsened | Worsened |
| 4 | 5/19/2016 | Worsened | Stayed the same |
| 4 | 2/16/2017 | Improved | Stayed the same |
+----------+-----------+-----------------+-----------------+期望输出：
+----------+-----------------+-----------------+
| PersonID | Final Result | Final Result 2 |
+----------+-----------------+-----------------+
| 1 | Stayed the same | Improved |
| 2 | Improved | Improved |
| 3 | Worsened | Worsened |
| 4 | Improved | Stayed the same |
+----------+-----------------+-----------------+回答 1
Stack Overflow用户
回答已采纳
发布于 2017-06-21 20:53:39
添加另一个RN应该可以工作
select t1.personid, t1.employment, t2.LA
from (select t1.*,
row_number() over (partition by personid
order by (case when employment <> 'Stayed the same' then 1 else 2 end),
date desc
) as seqnum
from t1
) t1
left join
(select t1.PersonID, t1.LA,
row_number() over (partition by personid
order by (case when LA <> 'Stayed the same' then 1 else 2 end),
date desc
) as seqnum
from t1) t2 on t2.PersonID = t1.PersonID and t2.seqnum = 1
where t1.seqnum = 1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44685821
复制相关文章
相似问题
腾讯云开发者
