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);
这样写就差不多了