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 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