569. 员工薪水中位数

English Version

题目描述

表: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| company      | varchar |
| salary       | int     |
+--------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行表示公司和一名员工的工资。

 

编写解决方案，找出每个公司的工资中位数。

以 任意顺序 返回结果表。

查询结果格式如下所示。

 

示例 1:

输入: 
Employee 表:
+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 1  | A       | 2341   |
| 2  | A       | 341    |
| 3  | A       | 15     |
| 4  | A       | 15314  |
| 5  | A       | 451    |
| 6  | A       | 513    |
| 7  | B       | 15     |
| 8  | B       | 13     |
| 9  | B       | 1154   |
| 10 | B       | 1345   |
| 11 | B       | 1221   |
| 12 | B       | 234    |
| 13 | C       | 2345   |
| 14 | C       | 2645   |
| 15 | C       | 2645   |
| 16 | C       | 2652   |
| 17 | C       | 65     |
+----+---------+--------+
输出: 
+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 5  | A       | 451    |
| 6  | A       | 513    |
| 12 | B       | 234    |
| 9  | B       | 1154   |
| 14 | C       | 2645   |
+----+---------+--------+

 

进阶: 你能在不使用任何内置函数或窗口函数的情况下解决它吗?

解法

SQL

# Write your MySQL query statement below
WITH
    t AS (
        SELECT
            *,
            row_number() OVER (
                PARTITION BY company
                ORDER BY salary ASC
            ) AS rk,
            count(id) OVER (PARTITION BY company) AS n
        FROM Employee
    )
SELECT
    id,
    company,
    salary
FROM t
WHERE rk >= n / 2 AND rk <= n / 2 + 1;