Oracle SQL is still irreplaceable-"Mastering Oracle SQL"

Oracle cold, stay at home and read the "Mastering Oracle SQL" 2nd, found that Oracle's function is still very strong, there are 200 optical functions, hsql is difficult to match.       The next stubborn, it seems either to risk using Hibernate3.0 SQL mapping function, or to run the JDBC assembly VO. 1. Report Totals dedicated Rollup function
Sales Report
Guangzhou January 2000 Yuan
Guangzhou February 2500 yuan
Guangzhou 4500 Yuan
Shenzhen January 1000 yuan
Shenzhen February 2000 Yuan
Shenzhen 3000 Yuan
All Areas 7500 RMB

Previous query sql:
Select area,month,sum from Saleorder GROUP by Area,month
Then the total of Guangzhou, Shenzhen and all regions need to accumulate in the program.
1. The following SQL can actually be used:
Select Area,month,sum (Total_sale) from Saleorder GROUP by rollup (Area,month)
Can produce a record that is exactly the same as the report 2. If year does not want to accumulate, you can write
Select Year,month,area,sum (Total_sale) from Saleorder Group by year, Rollup (Month,area)
In addition, Oracle 9i also supports the following syntax:
Select Year,month,area,sum (Total_sale) from Saleorder Group by Rollup ((Year,month), area)
3. If you use Cube (area,month) instead of Rollup (area,month), you will get totals for each month in addition to the totals for each region, shown at the end of the report. 4.Grouping make the Totals column read better
Rollup the month column is null when the Guangzhou total is displayed, but a better practice should be to show "all months"
Grouping is used to determine whether the current column is an aggregate column, 1 is yes, and then use decode to convert it to "all months"
Select Decode (Grouping (area), 1, ' all regions ', areas),
Decode (Grouping (month), 1, ' All month ', month),
SUM (Money)
From Saleorder
Group by RollUp (Area,month);
2. The start With.....connect for multilevel level queries by
such as personnel organization, product category, Oracle provides a very classical approach
SELECT level, name, emp_id,manager_emp_id
From employee
START with manager_emp_id is null
CONNECT by PRIOR emp_id = manager_emp_id;
The above statement shows all the applications, start with indicates where to start traversing the tree, and if starting from the root, then its manager should be null, if you start with an employee, you can write emp_id= ' 11 '
CONNECT by IS to indicate the parent-child relationship, note prior position
There is also a level column that shows the hierarchy of nodes 3. More Report/Analysis decision functions
3.1 Basic structure of the analysis function
Analysis function () over (partion clause, ORDER BY clause, window clause)
It is difficult to explain conceptually, or to use examples to speak better. 3.2 Row_number and Rank, Dense_rank
To select a report such as top 3 sales
When two salespeople may have the same performance, use Rank and Dense_rank
Like what
Amount RowNum Rank Dense_rank
Zhang 34,000 Yuan 1 1 1
Li 43,000 Yuan 2 2 2
Money 52,000 Yuan 3 3 3
Sun 62,000 $4 3 3
Ding 71,000 Yuan 5 5 4 at this time, should be tied to the third of the money five and Sun Liu are selected, so the ranking function than RowNumber insurance. As for desnse or ranking, see the specific situation.
SELECT salesperson_id, SUM (tot_sales) Sp_sales,
RANK () over (order by SUM (tot_sales) DESC) Sales_rank
From Orders
GROUP by salesperson_id
3.3 Ntile The record flat into a B, C, D, etc
For example, I want to get the top 25% record, or treat 25% of the record as equal to one level and treat another 25% equally as another level.
SELECT Cust_nbr, SUM (tot_sales) Cust_sales,
Ntile (4) over (order by SUM (tot_sales) DESC) sales_quartile
From Orders
GROUP by CUST_NBR
Order by 3,2 DESC;
Ntitle (4) divided the records into 4 parts in SUM (Tot_sales). 3.4 Secondary analysis columns and Windows Function
The report, in addition to the basic factual data, always hope that the next more annual total sales, so far the cumulative sales, three months before and after the average sales such columns reference.
This three-month average and cumulative sales to date are called Windows function, see the following example
SELECT month, SUM (tot_sales) Monthly_sales,
SUM (SUM (tot_sales)) over (order by month
ROWS BETWEEN unbounded preceding and current ROW) max_preceeding
From Orders
GROUP by month
Order BY month;





SELECT month, SUM (tot_sales) Monthly_sales,
AVG (SUM (Tot_sales)) over (order by month
ROWS BETWEEN 1 preceding and 1 following) Rolling_avg
From Orders
GROUP by month
Order BY month;
The key to Windows function is a few values for Windows clauses
A record before the 1 preceding
A record after the 1 following
All records prior to unbounded preceding
Current ROW currently record 4.SubQuery summary
Subquery used every day, theoretically summed up. Subquery is divided into three kinds
1.Noncorrelated subquery the most common style.
2.Correlated subqueries The parent query column to the subquery inside, the first time Cyt taught me to understand a half-day.
3.Inline View is also used as the most common style. Then the noncorrelated subquery has three different cases.
1. Returns a row where Price < (select Max (price) from goods)
2. Returns a list of multiple rows where Price>= all (select price from goods where type=2)
or where not price< any (select price from goods where type=2)
The most commonly used in fact is =any ()
3. Return multiple rows at a time to return multiple columns of course, save the query time
UPDATE monthly_orders
SET (tot_orders, Max_order_amt) =
(SELECT COUNT (*), MAX (Sale_price)
From Cust_order)


DELETE from Line_item
WHERE (ORDER_NBR, PART_NBR) in
(SELECT Order_nbr, PART_NBR from Cust_order c)