Question

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   |
+----+---------+--------+

 

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

解法

方法一

# 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;