SELECT
gender, height
FROM
heights
ORDERBY gender , height ASC
返回以下结果:
接下来,我们需要计算每个组的行数,并将其作为另一列添加到表格中。
SELECT
gender,
height,
(SELECTCOUNT(*)
FROM
heights
WHERE
a.gender = gender) AS total_of_group
FROM
(SELECT
gender, height
FROM
heights
ORDERBY gender , height) AS a
SET @ROW_NUMBER:=0;SET @median_group:='';SELECT
@ROW_NUMBER:=CASEWHEN @median_group = gender THEN @ROW_NUMBER + 1ELSE1ENDAS count_of_group,
@median_group:=gender AS median_group,
gender,
height,
(SELECTCOUNT(*)
FROM
heights
WHERE
a.gender = gender) AS total_of_group
FROM
(SELECT
gender, height
FROM
heights
ORDERBY gender , height) AS a
SET @ROW_NUMBER:=0;SET @median_group:='';SELECT
median_group, AVG(height) AS median
FROM
(SELECT
@ROW_NUMBER:=CASEWHEN @median_group = gender THEN @ROW_NUMBER + 1ELSE1ENDAS count_of_group,
@median_group:=gender AS median_group,
gender,
height,
(SELECTCOUNT(*)
FROM
heights
WHERE
a.gender = gender) AS total_of_group
FROM
(SELECT
gender, height
FROM
heights
ORDERBY gender , height) AS a) AS b
WHERE
count_of_group BETWEEN total_of_group / 2.0AND total_of_group / 2.0 + 1GROUPBY median_group