mysql 中位数

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

INSERT INTO employee (id, name, salary) VALUES
(1, 'Alice', 5000),
(2, 'Bob', 6000),
(3, 'Charlie', 7000),
(4, 'David', 8000),
(5, 'Eve', 9000);

SELECT AVG(salary) AS median_salary
FROM (
    SELECT salary
    FROM employee
    ORDER BY salary
    LIMIT 2 - (SELECT COUNT(*) FROM employee) % 2
    OFFSET (SELECT (COUNT(*) - 1) / 2 FROM employee)
) AS subquery;

WITH ranked_salaries AS (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary) AS row_num,
           COUNT(*) OVER () AS total_count
    FROM employee
)
SELECT AVG(salary) AS median_salary
FROM ranked_salaries
WHERE row_num IN (FLOOR((total_count + 1) / 2), CEIL((total_count + 1) / 2));