成績表中，每班人數不確定，如何去除分數前1/10與后 1/10的成績后，求平均值。-有解無憂

其實就是一個成績表，里面欄位大概如下

每班人數不確定，現在要求計算每科平均分，但這個平均分是不計算后1/10與前1/10名次的同學的成績的。那用SQL陳述句如何實作？

謝謝了！！

uj5u.com熱心網友回復：

SELECT AVG(分數) FROM 成績表 WHERE 分數 NOT IN
(
(SELECT TOP ((SELECT COUNT(*) FROM 成績表)/10) 分數 FROM 成績表 ORDER BY 分數),
(SELECT TOP ((SELECT COUNT(*) FROM 成績表)/10) 分數 FROM 成績表 ORDER BY 分數 DESC)
)

uj5u.com熱心網友回復：

參考 1 樓錕斤拷錕斤拷的回復:

SELECT AVG(分數) FROM 成績表 WHERE 分數 NOT IN
(
(SELECT TOP ((SELECT COUNT(*) FROM 成績表)/10) 分數 FROM 成績表 ORDER BY 分數),
(SELECT TOP ((SELECT COUNT(*) FROM 成績表)/10) 分數 FROM 成績表 ORDER BY 分數 DESC)
)

要按科目分的話要加GROUP BY 科目，子查詢里面也要科目=科目，不過感覺這個方法效率好低啊。

uj5u.com熱心網友回復：



CREATE TABLE #A

(

ID INT,

KEMU VARCHAR(20),

FENSHU INT

)

INSERT INTO #A VALUES (1,'語文',100)

INSERT INTO #A VALUES (2,'語文',4)

INSERT INTO #A VALUES (3,'語文',50)

INSERT INTO #A VALUES (4,'語文',60)

INSERT INTO #A VALUES (5,'數學',100)

INSERT INTO #A VALUES (6,'數學',20)

INSERT INTO #A VALUES (7,'數學',70)

INSERT INTO #A VALUES (8,'數學',72)

INSERT INTO #A VALUES (9,'數學',74)



SELECT A.KEMU 科目, AVG(A.FENSHU) 均分 FROM #A A WHERE A.ID NOT IN(

(SELECT TOP (SELECT CONVERT(INT,CEILING((COUNT(*)*0.1))) FROM #A WHERE KEMU=A.KEMU) ID FROM #A WHERE KEMU = A.KEMU ORDER BY FENSHU),

(SELECT TOP (SELECT CONVERT(INT,CEILING((COUNT(*)*0.1))) FROM #A WHERE KEMU=A.KEMU) ID FROM #A WHERE KEMU = A.KEMU ORDER BY FENSHU DESC)

)GROUP BY A.KEMU



DROP TABLE #A

uj5u.com熱心網友回復：

Quote: 參考 3 樓錕斤拷錕斤拷的回復:

[code=sql]
你要考慮名詞有重復的情況,還要考慮前后第10名的時候,剛好有多名的情況

uj5u.com熱心網友回復：

參考 4 樓雨夾雪的回復:

Quote: 參考 3 樓錕斤拷錕斤拷的回復:

你要考慮名詞有重復的情況,還要考慮前后第10名的時候,剛好有多名的情況

感謝！
換了一種方式,但是感覺越想越復雜了，不知是否可行
不比較名次而是比較分數：分別取前10%分數的最低分和后10%的最高分，然后以這兩個分數為開區間計算平均值



SELECT X.KEMU 科目, AVG(X.FENSHU) 均分 FROM #A X WHERE  

(SELECT TOP 1 FENSHU FROM (SELECT TOP (CONVERT(INT,(ROUND((SELECT COUNT(*) FROM #A WHERE KEMU=X.KEMU)*0.1,0)))) * FROM #A A WHERE A.KEMU = X.KEMU ORDER BY A.FENSHU) AA) <

X.FENSHU AND X.FENSHU <

(SELECT TOP 1 FENSHU FROM (SELECT TOP (CONVERT(INT,(ROUND((SELECT COUNT(*) FROM #A WHERE KEMU=X.KEMU)*0.1,0)))) * FROM #A A WHERE A.KEMU = X.KEMU ORDER BY A.FENSHU DESC) AA)

GROUP BY X.KEMU

uj5u.com熱心網友回復：

思路：
先分組，分成10個，去掉兩頭的再進行計算



CREATE TABLE #A

(

ID INT IDENTITY,

[subject] VARCHAR(20) NOT NULL,

Score INT NOT NULL

)

INSERT INTO #A ([subject],Score) VALUES 

('數學',	88 ),('數學',	86 ),('數學',	53 ),('數學',	65 ),('數學',	35 ),

('數學',	42 ),('數學',	63 ),('數學',	54 ),('數學',	87 ),('數學',	96 ),

('數學',	44 ),('數學',	46 ),('數學',	61 ),('數學',	49 ),('數學',	40 ),

('數學',	88 ),('數學',	73 ),('數學',	87 ),('數學',	65 ),('數學',	58 ),

('數學',	37 ),('數學',	63 ),('數學',	97 ),('數學',	57 ),('數學',	77 ),

('數學',	96 ),('數學',	70 ),('數學',	79 ),('語文',	90 ),('語文',	75 ),

('語文',	98 ),('語文',	93 ),('語文',	77 ),('語文',	96 ),('語文',	76 ),

('語文',	66 ),('語文',	49 ),('語文',	39 ),('語文',	56 ),('語文',	44 ),

('語文',	74 ),('語文',	76 ),('語文',	35 ),('語文',	75 ),('語文',	50 ),

('語文',	69 ),('語文',	69 ),('語文',	90 ),('語文',	64 ),('語文',	100),

('語文',	81 ),('語文',	75 ),('語文',	88 ),('語文',	59 ),('語文',	41 ),

('語文',	61 ),('英語',	62 ),('英語',	57 ),('英語',	34 ),('英語',	90 ),

('英語',	37 ),('英語',	60 ),('英語',	95 ),('英語',	75 ),('英語',	90 ),

('英語',	91 ),('英語',	70 ),('英語',	96 ),('英語',	72 ),('英語',	58 ),

('英語',	75 ),('英語',	38 ),('英語',	58 ),('英語',	39 ),('英語',	91 ),

('英語',	83 ),('英語',	30 ),('英語',	43 ),('英語',	53 ),('英語',	53 ),

('英語',	93 ),('英語',	63 ),('英語',	37 ),('英語',	97 )



 

 ;

WITH t AS (SELECT   *, NTILE(10) OVER (PARTITION BY a.[subject] ORDER BY a.Score) seq FROM  #A a),--分組，分成10個組

     maxSeq AS (SELECT  t.[subject], MAX(seq) maxSeq FROM  t GROUP BY  t.[subject])--最大組號，如果記錄數不到10潭訓小于10，取最大值，如果記錄數確定有10條則不用這個

SELECT      a.[subject], AVG(a.Score) Score_AVG

FROM        t a

WHERE       a.seq > 1 AND   NOT EXISTS (SELECT  * FROM  maxSeq b WHERE  a.[subject] = b.[subject] AND   a.seq = b.maxSeq)--非最大組的，如果確定可以分10組則直接不等于10即可

GROUP BY    a.subject;

DROP TABLE #A;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/239433.html

標籤：基礎類

上一篇：Mysql隨機查詢

下一篇：求助基于STC15F2K60S2單片機的超聲波避障