mysql - 获取单个 sql 查询中存在和不存在的总数

Question

这是我的考勤表详细信息 Emp_ID(varchar),pdate(datetime),attendance(char(2))

我想在单个查询中获取按emp_id分组的特定日期范围的总出勤天数、缺席总次数和出席总次数

Answer 1

只是一个示例，向您展示如何处理您的数据

declare @t table (id int,da date,attend Varchar(2))
insert into @t (id,da,attend) values (1,'20141011','P'),
 (1,'20141012','A'),
  (1,'20141013','P'),
   (1,'20141014','A'),
   (1,'20141014','P')

   select ID,COUNT(da)Total,
   (select COUNT(da) from @t where attend = 'A')as Absent,
   (select COUNT(da) from @t where attend = 'P')as Present from @t
   group by id