我使用以下代码从我的国家1-5检索最高值，但当有多个国家具有相同值时，代码会显示为NULL。我如何选择a(国家1-5的最高值和B(如果某些国家具有相同的值，则选择具有该值的第一个国家(从1开始(。这是我使用的代码：
, Risk AS(
SELECT *,
(SELECT MAX(v)
FROM (
VALUES (Redflag.[country1]),
(Redflag.[country2]),
(Redflag.[country3]),
(Redflag.[country4]),
(Redflag.[country5])) AS value(v)
) AS highest_risk
FROM Redflag
)
这就是我得到的：
SELECT 1 AS ID, 0.5 AS country1, 0 AS country2, 0.5 AS country3, 0 AS country4, 0 AS country5, NULL AS highest_risk
UNION
SELECT 2 AS ID, 0.5 AS country1, 0.5 AS country2, 2 AS country3, 0 AS country4, 0 AS country5, 2 AS highest_risk
UNION
SELECT 3 AS ID, 0 AS country1, 2 AS country2, 0.5 AS country3, 0.5 AS country4, 0.5 AS country5, 2 AS highest_risk
UNION
SELECT 4 AS ID, 0 AS country1, 0 AS country2, 0 AS country3, 0 AS country4, 5 AS country5, 5 AS highest_risk
UNION
SELECT 5 AS ID, 0.5 AS country1, 0.5 AS country2, 3 AS country3, 3 AS country4, 0.5 AS country5, NULL AS highest_risk
UNION
SELECT 6 AS ID,0 AS country1, 0 AS country2, 0 AS country3, 0.5 AS country4, 0.5 AS country5, NULL AS highest_risk
UNION
SELECT 7 AS ID, 0 AS country1, 0.5 AS country2, 0.5 AS country3, 0.5 AS country4, 0.5 AS country5, 0.5 AS highest_risk
UNION
SELECT 8 AS ID, 0.5 AS country1, 0.5 AS country2, 0.5 AS country3, 0.5 AS country4, 0.5 AS country5, NULL AS highest_risk
UNION
SELECT 9 AS ID, 0.5 AS country1, 0.5 AS country2, 0.5 AS country3, 0.5 AS country4, 4 AS country5, 4 AS highest_risk
我需要这样：
SELECT 1 AS ID, 0.5 AS country1, 0 AS country2, 0.5 AS country3, 0 AS country4, 0 AS country5, 0.5 AS highest_risk
UNION
SELECT 2 AS ID, 0.5 AS country1, 0.5 AS country2, 2 AS country3, 0 AS country4, 0 AS country5, 2 AS highest_risk
UNION
SELECT 3 AS ID, 0 AS country1, 2 AS country2, 0.5 AS country3, 0.5 AS country4, 0.5 AS country5, 2 AS highest_risk
UNION
SELECT 4 AS ID, 0 AS country1, 0 AS country2, 0 AS country3, 0 AS country4, 5 AS country5, 5 AS highest_risk
UNION
SELECT 5 AS ID, 0.5 AS country1, 0.5 AS country2, 3 AS country3, 3 AS country4, 0.5 AS country5, 3 AS highest_risk
UNION
SELECT 6 AS ID,0 AS country1, 0 AS country2, 0 AS country3, 0.5 AS country4, 0.5 AS country5, 0.5 AS highest_risk
UNION
SELECT 7 AS ID, 0 AS country1, 0.5 AS country2, 0.5 AS country3, 0.5 AS country4, 0.5 AS country5, 0.5 AS highest_risk
UNION
SELECT 8 AS ID, 0.5 AS country1, 0.5 AS country2, 0.5 AS country3, 0.5 AS country4, 0.5 AS country5, 0.5 AS highest_risk
UNION
SELECT 9 AS ID, 0.5 AS country1, 0.5 AS country2, 0.5 AS country3, 0.5 AS country4, 4 AS country5, 4 AS highest_risk
最简单的方法是什么？
类似的内容。"max_risk"列包含a(国家/地区1-5的最高值。"min_country"列包含b(如果某些国家/地区具有相同的值，则它会选择具有该值的第一个国家/地区(从1开始(。
数据
drop table if exists #redflag;
go
with data as (
SELECT 1 AS ID, 0.5 AS country1, 0 AS country2, 0.5 AS country3, 0 AS country4, 0 AS country5, NULL AS highest_risk
UNION
SELECT 2 AS ID, 0.5 AS country1, 0.5 AS country2, 2 AS country3, 0 AS country4, 0 AS country5, 2 AS highest_risk
UNION
SELECT 3 AS ID, 0 AS country1, 2 AS country2, 0.5 AS country3, 0.5 AS country4, 0.5 AS country5, 2 AS highest_risk
UNION
SELECT 4 AS ID, 0 AS country1, 0 AS country2, 0 AS country3, 0 AS country4, 5 AS country5, 5 AS highest_risk
UNION
SELECT 5 AS ID, 0.5 AS country1, 0.5 AS country2, 3 AS country3, 3 AS country4, 0.5 AS country5, NULL AS highest_risk
UNION
SELECT 6 AS ID,0 AS country1, 0 AS country2, 0 AS country3, 0.5 AS country4, 0.5 AS country5, NULL AS highest_risk
UNION
SELECT 7 AS ID, 0 AS country1, 0.5 AS country2, 0.5 AS country3, 0.5 AS country4, 0.5 AS country5, 0.5 AS highest_risk
UNION
SELECT 8 AS ID, 0.5 AS country1, 0.5 AS country2, 0.5 AS country3, 0.5 AS country4, 0.5 AS country5, NULL AS highest_risk
UNION
SELECT 9 AS ID, 0.5 AS country1, 0.5 AS country2, 0.5 AS country3, 0.5 AS country4, 4 AS country5, 4 AS highest_risk
)
select id, country1, country2, country3, country4, country5
into #redflag
from data;
查询
with country_cte(id, country1, country2, country3, country4, country5, v_n, v_v, rn) as (
select *, row_number() over (partition by id order by v.v desc, v.n) rn
from
#redflag r
cross apply
(values (1, r.[country1]), (2, r.[country2]),
(3, r.[country3]), (4, r.[country4]),
(5, r.[country5])) AS v(n, v))
select id, country1, country2, country3, country4, country5, v_n min_country, v_v max_risk
from country_cte
where rn=1;
输出
id country1 country2 country3 country4 country5 min_country max_risk
1 0.5 0.0 0.5 0.0 0.0 1 0.5
2 0.5 0.5 2.0 0.0 0.0 3 2.0
3 0.0 2.0 0.5 0.5 0.5 2 2.0
4 0.0 0.0 0.0 0.0 5.0 5 5.0
5 0.5 0.5 3.0 3.0 0.5 3 3.0
6 0.0 0.0 0.0 0.5 0.5 4 0.5
7 0.0 0.5 0.5 0.5 0.5 2 0.5
8 0.5 0.5 0.5 0.5 0.5 1 0.5
9 0.5 0.5 0.5 0.5 4.0 5 4.0