oracle-Query,,..

Subquery: A subquery When a query statement, he is nested in other SQL statements, the purpose is to provide data for the outer SQL statements, sub-queries can be used in DDL,DML,DQL.
Using subqueries in DDL, you can quickly create a query-to-sum result set as a single table
Sub-query According to the conditions are divided into: single-row sub-query, multi-line single subquery, multi-row multi-column subquery in which a single row subquery is commonly used in the filtering conditions, and multi-column subquery is often used as a table for a multi-row single subquery, in the filter is judged to be used with the in
exists keyword used in the filter condition, followed by a subquery, as long as the subquery can query a record, then exists is considered to meet the conditions
Paged query: Paging query is a query query of the data in batches of the query out, the advantage is that when a query can query the results of the factory large, effectively reduce the amount of data transmitted by the network, improve response speed, reduce system overhead.
ROWNUM ROWNUM a pseudo-column that does not actually exist in any table, but each table can query the field, the value of which is the result of the query, which is the line number of each record in. The value of the field is generated dynamically during the accompanying query as long as a record can be queried from the table, the value of the field is the row number of the record in the current result set, and the line number increments from 2.
In the process of using the RowNum field to line up the result set, do not use rownum to make the >1 above as a filter, otherwise you will not get any results.
Pagtsize: Pages to display page of pages to show start: (page-1) *pagesize+1 end:pagesize*page
The sort function can group the results according to the specified fields, and the groups are sorted within the specified fields, and then the number in the group is row_number to generate consecutive and unique numbers within the group.
--Advanced grouping
--rollup
--group by ROLLUP (a,b,c)
--equivalent to
--group by A,b,c
--union All
--group by A, b
--union All
--group by A
--union All
SELECT * from Sales_tab_zyl
SELECT Year_id,month_id,day_id,sum (sales_values) from Sales_tab_zyl GROUP by ROLLUP (year_id,month_id,day_id) ORDER by year_id,month_id,day_id
Each combination of the--cube parameter is grouped once
SELECT Year_id,month_id,day_id,sum (sales_values) from Sales_tab_zyl GROUP by CUBE (year_id,month_id,day_id) ORDER by year_id,month_id,day_id
--grouping sets: You can customize the grouping method, each parameter is a grouping, and then the results of these grouped statistics are displayed in a result set.
SELECT Year_id,month_id,day_id,sum (sales_values) from Sales_tab_zyl GROUP by GROUPING sets ((year_id,month_id,day_id)) ORDER by year_id,month_id,day_id

oracle-Query,,..