题解 | #最差是第几名(二)#毫无争议的正确解法

-- 毫无争议的正确解法 -- 所有成绩按照顺序排列成字符串，用repeat函数复制每个成绩，用group_concat函数合并所有成绩形成字符串，用replace函数把字符串中的“,”替换为空格('')，然后用mid函数选取中位数位置的字符，由于奇数和偶数中位数不同，用union(去重)合并两种情况，得出答案。

select mid(replace(group_concat(a.r order by grade),",",""),floor((sum(number)+1)/2),1) grade from (select grade,number,repeat(grade,number) r from class_grade order by grade) a

union

select mid(replace(group_concat(a.r order by grade),",",""),ceil((sum(number)+1)/2),1) grade from (select grade,number,repeat(grade,number) r from class_grade order by grade) a order by grade;