在MariaDB/MySQL中:SELECT AVG(dd.val) as median_valFROM (SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum FROM data d, (SELECT @rownum:=0) r WHERE d.val is NOT NULL
-- put some where clause here
ORDER BY d.val) as ddWHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );
史蒂夫·科恩指出,在第一次传递之后,@rownum将包含行总数。这可以用来确定中间值,因此不需要第二次传递或加入。
也AVG(dd.val)和dd.row_number IN(...)用于在有偶数记录时正确生成中值。推理:SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3