SQL ranking function instance and SQL ranking function instance
In actual development, it is often possible to calculate the ranking of a field.
Table: totak_sales
Now, this requires that you add a sales_rank field to display the ranking order in reverse order of sales.
Method:
Copy codeThe Code is as follows: SELECT a1.Name, a1.Sales, COUNT (a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales = a2.Sales and a1.Name = a2.Name)
Group by a1.Name, a1.Sales
Order by a1.Sales DESC, a1.Name DESC;
Result:
Or:
Copy codeThe Code is as follows: SELECT a1.Name, a1.Sales, COUNT (a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <a2.Sales or (a1.Sales = a2.Sales and a1.Name = a2.Name)
Group by a1.Name, a1.Sales
Order by a1.Sales DESC, a1.Name DESC;
Result:
From the above scheme, we can continue to speculate, how to select a certain item?
For example:
Copy codeThe Code is as follows: SELECT * from
(
SELECT a1.Name, a1.Sales, COUNT (a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <a2.Sales or (a1.Sales = a2.Sales and a1.Name = a2.Name)
Group by a1.Name, a1.Sales
Order by a1.Sales DESC, a1.Name DESC
) As tab WHERE tab. sales_rank = 5;
Of course, if the order is changed to 3 at this time, the content cannot be found very well. This is still a drawback and needs to be modified, to be continued.
The above is all the content of the SQL query ranking function instance. I hope you can give us a reference and support more.