这种方法可行，我想的是如何在查询语句中嵌套查100名的分数

SELECT bjmc,(sele COUNT(1) FROM 初一半期成绩 WHERE t.bjmc=bjmc AND xm11>=(SELECT MIN(xm11) FROM  (sele TOP 10 PERCENT xm11 FROM 初一半期成绩 ORDER BY xm11 desc) k)) 语文,;
            (sele COUNT(1) FROM 初一半期成绩 WHERE t.bjmc=bjmc AND xm21>=(SELECT MIN(xm21) FROM  (sele TOP 10 PERCENT xm21 FROM 初一半期成绩 ORDER BY xm21 desc) k)) 数学,;
            (sele COUNT(1) FROM 初一半期成绩 WHERE t.bjmc=bjmc AND xm31>=(SELECT MIN(xm31) FROM  (sele TOP 10 PERCENT xm31 FROM 初一半期成绩 ORDER BY xm31 desc) k)) 英语,;
            (sele COUNT(1) FROM 初一半期成绩 WHERE t.bjmc=bjmc AND xm41>=(SELECT MIN(xm41) FROM  (sele TOP 10 PERCENT xm41 FROM 初一半期成绩 ORDER BY xm41 desc) k)) 政治,;
            (sele COUNT(1) FROM 初一半期成绩 WHERE t.bjmc=bjmc AND xm51>=(SELECT MIN(xm51) FROM  (sele TOP 10 PERCENT xm51 FROM 初一半期成绩 ORDER BY xm51 desc) k)) 历史,;
            (sele COUNT(1) FROM 初一半期成绩 WHERE t.bjmc=bjmc AND xm61>=(SELECT MIN(xm61) FROM  (sele TOP 10 PERCENT xm61 FROM 初一半期成绩 ORDER BY xm61 desc) k)) 地理,;
            (sele COUNT(1) FROM 初一半期成绩 WHERE t.bjmc=bjmc AND xm71>=(SELECT MIN(xm71) FROM  (sele TOP 10 PERCENT xm71 FROM 初一半期成绩 ORDER BY xm71 desc) k)) 生物,;
            (sele COUNT(1) FROM 初一半期成绩 WHERE t.bjmc=bjmc AND xm81>=(SELECT MIN(xm81) FROM  (sele TOP 10 PERCENT xm81 FROM 初一半期成绩 ORDER BY xm81 desc) k)) 语数英,;
            (sele COUNT(1) FROM 初一半期成绩 WHERE t.bjmc=bjmc AND xm91>=(SELECT MIN(xm91) FROM  (sele TOP 10 PERCENT xm91 FROM 初一半期成绩 ORDER BY xm91 desc) k)) 总分;
 FROM 初一半期成绩 t GROUP BY bjmc ORDER BY
 基本上实现

感谢各位热心帮助

看了你这表结构,如这样就求得“语文”在各班的人数了，其它科类同.
SELECT bjmc,COUNT(*) 语文 from (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm11 DESC) k GROUP BY bjmc


[ 本帖最后由 bccn0906 于 2013-11-19 09:26 编辑 ]

这样少同分的 比如第11名分数与第10名分数相同

知道。

这个怎么将多科合成一条语句？

方法一:(这方法有缺陷，如果某科在某一班的人数为 0（即没记录）,则会影响该班的其它科查询结果)

SELECT a.bjmc,语文,数学,英语,政治,历史,地理,生物,语数英,总分 FROM ;
(SELECT bjmc,COUNT(*) 语文 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm11 DESC) k GROUP BY bjmc) a;
,(SELECT bjmc,COUNT(*) 数学 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm21 DESC) k GROUP BY bjmc) b;
,(SELECT bjmc,COUNT(*) 英语 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm31 DESC) k GROUP BY bjmc) c;
,(SELECT bjmc,COUNT(*) 政治 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm41 DESC) k GROUP BY bjmc) d;
,(SELECT bjmc,COUNT(*) 历史 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm51 DESC) k GROUP BY bjmc) e;
,(SELECT bjmc,COUNT(*) 地理 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm61 DESC) k GROUP BY bjmc) f;
,(SELECT bjmc,COUNT(*) 生物 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm71 DESC) k GROUP BY bjmc) g;
,(SELECT bjmc,COUNT(*) 语数英 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm81 DESC) k GROUP BY bjmc) h;
,(SELECT bjmc,COUNT(*) 总分 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm91 DESC) k GROUP BY bjmc) i ;
WHERE a.bjmc=b.bjmc AND b.bjmc=c.bjmc AND c.bjmc=d.bjmc AND d.bjmc=e.bjmc AND e.bjmc=f.bjmc AND f.bjmc=g.bjmc AND g.bjmc=h.bjmc AND h.bjmc=i.bjmc

