SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE TABLE Employees ( Age, Qualification, Income ) AS
SELECT 19, 'Grad', 5000 FROM DUAL
UNION ALL SELECT 19, 'Grad', 4000 FROM DUAL
UNION ALL SELECT 19, 'Grad', 3000 FROM DUAL
UNION ALL SELECT 26, 'Grad', 6000 FROM DUAL
UNION ALL SELECT 26, 'Grad', 5000 FROM DUAL
UNION ALL SELECT 26, 'PosG', 8000 FROM DUAL
UNION ALL SELECT 26, 'PosG', 6000 FROM DUAL;
查询1:
SELECT Age,
Qualification,
Income,
RANK() OVER ( PARTITION BY Age, Qualification ORDER BY Income DESC ) AS "Rank"
FROM Employees
Results:
| AGE | QUALIFICATION | INCOME | Rank |
|-----|---------------|--------|------|
| 19 | Grad | 5000 | 1 |
| 19 | Grad | 4000 | 2 |
| 19 | Grad | 3000 | 3 |
| 26 | Grad | 6000 | 1 |
| 26 | Grad | 5000 | 2 |
| 26 | PosG | 8000 | 1 |
| 26 | PosG | 6000 | 2 |
查询2:
WITH total_incomes AS (
SELECT Age,
Qualification,
SUM( Income ) AS total_income
FROM Employees
GROUP BY
Age,
Qualification
)
SELECT Age,
Qualification,
total_income,
RANK() OVER ( ORDER BY total_income DESC ) AS "Rank"
FROM total_incomes
Results:
| AGE | QUALIFICATION | TOTAL_INCOME | Rank |
|
| 26 | PosG | 14000 | 1 |
| 19 | Grad | 12000 | 2 |
| 26 | Grad | 11000 | 3 |