How to rewrite max/min function in Database

This article mainly introduces how to rewrite the max/min function in the database, the article is very detailed, has a certain reference value, interested friends must read it!

1.OLD:

SELECT a.deptno, a.min_no, mi.ename AS min_n, ma.empno AS max_n FROM (SELECT deptno, MIN (empno) AS min_no, MAX (empno) AS max_no FROM emp GROUP BY deptno) an INNER JOIN emp mi ON (mi.deptno = a.deptno AND mi.empno = a.min_no) INNER JOIN emp ma ON (ma.deptno = a.deptno AND ma.empno = a.max_no)

two。 Direction of optimization:

Sql requirements display the largest employee number and name, the minimum employee number and name in each department

Group by cannot display both aggregate results and source data, so it needs to be done through self-connection.

Using the analysis function, the metadata and aggregated data are displayed in the same row, removing the self-join.

Select deptno, min (empno) min_no, min (ename) keep (dense_rank first order by empno) min_n, max (empno) max_no, min (ename) keep (dense_rank last order by empno) max_nfrom empgroup by deptno; are all the contents of this article "how to rewrite max/min functions in the database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!