mysql 以某个最大值排名

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    amount DECIMAL(10, 2)
);

SELECT 
    s.id,
    s.product_name,
    s.amount,
    (SELECT COUNT(DISTINCT s2.amount) 
     FROM sales s2 
     WHERE s2.amount > s.amount) + 1 AS rank
FROM 
    sales s
ORDER BY 
    s.amount DESC;

SELECT 
    s.id,
    s.product_name,
    s.amount,
    DENSE_RANK() OVER (ORDER BY s.amount DESC) AS dense_rank,
    RANK() OVER (ORDER BY s.amount DESC) AS rank
FROM 
    sales s;

SELECT 
    *
FROM (
    SELECT 
        s.id,
        s.product_name,
        s.amount,
        RANK() OVER (ORDER BY s.amount DESC) AS rank
    FROM 
        sales s
) ranked_sales
WHERE 
    rank <= 10;