根据 sql 服务器中的优先级选取列值

Question

我有一个表格如下

ID  COL2        COL3    date
1   CLINICAL    red     30-Aug
1   HOUSE CALLS green   27-Oct
1   PREDICTIVE  blue    29-Oct
1   CLINICAL    green   30-oct
2   PREDICTIVE  green   20-Jan
2   CLINICAL    blue    21-Jan
2   HOUSE CALLS red     20-Feb
2   PREDICTIVE  green   28-Feb
3   HOUSE CALLS red     30-Apr
3   PREDICTIVE  green   12-May
4   CLINICAL    blue    14-May

我必须创建一个额外的列 COL4，它将根据每个 ID 的 COL2 中的值从 COL3 中选取值。 COL2 只能有 3 个值

临床(第一优先(预测(第二优先(上门拜访(第三优先(

输出应如下所示：

ID  COL2        COL3    date    COl4
1   CLINICAL    red     30-Aug  red
1   HOUSE CALLS green   27-Oct  red
1   PREDICTIVE  blue    29-Oct  red
1   CLINICAL    green   30-oct  green
2   PREDICTIVE  green   20-Jan  green
2   CLINICAL    blue    21-Jan  blue
2   HOUSE CALLS red     20-Feb  blue
2   PREDICTIVE  green   28-Feb  blue
3   HOUSE CALLS red     30-Apr  red
3   PREDICTIVE  green   12-May  green
4   CLINICAL    blue    14-May  blue

例如，让我们取 ID '1'。在第一个表中，col2 值在 30-8 月 30 日为临床，并且它也具有最高优先级，因此 ID 1 的 col4 值将保持不变，直到 29 日 29 月 30 日临床再次出现，因此值应更改为绿色。让我们为 col2 的第一个值取 ID '2' 是预测性的，因此 col4 的值将是其相应的 col3 值。但之后在 21 日，col2 值更改为临床，这是最高优先级的，因此 col4 值将更改为蓝色。与基于 col2 中存在的值的优先级的其他 ID 相同。

这是我目前正在使用的查询，但是当 id 1 再次出现另一个 col3 值时，它仍然选择较旧的查询 即红色，但它应该为第 4 行选择绿色

COALESCE(MAX(CASE WHEN [col2] = 'CLINICAL' THEN col3 END) OVER (PARTITION BY ID ORDER BY date ),
MAX(CASE WHEN [col2] = 'PREDICTIVE MODEL' THEN col3 END) OVER (PARTITION BY ID ORDER BY date),
MAX(CASE WHEN [col2] = 'HOUSECALLS' THEN col3 END) OVER (PARTITION BY ID ORDER BY date )
) as BANNER_RISK_LEVEL

Answer 1

选中下面的选项。

在这里演示

WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY ID,CAST(Date+'-2019' AS DATE)) RN
FROM your_table
)
SELECT ID,COL2,COL3,date,
(
SELECT TOP 1 COL3 
FROM CTE B 
WHERE B.RN <= A.RN  
AND B.ID = A.ID 
ORDER BY (CASE WHEN B.COL2 = 'CLINICAL' THEN 1 ELSE 2 END), 
B.RN DESC
) Col4
FROM CTE A

Answer 2

这可能是最简单的使用apply：

select t.*, tt.col3              
from t cross apply
(select top (1) t2.*
from t t2
where t2.id = t.id
order by (case when col2 = 'CLINICAL' then 1
when col2 = 'PREDICTIVE' then 2
else 3
end),
date desc
) tt;