mysql 组内排名

CREATE TABLE sales (
    id INT PRIMARY KEY,
    salesperson VARCHAR(50),
    region VARCHAR(50),
    amount DECIMAL(10, 2)
);

SELECT 
    salesperson,
    region,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM 
    sales;

SELECT 
    salesperson,
    region,
    amount,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM 
    sales;

SELECT 
    s.salesperson,
    s.region,
    s.amount,
    (
        SELECT COUNT(DISTINCT s2.amount)
        FROM sales s2
        WHERE s2.region = s.region AND s2.amount > s.amount
    ) + 1 AS rank
FROM 
    sales s;