急急急...求助加条件的分数段统计
在VB里面，数据库我用的是access。我的数据库里面有系别，年级，专业，班级，分数这几个字段，现在我想分别以系别，年级，专业，班级统计分数。每10分一个分数段即就是分成0-10,10-20,20-30...90-100统计的结果就显示在相应的字段内即0－10这个分数段就显示统计的0－10的结果，以此...。
我试图用select
[0-20]=sum(case when 分数 between 0 and 20 then 1 else 0 end),
[21-30]=sum(case when 分数 between 21 and 30 then 1 else 0 end),
[31-40]=sum(case when 分数 between 31 and 40 then 1 else 0 end),
[41-50]=sum(case when 分数 between 41 and 50 then 1 else 0 end)
from PTH
group by 班级，专业
但是通不过。
请大侠用VB给以帮助。
[解决办法]
貌似 case 是 sql server 的语法， access 不支持。
[解决办法]
select
sum(case when 分数 between 0 and 20 then 1 else 0 end) as [0-20],
sum(case when 分数 between 21 and 30 then 1 else 0 end) as [21-30],
sum(case when 分数 between 31 and 40 then 1 else 0 end) as [31-40],
sum(case when 分数 between 41 and 50 then 1 else 0 end) as [41-50]
from PTH
group by 班级，专业
[解决办法]
access不支持case语句
[解决办法]
http://ewbi.blogs.com/develops/2006/02/adding_case_to_.html
可能有帮助
[解决办法]
select count(*) from PTH where 分数 <=$下限 and 分数 > $上限
多次执行就行了
[解决办法]
我有办法,但楼主没有说清楚,请把数据库表PTH的完整结构写出来,并且说清楚想用哪些字段进行分组.
[解决办法]
select a.ssum as a20,b.ssum as a30,c.ssum as a40,d.ssum as a50 from (select count(*) from PTH where 分数 <=20 and 分数> 0) a,(select count(*) from PTH where 分数 <=30 and 分数> 20) b....
[解决办法]
access 可以用iif,如下试试：
select
sum(iif(分数 between 0 and 20,1,0)) as [0-20],
......
from PTH
group by 班级,专业
