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;