方法二:
SELECT bjmc,SUM(IIF(xm='语文',rs,0)) 语文,SUM(IIF(xm='数学',rs,0)) 数学,SUM(IIF(xm='英语',rs,0)) 英语,SUM(IIF(xm='政治',rs,0)) 政治,SUM(IIF(xm='历史',rs,0)) 历史,SUM(IIF(xm='地理',rs,0)) 地理,SUM(IIF(xm='生物',rs,0)) 生物,SUM(IIF(xm='语数英',rs,0)) 语数英,SUM(IIF(xm='总分',rs,0)) 总分 FROM ;
(SELECT bjmc,COUNT(*) rs,'语文' xm FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm11 DESC) k GROUP BY bjmc ;
UNION SELECT bjmc,COUNT(*) rs,'数学' xm FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm21 DESC) k GROUP BY bjmc ;
UNION SELECT bjmc,COUNT(*) rs,'英语' xm FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm31 DESC) k GROUP BY bjmc ;
UNION SELECT bjmc,COUNT(*) rs,'政治' xm FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm41 DESC) k GROUP BY bjmc ;
UNION SELECT bjmc,COUNT(*) rs,'历史' xm FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm51 DESC) k GROUP BY bjmc ;
UNION SELECT bjmc,COUNT(*) rs,'地理' xm FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm61 DESC) k GROUP BY bjmc ;
UNION SELECT bjmc,COUNT(*) rs,'生物' xm FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm71 DESC) k GROUP BY bjmc ;
UNION SELECT bjmc,COUNT(*) rs,'语数英' xm FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm81 DESC) k GROUP BY bjmc ;
UNION SELECT bjmc,COUNT(*) rs,'总分' xm FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm91 DESC) k GROUP BY bjmc) b GROUP BY bjmc

方法三:
select bjmc,SUM(语文) 语文,SUM(数学) 数学,SUM(英语) 英语,SUM(政治) 政治,SUM(历史) 历史,SUM(地理) 地理,SUM(生物) 生物,SUM(语数英) 语数英,SUM(总分) 总分 FROM (;
SELECT bjmc,COUNT(*) 语文,0 数学, 0 英语,0 政治,0 历史,0 地理,0 生物,0 语数英,0 总分 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm11 DESC) k GROUP BY bjmc;
UNION SELECT bjmc,0,COUNT(*) 数学,0,0,0,0,0,0,0 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm21 DESC) k GROUP BY bjmc;
UNION SELECT bjmc,0,0,COUNT(*) 英语,0,0,0,0,0,0 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm31 DESC) k GROUP BY bjmc;
UNION SELECT bjmc,0,0,0,COUNT(*) 政治,0,0,0,0,0 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm41 DESC) k GROUP BY bjmc;
UNION SELECT bjmc,0,0,0,0,COUNT(*) 历史,0,0,0,0 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm51 DESC) k GROUP BY bjmc;
UNION SELECT bjmc,0,0,0,0,0,COUNT(*) 地理,0,0,0 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm61 DESC) k GROUP BY bjmc;
UNION SELECT bjmc,0,0,0,0,0,0,COUNT(*) 生物,0,0 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm71 DESC) k GROUP BY bjmc;
UNION SELECT bjmc,0,0,0,0,0,0,0,COUNT(*) 语数英,0 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm81 DESC) k GROUP BY bjmc;
UNION SELECT bjmc,0,0,0,0,0,0,0,0,COUNT(*) 总分 FROM (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm91 DESC) k GROUP BY bjmc) b GROUP BY bjmc


[ 本帖最后由 bccn0906 于 2013-11-19 12:31 编辑 ]

收到的鲜花

• tlliqi 于 2013-11-19 15:42 送鲜花  10朵   附言：方法不少

写个通用代码

方法不少