表结构是这样http://hi.csdn.net/attachment/201203/21/10169717_1332318024lDPi.png我读数据时，我的数据显示
怎么作成类似人家这样
SQL用group by （MembeID）考勤人，只能读一条的，要怎么把所有的都读出来好作统计？可按月统计，也可按年统计，有没有方法或者代码？
------解决方案--------------------------------------------------------
可以统计每个人每个月考勤记录的条数
- SQL code
select MemberID,COUNT(1) AS 数据条数,SUBSTRING(convert(varchar(10),AttendanceDate,23),1,7) as 考勤年月 from 考勤表 group by MemberID,SUBSTRING(convert(varchar(10),AttendanceDate,23),1,7)
------解决方案--------------------------------------------------------
select MemberID,COUNT(1) AS 数据条数,YEAR(AttendanceDate) as 考勤年 from 考勤表 group by MemberID,YEAR(AttendanceDate)
这个可以统计每年的记录条数
------解决方案--------------------------------------------------------
select MemberID,COUNT(1) AS 数据条数,SUBSTRING(convert(varchar(10),AttendanceDate,23),1,7) as 考勤年月,count(case checkInStatus when 0 then 1 end) as 未考勤,count(case checkInStatus when 1 then 1 end) as正常,
count(case checkInStatus when 2 then 1 end ) as 临界,
count(case checkInStatus when 3 then 1 end ) as 迟到 from 考勤表 group by MemberID,SUBSTRING(convert(varchar(10),AttendanceDate,23),1,7);
这样写就差不多了