本帖题目已解决----感谢吴想和断轮回的热情解答，受益匪浅。

抹香鲸2009

我在别人那儿看到一张成绩统计表，本来别人只要求查询各班级80分以上的人数，我觉得有点简单，就把问题延伸了一下。既要统计各分段人数（分及格以下，和及格以上每10分一个步长），又要有班级总人数。特意发在讨论板块，以此抛砖引玉，请各位大侠指教。我的SQL语句太啰嗦，小弟学习透视表三月有余，以我对透视表的粗浅认识，只能写成这个样子了。请各位感兴趣的朋友提供一些简单的写法。先谢谢了


select 班级,count(姓名) as 班级人数,0 as 不及格人数,0 as 60分以上人数,0 as 70分以上人数,0 as 80分以上人数,0 as 90分以上人数 from [sheet1$] group by 班级 union all
select 班级,0,count(成绩) as 不及格人数,0,0,0,0 from [sheet1$] where 成绩<60 group by 班级 union all
select 班级,0,0,count(成绩) as 60分以上人数,0,0,0 from [sheet1$] where (成绩>=60 and 成绩<70) group by 班级 union all
select 班级,0,0,0,count(成绩) as 70分以上人数,0,0 from [sheet1$] where (成绩>=70 and 成绩<80) group by 班级 union all
select 班级,0,0,0,0,count(成绩) as 80分以上人数,0 from [sheet1$] where (成绩>=80 and 成绩<90) group by 班级 union all
select 班级,0,0,0,0,0,count(成绩) as 90分以上人数 from [sheet1$] where (成绩>=90 and 成绩<=100) group by 班级

[ 本帖最后由 抹香鲸2009 于 2010-4-2 13:54 编辑 ]

抹香鲸2009

怎么无人问津？有用iff函数做的吗？

再来难一点的。要求统计：每个班级各科目各分段人数，分段可以分为：80分以下，80-90，90-100，100-110，110分以上。本题具有一定挑战性。结果格式如下表。

[ 本帖最后由 抹香鲸2009 于 2010-4-1 11:09 编辑 ]

抹香鲸2009

恳请各位大侠不吝赐教！

抹香鲸2009

我已经计算出来2楼的结果，语句繁琐的我要吐了。

select 班级,科目,count(成绩) as 80分以下,0 as 90分以下,0 as 100分以下,0 as 110分以下,0 as 110分以上 from(
select 班级,"语文" as 科目,语文 as 成绩 from [sheet2$] union all  
select 班级,"数学" as 科目,数学 as 成绩 from [sheet2$] union all
select 班级,"英语" as 科目,英语 as 成绩 from [sheet2$] union all
select 班级,"物理" as 科目,物理 as 成绩 from [sheet2$] union all
select 班级,"化学" as 科目,化学 as 成绩 from [sheet2$] union all
select 班级,"历史" as 科目,历史 as 成绩 from [sheet2$] union all
select 班级,"政治" as 科目,政治 as 成绩 from [sheet2$]) where 成绩<80 group by 班级,科目 union all

select 班级,科目,0,count(成绩) as 90分以下,0,0,0 from(
select 班级,"语文" as 科目,语文 as 成绩 from [sheet2$] union all  
select 班级,"数学" as 科目,数学 as 成绩 from [sheet2$] union all
select 班级,"英语" as 科目,英语 as 成绩 from [sheet2$] union all
select 班级,"物理" as 科目,物理 as 成绩 from [sheet2$] union all
select 班级,"化学" as 科目,化学 as 成绩 from [sheet2$] union all
select 班级,"历史" as 科目,历史 as 成绩 from [sheet2$] union all
select 班级,"政治" as 科目,政治 as 成绩 from [sheet2$]) where (成绩<90 and 成绩>=80)group by 班级,科目 union all

