SELECT
id,
@INDEX := @INDEX + 1 AS myindex,
`VALUE` -- myindex代表的是这一列数,@index是位置
FROM
student_t
INNER JOIN ( SELECT @INDEX := 0 ) AS initvar ON 1 = 1
ORDER BY
`VALUE` #最内层
2.2 中位数:当记录是奇数时,中位数中间位置的数;当记录是偶数时,中位数是中间两个数的平均
SELECT
GROUP_CONCAT( id ),
avg( `VALUE` )
FROM
(#第二层开始
SELECT
id,
@INDEX := @INDEX + 1 AS myindex,
`VALUE` -- myindex代表的是这一列数,@index是位置
FROM
student_t
INNER JOIN ( SELECT @INDEX := 0 ) AS initvar ON 1 = 1
ORDER BY
`VALUE` #最内层
) AS t
WHERE
myindex = floor( @INDEX / 2+1 ) OR myindex = ceil( @INDEX / 2 )
2.3 四分位数:
SELECT
GROUP_CONCAT( id ),
avg( `VALUE` )
FROM
(#第二层开始
SELECT
id,
@INDEX := @INDEX + 1 AS myindex,
`VALUE` -- myindex代表的是这一列数,@index是位置
FROM
student_t
INNER JOIN ( SELECT @INDEX := 0 ) AS initvar ON 1 = 1
ORDER BY
`VALUE` #最内层
) AS t
WHERE
myindex = floor(( @INDEX+1) /4 )
SELECT
GROUP_CONCAT( id ),
avg( `VALUE` )
FROM
(#第二层开始
SELECT
id,
@INDEX := @INDEX + 1 AS myindex,
`VALUE` -- myindex代表的是这一列数,@index是位置
FROM
student_t
INNER JOIN ( SELECT @INDEX := 0 ) AS initvar ON 1 = 1
ORDER BY
`VALUE` #最内层
) AS t
WHERE
myindex = floor(3*( @INDEX+1) /4 )
create table student_t (id varchar(32) primary key,value int);insert into student_t (id,value)values (‘A’,40),(‘B’,50),(‘C’,60),(‘D’,70),(‘E’,80),(‘F’,90);SELECTgroup_concat( id ),avg( VA...