MySQL查询帮助要求

Question

我正在使用 MySQL 数据库。我有员工休假表，其中包含有关员工休假的信息。

请查找表格详细信息:

CREATE TABLE IF NOT EXISTS `APPLY_LEAVE` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `EMP_ID` varchar(100) NOT NULL,
  `TYPE_OF_LEAVE` varchar(100) NOT NULL,  
  `DAYS` varchar(100) NOT NULL,
  `REASON` varchar(200) NOT NULL,  
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `STATUS` tinyint(2) NOT NULL,
  `CREATED_ON` date NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

--
-- Dumping data for table `APPLY_LEAVE`
--

INSERT INTO `APPLY_LEAVE` (`ID`, `EMP_ID`, `TYPE_OF_LEAVE`, `DAYS`, `REASON`, `START_DATE`, `END_DATE`, `STATUS`, `CREATED_ON`) VALUES
(1, 'EMP001', 'SL', '2', 'Sick Leave', '2018-11-30', '2018-12-01', 1,'2018-11-06'),
(2, 'EMP002', 'EL', '1', 'Personal', '2018-12-13', '2018-12-13', 1,'2018-11-09'),
(3, 'EMP003', 'CL', '2', 'Casual Leave due to Birthday', '2018-08-31', '2018-09-01', 1,'2018-08-20'),
(4, 'EMP001', 'CL', '3', 'Casual Leave', '2018-12-04', '2018-12-06', 1,'2018-11-27'),
(5, 'EMP002', 'SL', '4', 'Sick Leave', '2018-09-10', '2018-09-13', 1,'2018-10-04'),
(6, 'EMP003', 'SL', '3', 'Sick Leave', '2018-10-30', '2018-11-01', 1,'2018-11-25');

需要输出:我想生成报告/excel 以根据休假类型接收按月员工休假数据的信息，即(按月，休假类型数据)格式应如下:

要求:我希望 MySQL 查询能够按月获取附加结果，由员工获取的休假类型数据(SL/CL/EL)。

<小时/>

查询尝试:

SELECT EMP_ID,
SUM(CASE WHEN TYPE_OF_LEAVE = 'EL' AND MONTH( START_DATE ) =11 THEN DAYS ELSE 0 END ) AS EL_NOV,
SUM(CASE WHEN TYPE_OF_LEAVE = 'CL' AND MONTH( START_DATE ) =11 THEN DAYS ELSE 0 END ) AS CL_NOV,
SUM(CASE WHEN TYPE_OF_LEAVE = 'SL' AND MONTH( START_DATE ) =11 THEN DAYS ELSE 0 END ) AS SL_NOV,
SUM(CASE WHEN TYPE_OF_LEAVE = 'LOP' AND MONTH( START_DATE ) =11 THEN DAYS ELSE 0 END ) AS LOP_NOV,
SUM(CASE WHEN TYPE_OF_LEAVE = 'EL' AND MONTH( START_DATE ) =12 THEN DAYS ELSE 0 END ) AS EL_DEC,
SUM(CASE WHEN TYPE_OF_LEAVE = 'CL' AND MONTH( START_DATE ) =12 THEN DAYS ELSE 0 END ) AS CL_DEC,
SUM(CASE WHEN TYPE_OF_LEAVE = 'SL' AND MONTH( START_DATE ) =12 THEN DAYS ELSE 0 END ) AS SL_DEC,
SUM(CASE WHEN TYPE_OF_LEAVE = 'LOP' AND MONTH( START_DATE ) =12 THEN DAYS ELSE 0 END ) AS LOP_DEC
FROM APPLY_LEAVE
GROUP BY EMP_ID

面临的问题:即一名员工在周五和周六请假(即 EMP001 在 2018 年 11 月 30 日至 2018 年 12 月 1 日期间领取了 SL)(周五是最后一个日期的月份，周六是该月的第一个日期，我正在将单个记录插入表中。当员工申请休假时。这里的结果应该是

EMP001 - SL

11 月 - 1 次休假

12 月 - 1 次休假

如何编写这个 MySQL 查询？

Answer 1

亲爱的 Dipti，请在下面的查询中找到所需的结果。

     SELECT 
  * 
FROM 
  (
    SELECT 
      EMP_ID, 
      START_DATE as date_day, 
      TYPE_OF_LEAVE, 
      SUM(
        if(
          MONTH(START_DATE) <> MONTH(END_DATE), 
          (
            day(
              last_day(START_DATE)
            )+ 1 - day(START_DATE)
          ), 
          days
        )
      ) as DAYS 
    FROM 
      APPLY_LEAVE 
    GROUP BY 
      MONTH(START_DATE), 
      MONTH(END_DATE), 
      EMP_ID 
    UNION ALL 
    SELECT 
      EMP_ID, 
      END_DATE as date_day, 
      TYPE_OF_LEAVE, 
      SUM(
        if(
          MONTH(START_DATE) <> MONTH(END_DATE), 
          DAY(END_DATE), 
          0
        )
      ) as DAYS 
    FROM 
      APPLY_LEAVE 
    GROUP BY 
      MONTH(START_DATE), 
      MONTH(END_DATE), 
      EMP_ID
  ) as a 
WHERE 
  a.DAYS > 0;