Mysql如何查询中位数？

表结构如下：

如何查出每科成绩的中位数呢？

可以一次查出每一科的中位数吗

求平均数可以吗？

select avg(语文),avg(数学),avg(语言),avg(政治) from 表名

可以用自定义一个变量来当做行号解决这个问题。

自定义变量@ID当成行号(按照列pos排序)：

SET @ID=0;
SELECT @ID:=@ID+1 AS ID , pos FROM alerts_ring_list WHERE 1=1 ORDER BY `pos`

然后做个子查询

SET @ID=0;
SELECT AVG(pos) FROM (
SELECT @ID:=@ID+1 AS ID , pos FROM alerts_ring_list WHERE 1=1 ORDER BY `pos`
) a WHERE IF (@ID%2=0, ID in (ROUND(@ID/2,0),ROUND(@ID/2,0)+1), ID=ROUND(@ID/2,0))

具体的还需要修改下

可以用窗口函数求每课成绩的顺序号，再于学生总人数相比，求出中尉数学生。下面是3课中尉数一起求的，你可以分开求单科。学生总人数是奇数或者偶数都已经考虑好了。

SELECT	*
FROM	(
			SELECT	*,
					(SELECT COUNT(*) FROM Student_Score) AS Number_Of_Student,
					ROW_NUMBER() OVER(ORDER BY CN_Score) AS CN_Order,
					ROW_NUMBER() OVER(ORDER BY Math_Score) AS Math_Order,
					ROW_NUMBER() OVER(ORDER BY EN_Score) AS EN_Order
			FROM Student_Score
		) AS Ordered_Score
WHERE	ABS(CN_Order * 2 - Number_Of_Student - 1) <= 1
		OR ABS(Math_Order * 2 - Number_Of_Student - 1) <= 1
		OR ABS(EN_Order * 2 - Number_Of_Student - 1) <= 1
;


// Output
User_Name    CN_Score    Math_Score    EN_Score    Number_Of_Student    CN_Order    EN_OrderMath_Order    
Li Si	45	87	45	3	2	3	1
Zhang San	34	58	58	3	1	2	2

您好，我是有问必答小助手，您的问题已经有小伙伴解答了，您看下是否解决，可以追评进行沟通哦~

如果有您比较满意的答案 / 帮您提供解决思路的答案，可以点击【采纳】按钮，给回答的小伙伴一些鼓励哦～～

ps:问答VIP仅需29元，即可享受5次/月 有问必答服务，了解详情>>>https://vip.csdn.net/askvip?utm_source=1146287632

每以科的中位数

SELECT	User_Name, CN_Score
FROM	(
			SELECT	*,
					(SELECT COUNT(*) FROM Student_Score) AS Number_Of_Student,
					ROW_NUMBER() OVER(ORDER BY CN_Score) AS Score_Order
			FROM Student_Score
		) AS Ordered_Score
WHERE	ABS(Score_Order * 2 - Number_Of_Student - 1) <= 1
;
-- Output:
User_name	CN_Score
Li Si	45

SELECT	User_Name, Math_Score
FROM	(
			SELECT	*,
					(SELECT COUNT(*) FROM Student_Score) AS Number_Of_Student,
					ROW_NUMBER() OVER(ORDER BY Math_Score) AS Score_Order
			FROM Student_Score
		) AS Ordered_Score
WHERE	ABS(Score_Order * 2 - Number_Of_Student - 1) <= 1
;
-- Output:
User_name	Math_Score
Zhang San	58

SELECT	User_Name, EN_Score
FROM	(
			SELECT	*,
					(SELECT COUNT(*) FROM Student_Score) AS Number_Of_Student,
					ROW_NUMBER() OVER(ORDER BY EN_Score) AS Score_Order
			FROM Student_Score
		) AS Ordered_Score
WHERE	ABS(Score_Order * 2 - Number_Of_Student - 1) <= 1
;

-- Output:
User_name	EN_Score
Zhang San	58