mysql - 调整两个查询

Question

社区再次问好。

我最近收到了有关问题解决方案的帮助，其中包括 List grouped data by month including months in which there are no results

现在我有相同的场景，但不同之处在于有一个需要评估的条件。我需要调整这个查询

select
    date_format(a.date_created, '%b') month,
    month(a.date_created) pivot,
    sum(case when a.state = 'created' then 1 else 0 end) created,
    sum(case when a.state = 'notified' then 1 else 0 end) notified,
    sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
    sum(case when a.state = 'approved' then 1 else 0 end) approved,
    sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
    sum(case when a.state = 'attended' then 1 else 0 end) attended,
    sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
    count(a.id) as total
from
    activities a
        inner join
    employees e on a.employee_id = e.id
where
    e.id = 8
group by 1, 2 order by pivot desc;

针对此查询

select
    s.name month,
    s.m pivot,
    sum(case when a.state = 'created' then 1 else 0 end) created,
    sum(case when a.state = 'notified' then 1 else 0 end) notified,
    sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
    sum(case when a.state = 'approved' then 1 else 0 end) approved,
    sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
    sum(case when a.state = 'attended' then 1 else 0 end) attended,
    sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
    count(a.id) as total
from (
      SELECT  1 m, 'Jan' AS name 
      UNION SELECT 2, 'Feb' 
      UNION SELECT 3, 'Mar'
      UNION SELECT 4, 'Apr'
      UNION SELECT 5, 'May'
      UNION SELECT 6, 'Jun'
      UNION SELECT 7, 'Jul'
      UNION SELECT 8, 'Aug'
      UNION SELECT 9, 'Sep'
      UNION SELECT 10, 'Oct'
      UNION SELECT 11, 'Nov'
      UNION SELECT 12, 'Dec'
) s
LEFT JOIN activities a
    ON s.m = month(date_created)
group by 1, 2 order by pivot desc;

我不知道如何在上一个问题中有效的解决方案的上下文中添加条件

我测试了以下内容，但没有列出没有结果的月份

select
    s.name month,
    s.m pivot,
    sum(case when a.state = 'created' then 1 else 0 end) created,
    sum(case when a.state = 'notified' then 1 else 0 end) notified,
    sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
    sum(case when a.state = 'approved' then 1 else 0 end) approved,
    sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
    sum(case when a.state = 'attended' then 1 else 0 end) attended,
    sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
    count(a.id) as total
from (
      SELECT  1 m, 'Jan' AS name 
      UNION SELECT 2, 'Feb' 
      UNION SELECT 3, 'Mar'
      UNION SELECT 4, 'Apr'
      UNION SELECT 5, 'May'
      UNION SELECT 6, 'Jun'
      UNION SELECT 7, 'Jul'
      UNION SELECT 8, 'Aug'
      UNION SELECT 9, 'Sep'
      UNION SELECT 10, 'Oct'
      UNION SELECT 11, 'Nov'
      UNION SELECT 12, 'Dec'
) s
LEFT JOIN activities a
  ON s.m = month(date_created)
inner join employees e on a.employee_id = e.id
group by 1, 2 order by pivot desc;

我分享以下内容sqlfiddle包括员工和事件之间的关系

我期望 id 2 的用户结果应如下

再次非常感谢您。

Answer 1

你只是把连接弄乱了。为了将事件和员工之间的联接视为内部联接，必须将其括在括号中。让我们知道它是否有效!

解决办法在这里

select
    s.name month,
    s.m pivot,
    sum(case when a.state = 'created' then 1 else 0 end) created,
    sum(case when a.state = 'notified' then 1 else 0 end) notified,
    sum(case when a.state = 'confirmed' then 1 else 0 end) confirmed,
    sum(case when a.state = 'approved' then 1 else 0 end) approved,
    sum(case when a.state = 'authorized' then 1 else 0 end) authorized,
    sum(case when a.state = 'attended' then 1 else 0 end) attended,
    sum(case when a.state = 'canceled' then 1 else 0 end) canceled,
    count(a.id) as total
from (
      SELECT  1 m, 'Jan' AS name 
      UNION SELECT 2, 'Feb' 
      UNION SELECT 3, 'Mar'
      UNION SELECT 4, 'Apr'
      UNION SELECT 5, 'May'
      UNION SELECT 6, 'Jun'
      UNION SELECT 7, 'Jul'
      UNION SELECT 8, 'Aug'
      UNION SELECT 9, 'Sep'
      UNION SELECT 10, 'Oct'
      UNION SELECT 11, 'Nov'
      UNION SELECT 12, 'Dec'
) s
LEFT JOIN ( activities a INNER JOIN employees e 
                                 ON a.employee_id = e.id AND e.id = 2)
        ON (s.m = month(a.date_created))
group by 1, 2 order by pivot desc;