周总找我问个报表SQL实现逻辑的案例，废话不说给他看看。
原SQL：
SELECT d.tname 姓名,
d.spname 岗位,
d.sum_cnt 报单单量,
d.min_cnt 放款单量,
d.date 月份
FROM (SELECT *
FROM (SELECT a.zts_name tname,
a.sp_name spname,
CONVERT(ifnull(a.order_cnt, '0'), SIGNED) sum_cnt,
CONVERT(ifnull(b.order_cnt, '0'), SIGNED) min_cnt,
a.gmt_create DATE
FROM
(
SELECT
t.zts_name, t.sp_name, count( t.order_no ) AS order_cnt, DATE_FORMAT ( t.gmt_create, '%Y-%m' ) gmt_create
FROM
(
SELECT
zts.`name` AS zts_name,
ztr.`name` AS sp_name,
rto.order_no,
rto.gmt_create AS gmt_create
FROM
rob_t_order rto
INNER JOIN rob_t_stakeholder rts ON rto.id = rts.order_id
INNER JOIN zeu_t_staff zts ON rts.principal_id = zts.id
INNER JOIN zeu_t_job ztj ON zts.id = ztj.staff_id
INNER JOIN zeu_t_role ztr ON ztj.role_id = ztr.id
WHERE
rts.role = '7'
AND ztr.`description` = '客户经理'
) t
GROUP BY
t.zts_name
) A
LEFT JOIN (
SELECT
t.zts_name,
t.sp_name,
count( t.order_no ) AS order_cnt,
DATE_FORMAT( t.gmt_create, '%Y-%m' ) gmt_create
FROM
(
SELECT
zts.`name` AS zts_name,
ztr.`name` AS sp_name,
rto.order_no,
rto.gmt_create AS gmt_create
FROM
rob_t_order rto
INNER JOIN rob_t_stakeholder rts ON rto.id = rts.order_id
INNER JOIN zeu_t_staff zts ON rts.principal_id = zts.id
INNER JOIN zeu_t_job ztj ON zts.id = ztj.staff_id
INNER JOIN zeu_t_role ztr ON ztj.role_id = ztr.id
WHERE
rto.state IN ( 4010, 4030 )
AND rts.role = '7'
AND ztr.`description` = '客户经理'
) t
GROUP BY
t.zts_name
) b
ON A.zts_name = b.zts_name
AND A.gmt_create = b.gmt_create) c
ORDER BY c.date ASC,
CONVERT(c.sum_cnt, signed) DESC) d;
精简后的结果集：
周总的意思很简单，就是以 月份 来分组，求出 报单数量 在不同月份中的中位数就行。
这道题读者不需要过多关注原来的SQL是如何写的，只需要把重点放在如何取 报单数量 的中位数即可。
中位数：在一个数列中，如果按照大小顺序排列，中位数就是位于中间的那个数。
这道SQL的解题思路其实很简单：
1、使用 DENSE_RANK 开窗函数对 月份分组 ，然后对 报单数量排序，注意：不能使用 ROW_NUMBER() 和 RANK() 进行排序，这两个函数一个会给连续的序号，另外一个会跳号。
2、然后对 DENSE_RANK 开窗函数 的排序结果进行 max 开窗取每个月的最大序号。
3、最后使用 每个月的最大序号除以2 就可以得出不同月份的中位数。
最终SQL代码：
SELECT *
FROM (
/* FLOOR(j.最大的编号 / 2) 中位数编号
最后使用 每个月的最大序号除以2 就可以得出不同月份的中位数。
*/
SELECT j.姓名,
j.岗位,
j.报单单量,
j.放款单量,
j.月份,
j.编号,
FLOOR(j.最大的编号 / 2) 中位数编号
FROM (
/* MAX(x.rn) OVER (PARTITION BY x.月份 ) 最大的编号
然后对 DENSE_RANK 开窗函数 的排序结果进行 max 开窗取每个月的最大序号
*/
SELECT x.姓名,
x.岗位,
x.报单单量,
x.放款单量,
x.月份,
x.rn 编号,
MAX(x.rn) OVER (PARTITION BY x.月份 ) 最大的编号
FROM (
/* DENSE_RANK() OVER (PARTITION BY d.date ORDER BY d.sum_cnt) rn
对月份分组 ，然后对报单数量排序
*/
SELECT
d.tname 姓名,
d.spname 岗位,
d.sum_cnt 报单单量,
d.min_cnt 放款单量,
d.date 月份,
DENSE_RANK() OVER (PARTITION BY d.date ORDER BY d.sum_cnt) rn
FROM (
/* 以下SQL为原始SQL */
SELECT *
FROM (
SELECT a.zts_name tname,
a.sp_name spname,
CONVERT(ifnull(a.order_cnt, '0'), SIGNED) sum_cnt,
CONVERT(ifnull(b.order_cnt, '0'), SIGNED) min_cnt,
a.gmt_create DATE
FROM
(
SELECT
t.zts_name, t.sp_name, count( t.order_no ) AS order_cnt, DATE_FORMAT ( t.gmt_create, '%Y-%m' ) gmt_create
FROM
(
SELECT
zts.`name` AS zts_name,
ztr.`name` AS sp_name,
rto.order_no,
rto.gmt_create AS gmt_create
FROM
rob_t_order rto
INNER JOIN rob_t_stakeholder rts ON rto.id = rts.order_id
INNER JOIN zeu_t_staff zts ON rts.principal_id = zts.id
INNER JOIN zeu_t_job ztj ON zts.id = ztj.staff_id
INNER JOIN zeu_t_role ztr ON ztj.role_id = ztr.id
WHERE
rts.role = '7'
AND ztr.`description` = '客户经理'
) t
GROUP BY
t.zts_name
) A
LEFT JOIN (
SELECT
t.zts_name,
t.sp_name,
count( t.order_no ) AS order_cnt,
DATE_FORMAT( t.gmt_create, '%Y-%m' ) gmt_create
FROM
(
SELECT
zts.`name` AS zts_name,
ztr.`name` AS sp_name,
rto.order_no,
rto.gmt_create AS gmt_create
FROM
rob_t_order rto
INNER JOIN rob_t_stakeholder rts ON rto.id = rts.order_id
INNER JOIN zeu_t_staff zts ON rts.principal_id = zts.id
INNER JOIN zeu_t_job ztj ON zts.id = ztj.staff_id
INNER JOIN zeu_t_role ztr ON ztj.role_id = ztr.id
WHERE
rto.state IN ( 4010, 4030 )
AND rts.role = '7'
AND ztr.`description` = '客户经理'
) t
GROUP BY
t.zts_name
) b
ON A.zts_name = b.zts_name
AND A.gmt_create = b.gmt_create) c
ORDER BY c.date ASC,
CONVERT(c.sum_cnt, signed) DESC) d) x) j) g
WHERE g.编号 = g.中位数编号;
最后可以看到该SQL已经改写成功，可以列出不同月份 达到中位数的销售姓名的单量。
其实这条SQL不算很难，只是最近在戒烟，注意力集中不了思考了好一会才实现，哎戒烟真的是太痛苦了。