假如有这样一个表表名SCORE,字段STUID(varchar(50)),SCORE(int)记录如下stuid score
1 100
2 22
3 32
4 89
5 20
6 77
7 68
8 90
9 82
10 66我想去掉前面的10%,后面的10%,剩下记录求平均分呢也就是这样stuid score
3 32
4 89
5 20
6 77
7 68
8 90
9 82
10 66
平均分,不要删除记录
1 100
2 22
3 32
4 89
5 20
6 77
7 68
8 90
9 82
10 66我想去掉前面的10%,后面的10%,剩下记录求平均分呢也就是这样stuid score
3 32
4 89
5 20
6 77
7 68
8 90
9 82
10 66
平均分,不要删除记录
INSERT INTO @SCORE
SELECT '1',100
UNION ALL
SELECT '2',22
UNION ALL
SELECT '3',32
UNION ALL
SELECT '4',89
UNION ALL
SELECT '5',20
UNION ALL
SELECT '6',77
UNION ALL
SELECT '6',68
UNION ALL
SELECT '8',90
UNION ALL
SELECT '9',82
UNION ALL
SELECT '10',66
SELECT avg(SCORE*1.0) FROM @SCORE WHERE
STUID NOT IN
(
SELECT STUID FROM ( SELECT TOP 10 PERCENT * FROM @SCORE ORDER BY SCORE ASC ) a
UNION ALL
SELECT STUID FROM ( SELECT TOP 10 PERCENT * FROM @SCORE ORDER BY SCORE DESC ) a
)65.750000
STUID NOT IN
(
SELECT STUID FROM ( SELECT TOP 10 PERCENT * FROM @SCORE ORDER BY SCORE ASC ) a
UNION ALL
SELECT STUID FROM ( SELECT TOP 10 PERCENT * FROM @SCORE ORDER BY SCORE DESC ) a
)
GROUP BY STUID WITH ROLLUP STUID
-------------------------------------------------- ----------------------------------------
10 66.000000
2 22.000000
3 32.000000
4 89.000000
6 72.500000
8 90.000000
9 82.000000
NULL 65.750000
INSERT INTO @SCORE
SELECT '1',100
UNION ALL
SELECT '2',22
UNION ALL
SELECT '3',32
UNION ALL
SELECT '4',89
UNION ALL
SELECT '5',20
UNION ALL
SELECT '6',77
UNION ALL
SELECT '6',68
UNION ALL
SELECT '8',90
UNION ALL
SELECT '9',82
UNION ALL
SELECT '10',66
SELECT STUID=ISNULL(STUID,'平均'),SCORE=avg(SCORE*1.0) FROM @SCORE WHERE
STUID NOT IN
(
SELECT STUID FROM ( SELECT TOP 10 PERCENT * FROM @SCORE ORDER BY SCORE ASC ) a
UNION ALL
SELECT STUID FROM ( SELECT TOP 10 PERCENT * FROM @SCORE ORDER BY SCORE DESC ) a
)
GROUP BY STUID WITH ROLLUP
STUID SCORE
-------------------------------------------------- ----------------------------------------
10 66.000000
2 22.000000
3 32.000000
4 89.000000
6 72.500000
8 90.000000
9 82.000000
平均 65.750000
SELECT AVG(score) FROM SCORE
WHERE stuid NOT IN (SELECT TOP 10 PERCENT stuid from SCORE ORDER BY SCORE ASC)
AND stuid NOT IN (SELECT TOP 10 PERCENT stuid from SCORE ORDER BY SCORE DESC)
SELECT AVG(score) FROM SCORE
WHERE stuid NOT IN (SELECT TOP 10 PERCENT stuid from SCORE ORDER BY SCORE ASC)
AND stuid NOT IN (SELECT TOP 10 PERCENT stuid from SCORE ORDER BY SCORE DESC)
这个SQL语句比较经典 学习了
(
(select max(score) as score from GradeT)
union all
(select min(score) as score from GradeT)
)
兄弟,还有没有其它的经典语句呀,共享学习一下!谢谢!