| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 1698 人关注过本帖
标题:如何统计一个年级各个班各科位于年级前10%名次各有多少人?
只看楼主 加入收藏
dzy123
Rank: 8Rank: 8
等 级:蝙蝠侠
威 望:5
帖 子:379
专家分:820
注 册:2013-4-18
收藏
得分:0 
回复 10楼 tlliqi
这种方法可行,我想的是如何在查询语句中嵌套查100名的分数
2013-11-18 14:12
dzy123
Rank: 8Rank: 8
等 级:蝙蝠侠
威 望:5
帖 子:379
专家分:820
注 册:2013-4-18
收藏
得分:0 
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
 基本上实现
2013-11-18 14:42
dzy123
Rank: 8Rank: 8
等 级:蝙蝠侠
威 望:5
帖 子:379
专家分:820
注 册:2013-4-18
收藏
得分:0 
感谢各位热心帮助
2013-11-18 14:43
bccn0906
Rank: 9Rank: 9Rank: 9
来 自:广州
等 级:蜘蛛侠
威 望:2
帖 子:414
专家分:1183
注 册:2013-10-16
收藏
得分:0 
看了你这表结构,如这样就求得“语文”在各班的人数了,其它科类同.
SELECT bjmc,COUNT(*) 语文 from (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm11 DESC) k GROUP BY bjmc


[ 本帖最后由 bccn0906 于 2013-11-19 09:26 编辑 ]
2013-11-19 09:16
tlliqi
Rank: 19Rank: 19Rank: 19Rank: 19Rank: 19Rank: 19
等 级:贵宾
威 望:204
帖 子:15453
专家分:65956
注 册:2006-4-27
收藏
得分:0 
以下是引用bccn0906在2013-11-19 09:16:45的发言:

看了你这表结构,如这样就求得“语文”在各班的人数了,其它科类同.
SELECT bjmc,COUNT(*) 语文 from (SELECT TOP 10 PERCENT * FROM 初一半期成绩 ORDER BY xm11 DESC) k GROUP BY bjmc
这样少同分的 比如第11名分数与第10名分数相同
2013-11-19 09:46
bccn0906
Rank: 9Rank: 9Rank: 9
来 自:广州
等 级:蜘蛛侠
威 望:2
帖 子:414
专家分:1183
注 册:2013-10-16
收藏
得分:0 
以下是引用tlliqi在2013-11-19 09:46:14的发言:

这样少同分的 比如第11名分数与第10名分数相同
知道。
2013-11-19 09:59
dzy123
Rank: 8Rank: 8
等 级:蝙蝠侠
威 望:5
帖 子:379
专家分:820
注 册:2013-4-18
收藏
得分:0 
回复 14楼 bccn0906
这个怎么将多科合成一条语句?
2013-11-19 11:24
bccn0906
Rank: 9Rank: 9Rank: 9
来 自:广州
等 级:蜘蛛侠
威 望:2
帖 子:414
专家分:1183
注 册:2013-10-16
收藏
得分:0 
以下是引用dzy123在2013-11-19 11:24:23的发言:

这个怎么将多科合成一条语句?
方法一:(这方法有缺陷,如果某科在某一班的人数为 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 编辑 ]
收到的鲜花
  • tlliqi2013-11-19 15:42 送鲜花  10朵   附言:方法不少
2013-11-19 11:48
sdta
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
来 自:江苏省连云港市
等 级:版主
威 望:335
帖 子:9841
专家分:27213
注 册:2012-2-5
收藏
得分:0 
写个通用代码

坚守VFP最后的阵地
2013-11-19 13:41
tlliqi
Rank: 19Rank: 19Rank: 19Rank: 19Rank: 19Rank: 19
等 级:贵宾
威 望:204
帖 子:15453
专家分:65956
注 册:2006-4-27
收藏
得分:0 
回复 18楼 bccn0906
方法不少
2013-11-19 15:43
快速回复:如何统计一个年级各个班各科位于年级前10%名次各有多少人?
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.034448 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved