c语言如何计算中位数,如何计算中位数？

尝试捕获INSERT上的行计数，然后使用ROW_NUMBER()选择中间的行：

CREATE TABLE #QuizTemp (QuizProfileID INT,Cnt INT,TotalScore INT)

DECLARE @Rows int

INSERT INTO #QuizTemp

SELECT QuizAnswers.QuizProfileID, COUNT(QuizAnswers.QuizProfileID) AS Cnt, SUM(QuizAnswers.AnsweredYes) As TotalScore

FROM QuizAnswers INNER JOIN

Quizzes ON QuizAnswers.QuizID = Quizzes.QuizID

WHERE (Quizzes.PartnerID = 16)

GROUP BY QuizAnswers.QuizProfileID

HAVING COUNT(QuizAnswers.QuizProfileID)= 5

DECLARE @Rows int

SELECT @Rows=@@Rowcount

;with allrows as

(

SELECT TotalScore, ROW_NUMBER() (ORDER BY TotalScore) AS RowNumber

)

SELECT @Rows AS CNT, TotalScore AS MedianScore

FROM allrows WHERE RowNumber=@Rows/2

DROP TABLE #QuizTemp

编辑

这是一个没有临时表的解决方案：

DECLARE @YourTable table (TotalScore int)

INSERT INTO @YourTable Values (1)

INSERT INTO @YourTable Values (2)

INSERT INTO @YourTable Values (3)

INSERT INTO @YourTable Values (40)

INSERT INTO @YourTable Values (50)

INSERT INTO @YourTable Values (60)

INSERT INTO @YourTable Values (70)

;with allrows as

(

SELECT

TotalScore, ROW_NUMBER() OVER (ORDER BY TotalScore) AS RowNumber

FROM @YourTable

)

,MaxRows AS

(SELECT MAX(RowNumber) AS CNT,CONVERT(int,ROUND(MAX(RowNumber)/2.0,0)) AS Middle FROM allrows)

SELECT

m.CNT

,(SELECT AVG(TotalScore) FROM allrows) AS AvgTotalScore

,a.TotalScore AS Median

,m.Middle AS MedianRowNumber

FROM allrows a

CROSS JOIN MaxRows m

WHERE a.RowNumber=m.Middle

OUTPUT：

CNT AvgTotalScore Median MedianRowNumber

-------------------- -------------------- ----------- --------------------

7 32 40 4

(1 row(s) affected)

如果你编辑第一个CTE：

;with allrows as

(

SELECT QuizAnswers.QuizProfileID, COUNT(QuizAnswers.QuizProfileID) AS Cnt, SUM(QuizAnswers.AnsweredYes) As TotalScore

, ROW_NUMBER() OVER (ORDER BY TotalScore) AS RowNumber

FROM QuizAnswers INNER JOIN

Quizzes ON QuizAnswers.QuizID = Quizzes.QuizID

WHERE (Quizzes.PartnerID = 16)

GROUP BY QuizAnswers.QuizProfileID

HAVING COUNT(QuizAnswers.QuizProfileID)= 5

)

它应该适用于您的查询