mysql - 使用 codeigniter 通过查询在组中添加多个计数

Question

我的 table 看起来像这样:

| id | employee_id |   status  |
   1      2           Present
   2      2           Present
   3      2           Absent
   4      2           Holiday

这是我的查询:

    $this->db->select('employee_id,COUNT(*) as num_present',FALSE);
    $this->db->group_by('employee_id'); 
    $this->db->group_by('status'); 
    $query  =  $this->db->get('xin_payroll_temp'); 
    $data = $query->result();

    print json_encode($data);

查询的输出:

  {
    "employee_id": "2",
    "num_present": "2"
  },
  {
    "employee_id": "2",
    "num_present": "1"
  },
  {
    "employee_id": "2",
    "num_present": "1"
  }

我的查询对 Present 、 Absent 、 Holiday 进行计数，但问题是它将它们显示在单独的数组数据中。

我想要的是以这种格式将其显示在单个数组中:

{
 "employee_id": "2",
 "num_present": "2",
 "num_absent" : "1",
 "num_holiday" : "1"
}

如有任何帮助，我们将不胜感激。谢谢。

Answer 1

经过一番谷歌搜索，终于解决了这个问题。这是我用来实现我想要的格式的查询:

        $query = $this->db->select('employee_id, sum(case when status = "Present" then 1 else 0 end ) As present_count, sum(case when status = "Absent" then 1 else 0 end ) As absent_count, sum(case when status = "Holiday" then 1 else 0 end ) As holiday_count',FALSE)
          ->from("xin_payroll_temp")
           ->group_by('employee_id')
           ->get();

    $data = $query->result();

    print json_encode($data);