在MySQL 8.x中，一个选项是 unpivot（将列转换为行） 数据，然后使用row_number函数获取每列具有最大值的行。

with t as
(
  select t.date_, u.val, u.colunax,
     row_number() over (partition by u.colunax order by u.val desc) rn
  from tbl t
  cross join lateral (
    select coluna1, 'coluna1' union all
    select coluna2, 'coluna2' union all
    select coluna4, 'coluna4' union all
    select coluna5, 'coluna5'
  ) as u(val, colunax)
)
select date_, colunax, val
from t
where rn = 1

如果您的版本不支持lateral derived tables，您可以使用union all来取消数据透视。

with t as
(
  select date_, coluna1 val, 'coluna1' colunax from tbl union all
  select date_, coluna2, 'coluna2' from tbl union all
  select date_, coluna4, 'coluna4' from tbl union all
  select date_, coluna5, 'coluna5' from tbl
),
t2 as
(
 select date_, val, colunax,
     row_number() over (partition by colunax order by val desc) rn
  from t
)
select date_, colunax, val
from t2
where rn = 1

demo