以下是引用dzy123在2013-11-20 08:49:10的发言:
能给个示例代码不?
这个不是实例吗(将你的横向改为畅通纵向,然后又从纵向改为横向)
即从你的表结构 ...xm11,xm21,xm31.....改为 bjmc,rs,xm
后再改为 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