多列求最大值列和列名

select substr(decode(t.n_intension1_avg,greatest(t.n_intension1_avg,
t.n_intension2_avg,
t.n_intension3_avg,
t.n_intension4_avg,
t.n_intension5_avg),'白棉1级'||t.n_intension1_avg) ||
decode(t.n_intension2_avg,greatest(t.n_intension1_avg,
t.n_intension2_avg,
t.n_intension3_avg,
t.n_intension4_avg,
t.n_intension5_avg),'白棉2级'||t.n_intension2_avg) ||
decode(t.n_intension3_avg,greatest(t.n_intension1_avg,
t.n_intension2_avg,
t.n_intension3_avg,
t.n_intension4_avg,
t.n_intension5_avg),'白棉3级'||t.n_intension3_avg) ||
decode(t.n_intension4_avg,greatest(t.n_intension1_avg,
t.n_intension2_avg,
t.n_intension3_avg,
t.n_intension4_avg,
t.n_intension5_avg),'白棉4级'||t.n_intension4_avg) ||
decode(t.n_intension5_avg,greatest(t.n_intension1_avg,
t.n_intension2_avg,
t.n_intension3_avg,
t.n_intension4_avg,
t.n_intension5_avg),'白棉5级'||t.n_intension5_avg),1,4) 颜色级,
substr(decode(t.n_intension1_avg,greatest(t.n_intension1_avg,
t.n_intension2_avg,
t.n_intension3_avg,
t.n_intension4_avg,
t.n_intension5_avg),'白棉1级'||t.n_intension1_avg) ||
decode(t.n_intension2_avg,greatest(t.n_intension1_avg,
t.n_intension2_avg,
t.n_intension3_avg,
t.n_intension4_avg,
t.n_intension5_avg),'白棉2级'||t.n_intension2_avg) ||
decode(t.n_intension3_avg,greatest(t.n_intension1_avg,
t.n_intension2_avg,
t.n_intension3_avg,
t.n_intension4_avg,
t.n_intension5_avg),'白棉3级'||t.n_intension3_avg) ||
decode(t.n_intension4_avg,greatest(t.n_intension1_avg,
t.n_intension2_avg,
t.n_intension3_avg,
t.n_intension4_avg,
t.n_intension5_avg),'白棉4级'||t.n_intension4_avg) ||
decode(t.n_intension5_avg,greatest(t.n_intension1_avg,
t.n_intension2_avg,
t.n_intension3_avg,
t.n_intension4_avg,
t.n_intension5_avg),'白棉5级'||t.n_intension5_avg),5,7) 比例
from b_batch_avg t
where t.c_factorybatchno = '66209161001'
 
posted @ 2017-06-27 14:41  披着凉皮的狼  阅读(337)  评论(0)  编辑  收藏  举报