SELECT id, chinese, math, english,
greatest(chinese, math, english) max,
least(chinese, math, english) min
FROM tb; ID CHINESE MATH ENGLISH MAX MIN
- -------- ---------- ---------- ---------- ---------- ----------
1001 89 98 87 98 87
1002 81 87 79 87 79
如上 ,求出了最大值是 98 也就是 ,数学(math),但是我只能求出98,怎么知道他是数学呢?
望各位赐教。谢谢 ,如果不能解决 ,能提供比较好的在程序中解决的方案也行再次谢谢大家
greatest(chinese, math, english) max,
least(chinese, math, english) min
FROM tb; ID CHINESE MATH ENGLISH MAX MIN
- -------- ---------- ---------- ---------- ---------- ----------
1001 89 98 87 98 87
1002 81 87 79 87 79
如上 ,求出了最大值是 98 也就是 ,数学(math),但是我只能求出98,怎么知道他是数学呢?
望各位赐教。谢谢 ,如果不能解决 ,能提供比较好的在程序中解决的方案也行再次谢谢大家
然后看到MAX MIN 为 98 87 就知道对应的分别是数学和英语了啊!
( ID VARCHAR2(10),
CHINESE NUMBER(8,2),
MATH NUMBER(8,2),
ENGLISH NUMBER(8,2)
);
--采用decode即可
select decode(greatest(chinese,math,english),chinese,'chinese',math ,'math',english,'english') as greatestclass
,greatest(chinese,math,english) as greatest
from t_score ;
SQL> select * from t_score;ID CHINESE MATH ENGLISH
---------- ---------- ---------- ----------
1 78.00 65.00 0.10SQL> select decode(greatest(chinese, math, english),
2 chinese,
3 'chinese',
4 math,
5 'math',
6 english,
7 'english') as greatestclass,
8 greatest(chinese, math, english) as greatest
9 from t_score;GREATESTCLASS GREATEST
------------- ----------
chinese 78
case when greatest(chinese,math,english) = chinese then 'chinese' else null end||
case when greatest(chinese,math,english) = math then 'math' else null end||
case when greatest(chinese,math,english) = english then 'english' else null end
from tb;