sql 取出一行中最大值对应的字段名

原始数据

目标结果

实现思路

先把最大品类的字段添加到最后,作为值出现(sql中不能直接拿到字段名)
在查询最大值对应的字段名

添加最大值对应的字段名到值中

select
shop,
month,
dz,
fz,
sp,
case
   when dz>fz and dz>sp then 'dz'
   when fz>dz and fz>sp then 'fz'
   when sp>dz and sp>fz then 'sp'
  else null
end as `品类`
from
tablename;

查询最大值对应的字段名

with tmp as (
select
shop,
month,
dz,
fz,
sp,
case
   when dz>fz and dz>sp then 'dz'
   when fz>dz and fz>sp then 'fz'
   when sp>dz and sp>fz then 'sp'
  else null
end as `品类`
from
tablename)
select
shop,
month,
`品类`,
greatest(dz,fz,sp) as max
from
tmp;

简单写法

select
shop,
case 
   when dz>fz and dz>sp then 'dz'
   when fz>dz and fz>sp then 'fz'
   when sp>dz and sp>fz then 'sp'
   else null
end as `品类`, 
greatest(dz,fz,sp) as `最大金额`
from
tablename;