TA貢獻(xiàn)1847條經(jīng)驗 獲得超7個贊
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) );
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
TA貢獻(xiàn)1827條經(jīng)驗 獲得超4個贊
對于幾乎所有SQL中的中介： SELECT x.val from data x, data yGROUP BY x.valHAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2
select count(*) from table --find the number of rows
median_row = floor(count / 2).
select val from table order by val asc limit median_row,1
TA貢獻(xiàn)1876條經(jīng)驗 獲得超5個贊
SELECT x.val from data x, data yGROUP BY x.valHAVING SUM(SIGN(1-SIGN(y.val-x.val)))/COUNT(*) > .5LIMIT 1
舉報