select 班级,科目,0,0,count(成绩) as 100分以下,0,0 from(
select 班级,"语文" as 科目,语文 as 成绩 from [sheet2$] union all  
select 班级,"数学" as 科目,数学 as 成绩 from [sheet2$] union all
select 班级,"英语" as 科目,英语 as 成绩 from [sheet2$] union all
select 班级,"物理" as 科目,物理 as 成绩 from [sheet2$] union all
select 班级,"化学" as 科目,化学 as 成绩 from [sheet2$] union all
select 班级,"历史" as 科目,历史 as 成绩 from [sheet2$] union all
select 班级,"政治" as 科目,政治 as 成绩 from [sheet2$]) where (成绩<100 and 成绩>=90)group by 班级,科目 union all

select 班级,科目,0,0,0,count(成绩) as 110分以下,0 from(
select 班级,"语文" as 科目,语文 as 成绩 from [sheet2$] union all  
select 班级,"数学" as 科目,数学 as 成绩 from [sheet2$] union all
select 班级,"英语" as 科目,英语 as 成绩 from [sheet2$] union all
select 班级,"物理" as 科目,物理 as 成绩 from [sheet2$] union all
select 班级,"化学" as 科目,化学 as 成绩 from [sheet2$] union all
select 班级,"历史" as 科目,历史 as 成绩 from [sheet2$] union all
select 班级,"政治" as 科目,政治 as 成绩 from [sheet2$]) where (成绩<110 and 成绩>=100)group by 班级,科目 union all

select 班级,科目,0,0,0,0,count(成绩) as 110分以上 from(
select 班级,"语文" as 科目,语文 as 成绩 from [sheet2$] union all  
select 班级,"数学" as 科目,数学 as 成绩 from [sheet2$] union all
select 班级,"英语" as 科目,英语 as 成绩 from [sheet2$] union all
select 班级,"物理" as 科目,物理 as 成绩 from [sheet2$] union all
select 班级,"化学" as 科目,化学 as 成绩 from [sheet2$] union all
select 班级,"历史" as 科目,历史 as 成绩 from [sheet2$] union all
select 班级,"政治" as 科目,政治 as 成绩 from [sheet2$]) where 成绩>=110 group by 班级,科目

[ 本帖最后由 抹香鲸2009 于 2010-4-1 16:31 编辑 ]

wuxiang_123

这题用多重合并计算区域就可以了，不必用SQL。
PS：数据源区域不要选总计列。

抹香鲸2009

太谢谢吴想了！佩服

wuxiang_123

1. SELECT 班级,科目,值,IIF(值<80,"<80",IIF(值<90,"80-90",IIF(值<100,"90-100",IIF(值<110,"100-110",">=110")))) FROM (
   
2. SELECT 班级,"语文" AS 科目,语文 AS 值 FROM [Sheet1$]
   
3. UNION ALL
   
4. SELECT 班级,"数学",数学 FROM [Sheet1$]
   
5. UNION ALL
   
6. SELECT 班级,"英语",英语 FROM [Sheet1$]
   
7. UNION ALL
   
8. SELECT 班级,"物理",物理 FROM [Sheet1$]
   
9. UNION ALL
   
10. SELECT 班级,"化学",化学 FROM [Sheet1$]
    
11. UNION ALL
    
12. SELECT 班级,"历史",历史 FROM [Sheet1$]
    
13. UNION ALL
    
14. SELECT 班级,"政治",政治 FROM [Sheet1$]
    
15. )

复制代码

SQL参考。
PS：使用UNION或UNION ALL时，后面的AS 会自动忽略，所以，后面的AS 我们可以不用输，只保留第一个。

抹香鲸2009

8楼的解释太精辟了，以前语句写得太啰嗦，也不知道原因，现在算是明白了一点点。要好好向你学习。

[ 本帖最后由 抹香鲸2009 于 2010-4-2 12:59 编辑 ]

masterexcel

SQL：用partition结合int函数，供参考：
工作簿--成绩统计-10版.rar (48.64 KB, 下载次数: 50)

2010-4-2 11:52 上传

点击文件名下载附件

1. 
   
2. select
   
3.         班级,
   
4.         成绩,
   
5.         partition(int(成绩),60,89,10) as 分数段
   
6. from
   
7.         [Sheet1$a:c]
   

复制代码

抹香鲸2009

谢谢10楼提供的精妙方法。这里果真是藏龙卧虎啊。

[ 本帖最后由 抹香鲸2009 于 2010-4-3 08:22 编辑 ]