Oracle subquery related content (includes top-n query and paged query)

This section describes the Oracle subquery: The data used by the instance is the EMP employee table under the Scott user in Oracle, dept the Department table with the following data:

One, sub-query 1, Concept: Another query embedded in a query, that is, one query as a condition of another query, this query is called a subquery. Then you can use a subquery in the location behind the select, from behind, where and after having. 2, Classification: (1) Single-line subquery: Query results only return one row of data (2) Multiline subquery: Query results return multiple rows of data, multi-row subquery operators have In,all,any, specific usage examples are described. 3. Example Description: Example1: Find the number of employees in each department:

Select deptno,dname, (selectfromwhere from Dept D;

Here the subquery is behind Select, which is the total number of employees in each department. Example2: Find employees who pay more than the department's average salary

Select  from emp E, (select deptno,avg. Fromgroupbywhere E.deptno= and e.sal>m.avgsal;

The subquery is located behind the from, which is the average wage for each department, which is considered a new table m, plus the query criteria. Example3: Find employee information for the same position as Scott

Select *  from where job=(selectfromwhere ename='SCOTT  ');

Here the subquery is in the Where condition and is the same position as the Scott employee. EXAMPLE4: The Department information of the department's highest salary is more than 30th.

Select deptno,avgfromgroupby hasavg(SAL)  >(selectmaxfromwhere deptno=);

Here the subquery is in the HAVING clause, which is the maximum wage for department number 30th. Example5: Inquiry department is the employee information of development department or sales department

Select *  from where inch (selectfromwhere dname='it'or dname='SALES');

The in operator of the multiline subquery is used here to get the department number of the search and sales department to limit a range. Example6: Get information about the wages of all employees in the department with a salary greater than 30th.

Select  from where sal>all(selectfromwhere deptno=);

The all operator in the multi-row subquery is used here to get all the payroll information for Department 30th, where all the main function is to get the maximum wage of department number 30th, which is greater than all the meaning is greater than the maximum. Example7: Get information about the wages of any employee in the department with a salary greater than 30th.

Select  from where sal>any(selectfromwhere deptno=);

This is used in the multi-row sub-query of the any operator, for the 30th Department of the payroll information, where any of the role and all different, mainly to take the minimum wage, any of which is greater than any one of these wages is more than the minimum wage can be. Second, Oracle Top-n query: concept: Used to get the first n records in a query, need to use the rownum pseudo-column to implement,rownum pseudo-columns, which are automatically generated by Oracle for each query, do not physically exist and often involve multiple tables in a query, but each query has only one column of pseudo-columns. Example: Find the top 5 employee information for the department number 20 and 30

Select *  from (Select*fromwherein  () bydescwhere rownum<=5;

Here in the from after a subquery, then there is a problem arises, why do not write here directly but to introduce a subquery, first look at the results of the non-add:

Select *  from where inch (rownum<=5orderby desc ;

It is obvious that 3000 is the highest wage, and what causes such a result? is becauseThe execution order of SELECT query statements in Oracle is the first where condition after order by ordering, that is to take 5 rows in the 5 rows to sort, and the correct order should be all 20, 30 Department of Staff wages first ranked in the row of 5 line three, the application of Oracle paging query: Concept: Paging query, as the name implies, control the scope of query results, get the part of the data we want. Example: Get the Employee table in 20, 30 departments in descending order of wages after the 4th page is 7th, 82 data

Select *  from(SelectRowNum ROWLINE,EMP1.*  from(Select *  fromEmpwhereDeptnoinch( -, -) Order  bySal) EMP1whereRowNum<=Ten) EMP2whereEmp2.rowline>=7  andEmp2.rowline<=8;

It might be a little complicated here, first of all, why not.

Select *  from (Select*fromwherein  () by descwhere rownum>=7 and rownum<= 8;

This query will never have data generation, for what reason, because when the inner query produces the first record, Oracle assigns its pseudo-column value rownum=1,

The outer query rownum>=7 and rownum<=8 does not conform to the conditional removal record, and when the second record is generated, Oracle still assigns its pseudo-column rownum=1,

The outer judgment still does not pass, so that no matter how much data the inner query produces, the data is lost because the condition of the outer query does not conform to the record.

To avoid this, you need to treat the pseudo-column as a field in a query, not as a pseudo-column, but as a real field.

This requires that the pseudo-columns be made into a physically existing field on the outside in a nested layer of queries, and finally we only need to change the condition of the outer query to the pseudo-column "real" in the inner query.

Sub-query related content summed up, there are unclear places please advice. 2018-08-13 15:51:41

Oracle subquery related content (includes top-n query and paged query)