php - MySQL 从不同的列中选择计数也使用 group by

Question

我有这张表叫做任务。我想获取每个 assign_date 的总任务计数，以及在该日期每个用户按 complete_date 完成的任务数量。

我想要类似下面的东西

    [{
      user_id:1 ,
      assign_date / compete_date : 2019-09-04,
     assign_task : 2,
    complete_task: 1
    },
{
      user_id:1 ,
      assign_date / compete_date : 2019-09-19,
     assign_task : 1,
    complete_task: 2
    }
]

标签： phpmysqlsqlgroup-by

Answer 1

这是您的查询。usingunion all将获得分配日期和完成日期的计数

select user_id
    , complete_date as [assign_date / compete_date]
    , sum(case when t1.s = 'assigned' then 1 else 0 end) as assign_task
    , sum(case when t1.s = 'complete' then 1 else 0 end) as complete_task
from
    (select user_Id, complete_date, 'complete' as s from task 
    union all
    select user_Id, assign_date, 'assigned' as s from task) t1
group by t1.complete_date, t1.user_id