Collation of SQL statements with Oracle Characteristics

Oracle-specific SQL statement sorting. We usually want to select the original column and the statistical value column as many as possible during statistical analysis. However, in this way, the groupby must follow more

Sort SQL statements with Oracle characteristics. We usually want to select the original column and the statistical value column as many as possible during statistical analysis. However, in this way, the group by clause must follow more

We know that every RDBMS has its own characteristics in SQL. So today, let's take a look at the characteristics of Oracle, which deserves our attention.

Feature 1:
Oracle analysis functions and window functions:
Syntax:

FUNCTION_NAME (,...)
OVER ( )
For example:
Sum (sal) over (partition by deptno order by ename rows ...)
Where sum is the function name,
Over () is a keyword. The direct point is to add conditions to the analysis function to identify whether sum () is an aggregate function or an analysis function.
Note:
(1) During statistical analysis, we usually want to select as many original columns and statistical value columns as possible, but in this way, the group by column must follow more columns, using the analysis function can avoid the problem that the selected column name must appear in the group by list.

(2) The aggregate function uses group by, and each group returns a statistical value. The analysis function uses partition by group, and each group returns a statistical value per line.

(3) The analysis function contains an over () Window Function and three analysis words:
Order by window (rows)

(4) differences between two order by statements:

The analysis function is performed after the entire SQL query (the execution of the SQL statement is special). That is to say, the order by statement of the SQL statement also affects the execution result of the analysis function.

A) if the order by statement in the SQL statement meets the sorting requirements of the analysis function, the sorting of the SQL statement will be executed first, and the analysis function does not have to be sorted during analysis.

B) if the order by statement in the SQL statement does not meet the sorting required by the analysis function, the order in the SQL statement will be executed first.

(5) The window is the data range to be processed during function analysis:

The first line is: unbounded preceding

The current row is: current row

The last line is unbounded following.

Window words cannot appear separately. They can only appear when order by clause is available. The order by clause does not have to have a window clause. In this case, the window is from the first row to the last row by default;

When the window clause is omitted:

A) if order by exists, the default window is unbounded preceding.
And current row;

B) if order by is omitted at the same time, the default window is unbounded preceding and unbounded.

Following

Example:

1. Which of the following is the highest wage for each department?

Select * from

(

Select ename, sal, deptno, rank () over (partition by deptno order by sal desc) mm from emp

)

Where mm = 1

Note:
1). row_number () cannot be used when you calculate the first place, because if there are two equal places in the same class,

Row_number () returns only one result.
2) The difference between rank () and dense_rank () is:
-- Rank () is the Skip sorting. When there are two second names, the next is the fourth name.
-- Dense_rank () l is a continuous sorting, with two second names still followed by the third

2. display the salaries of employees in each department with the highest salaries of the Department.

Select deptno, empno, ename, sal, last_value (sal) over (partition by deptno order by sal rows

Between unbounded preceding and unbounded following)

Max_sal from emp;