Oracle extracts the maximum and minimum values of multiple field Columns

To write an SQL statement today, You need to retrieve the maximum and minimum values of multiple field columns.
The originally thought method is quite troublesome. We need to extract max (one), max (two), and max (three) respectively and put them in pb for programming.
Later, my colleague Xia Lao helped me find a greatest function and least function. He only wrote greatest (max (one), max (two), and max (three) to solve the problem. least is used in the same way, good.

 

Evaluate the maximum value of multiple columns, greatest function in oracle
The following table TB data is known:

SQL> select * from tb;

ID CHINESE MATH ENGLISH
----------------------------------------
1001 89 98 87
1002 81 87 79

Now we need to get the following results. How can we solve this problem?

ID CHINESE MATH ENGLISH MAX MIN
------------------------------------------------------------
1001 89 98 87 98 87
1002 81 87 79 87 79

 

After thinking for a long time, I did not think of a good solution. First of all, I naturally thought of using the MAX and MIN functions. But obviously these two functions are clustering functions, which are used for a Group in the same column, the MAX and MIN values to be obtained apply to each row. If you want to use MAX () and MIN, the data structure of the original table needs to be processed (the row-to-column operation is not performed first), but it is obviously not very good.

I saw a netizen reply using the greatest and least functions. It was so concise and beautiful that I was sweating for my ignorance.

The solution is as follows:

SQL> SELECT id, chinese, math, english,
2 greatest (chinese, math, english) max,
3 least (chinese, math, english) min
4 FROM tb;

ID CHINESE MATH ENGLISH MAX MIN
------------------------------------------------------------
1001 89 98 87 98 87
1002 81 87 79 87 79

 

This article is from "mzhj"