Seeing a man's question today,
Probably as follows:
Check the userid number of the department's minimum wage
Table structure:
Payroll Department D,
UserID Salary Dept
1 2000 1
2 1000 1
3 500 2
4 1000 2
There is an expert who gives an answer:
SELECT MIN (Salary) over (PARTITION by dept) Salary, Dept
from SS
After running, get:
1000 1
1000 1
500 2
500 2
The landlord of the man looked at the feeling very advanced. Big sigh is very high-skilled AH ~
I also think this man is really tall.
But I'll look into it and find out that the man is not very understanding of partition by's usage. does not solve the landlord problem.
Let's see my revised statement.
SELECT Userid,salary,dept,min (Salary) over (PARTITION by dept) Salary
from SS
Results after the run:
UserID Salary Dept MIN (Salary) over (PARTITION by dept)
1 2000 1 1000
2 1000 1 1000
3 500 2 500
4 1000 2 500
You see the clues.
The advanced is not necessarily suitable.
Here is the answer I gave:
SELECT * from SS
INNER JOIN (SELECT MIN (SALARY) as SALARY, DEPT from SS GROUP by DEPT) SS2
USING (salary,dept)
Results after the run:
Salary Dept UserID
1000 1 2
500 2 3
So I think I'll summarize the usage of group by and partition by.
Group BY is a simple grouping of the retained rows of the search results, with the general total love and aggregation functions used such as AVG (), COUNT (), Max (), Main (), and so on.
Partition by, although it also has a grouping function, but also has other functions.
It belongs to the analytic function of Oracle.
Borrow a diligent person's data to illustrate:
SUM () over (PARTITION by ...) is an analytic function. The effect he performs with the ordinary sum ... GROUP by ... Instead, it calculates the accumulation of expressions in the group and not the simple and.
Table A, the contents are as follows:
B C D
02 02 1
02 03 2
02 04 3
02 05 4
02 01 5
02 06 6
02 07 7
02 03 5
02 02 12
02 01 2
02 01 23
Select B,c,sum (d) E from a group by b,c
Get:
B C E
02 01 30
02 02 13
02 03 7
02 04 3
02 05 4
02 06 6
02 07 7
The result of using the analysis function is:
SELECT B, C, D, SUM (d) over (PARTITION by B,c ORDER by D) E from a
B C E
02 01 2
02 01 7
02 01 30
02 02 1
02 02 13
02 03 2
02 03 7
02 04 3
02 05 4
02 06 6
02 07 7
The result is not the same, so it is not clear, we have to show the content of D is more clear:
SELECT B, C, D,sum (d) over (PARTITION by B,c ORDER by D) E from a
B C D E
2 2 d=2,sum (d) =2
5 7 d=5,sum (d) =7
D=23,sum (d) =30
Geneva 1 1 C value is different, re-accumulate
02 02 12 13
02 03 2 2
02 03 5 7
02 04 3 3
02 05 4 4
02 06 6 6
02 07 7 7
The difference between over partition by and group by
