最近在做一个考勤系统,其中有一个是考勤汇总的模块,当用户在下拉控件中选择考勤汇总时就会在gridview里面显示一定时间段内有哪些同学迟到次数，旷课次数和早退次数，数据库里有事件表(matter)、考勤表(kaoqin),考勤表里面有字段事件ID，事件名称，次数,其中当用户在下拉控件中选择其他事件时比如说选择迟到时用count聚合函数就可以解决，比如是sql= "select stuname as 姓名,count(cishu) as 迟到次数,classname as 班级 from kaoqin,class where matterno=1111 and kaoqin.classno=class.classno and riqi between ' " & Text1.Value & " ' and ' " & Text2.Value & " ' group by stuname,classname "因为在gridview只显示迟到次数就可以了，但是想让迟到次数，早退次数和旷课次数在gridview里面一起显示应该怎么写sql语呢？如果没有其他次数就显示为0，恳请大家帮忙解决。
------解决方案--------------------------------------------------------
select
case 事件id when '迟到 ' then sum(次数) 迟到次数
when '旷工 ' then sum(次数) 迟到矿工次数
from 考勤
------解决方案--------------------------------------------------------
你这样
select stuname as 姓名,count(cishu) as 迟到次数,classname as 班级,count(迟到次数字段) as 迟到次数,count(早退次数字段) as 早退次数,count(旷课次数字段) as 旷课次数
from kaoqin,class
where matterno=1111 and kaoqin.classno=class.classno and riqi between ' " & Text1.Value & " ' and ' " & Text2.Value & " '
group by stuname,classname