mysql - mysql中2列按月和年排序

Question

员工表如下...

    Name          DOJ                  DOL
 ............   ...............      ............
    Ram            2014-01-12          2014-02-12
    Kiran          2014-02-05          2014-07-05
    Jhon           2014-01-25          2014-10-01

预期输出是....

    Month          Joining_count     Leaving_count
  ...........     ................  .................
   Jan-2014              2                 0
   Feb-2014              1                 1
      .                  .                 .
      .                  .                 .
      .                  .                 .

我尝试使用以下 Mysql 查询，但无法获得预期的输出请帮助我

        SELECT monthname(current_date) as month, 

        count( `DATE_OF_JOINING`)  as 'Joining_count' , 
        count( `DATE_OF_LEAVING`)  as 'leaving_count' , 

        group by year(`DATE_OF_JOINING`),month('DATE_OF_JOINING`),
        year(`DATE_OF_LEAVING),month(`DATE_OF_LEAVING)

Answer 1

您正在对同一数据执行两种不同的聚合 - 因此您需要将两个聚合查询joined。不幸的是，mysql没有完整的外部连接，因此处理有人离开但没有人加入的月份和有人加入但没有人离开的月份有点麻烦。我用三个连接解决了这个问题——一个查询获取所有可能的日期，另外两个用于每个聚合，尽管 there are other ways .

SELECT    my_table.d, COALESCE(join_count, 0), COALESCE(leave_count, 0)
FROM      (SELECT DISTINCT DATE_FORMAT(doj, '%b-%y') AS d
           FROM   my_table
           UNION
           SELECT DISTINCT DATE_FORMAT(dol, '%b-%y')
           FROM   my_table) dates
LEFT JOIN (SELECT   DATE_FORMAT(doj, '%b-%y') d, COUNT(*) AS join_count
           FROM     my_table
           GROUP BY DATE_FORMAT(doj, '%b-%y')
          ) joins ON dates.d = joins.d
LEFT JOIN (SELECT   DATE_FORMAT(dol, '%b-%y') d, COUNT(*) AS leave_count
           FROM     my_table
           GROUP BY DATE_FORMAT(dol, '%b-%y')
          ) leaves ON dates.d = leaves.d