如何在一行多列中找到具有最大值的列

Question

我有一个临时表，如下所示：

|----------|------------|-------------|--------|-----------|
| Country  | Confirmed  | Unconfirmed | Deaths | Recovered |
|----------|------------|-------------|--------|-----------|
|     A    |   95782    |   1034219   |  6723  |   25892   |
|----------|------------|-------------|--------|-----------|

我需要找到具有最大值的column_name(在给定的四列中(。比方说，我需要找出确诊病例、未确诊病例、死亡病例或康复病例是否更适合A国。

在这种情况下，预期输出是"未确认的"，因为它在四个给定列中具有最大值。

Answer 1

使用最伟大的

SELECT Country, 'unconfirmed', GREATEST(Confirmed, Unconfirmed, Deaths, Recovered)
FROM TAble1;

更多内容

您的完整查询看起来像这个

SELECT
t1.country
, ( case 
WHEN t1.Confirmed = t2.maxnumber Then 'Confirmed'
WHEN t1.Unconfirmed = t2.maxnumber Then 'Unconfirmed'
WHEN t1.Deaths = t2.maxnumber Then 'Deaths'
WHEN t1.Recovered = t2.maxnumber Then 'Recovered'
ELSE 'unknown'
END) type
,t2.maxnumber
FROM table1 t1 inner join
(SELECT 
Country
,GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) maxnumber
FROM table1) t2 ON t1.country = t2.country

示例

架构(MySQL v5.7(

CREATE TABLE table1 (
`Country` VARCHAR(1),
`Confirmed` INTEGER,
`Unconfirmed` INTEGER,
`Deaths` INTEGER,
`Recovered` INTEGER
);
INSERT INTO table1
(`Country`, `Confirmed`, `Unconfirmed`, `Deaths`, `Recovered`)
VALUES
('A', '95782', '1034219', '6723', '25892'),
('B', '95782', '1034219', '6723', '2225892');

---

查询#1

SELECT
t1.country
, ( case 
WHEN t1.Confirmed = GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) Then 'Confirmed'
WHEN t1.Unconfirmed = GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) Then 'Unconfirmed'
WHEN t1.Deaths = GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) Then 'Deaths'
WHEN t1.Recovered = GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) Then 'Recovered'
ELSE 'unknown'
END) type
,GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered )
FROM table1 t1;
| country | type        | GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) |
| ------- | ----------- | -------------------------------------------------------- |
| A       | Unconfirmed | 1034219                                                  |
| B       | Recovered   | 2225892                                                  |

---

查看DB Fiddle

Answer 2

一个选项是取消透视并使用row_number()来识别每个国家/地区病例最多的列：

select country, case_type, nb_cases
from (
select 
t.*, row_number() over(partition by country order by nb_cases desc) rn
from (
select country, 'confirmed' case_type, confirmed nb_cases from mytable
union all
select country, 'unconfirmed', unconfirmed from mytable
union all
select country, 'deaths', deaths from mytable
union all
select country, 'recovered', recovered from mytable
) t
) t
where rn = 1

DB Fiddle上的演示：

| country | case_type   | nb_cases |
| ------- | ----------- | -------- |
| A       | unconfirmed | 1034219  |

Answer 3

您可以使用greatest()和case:

select t.*,
(case greatest(Confirmed, Unconfirmed, Deaths, Recovered)
when Confirmed then 'Confirmed'
when Unconfirmed then 'Unconfirmed'
when Deaths then 'Deaths'
when Recovered then 'Recovered'
end) as column_greatest
from t;

两个音符。

首先，如果任何列都是NULL，则此操作不起作用。您没有样本表明情况确实如此。但考虑到这些值从不为负数，如果可能存在NULL值，则可以使用COALESCE()。

其次，如果存在平局，则返回具有最大值的第一列。再说一遍，你没有具体说明该怎么做，所以这似乎是对这个问题的合理解释。