mysql - 当一个日期为空时，两个日期之间的月、年

Question

我有一个所有员工的指定表。每当角色发生变化时，就会有新的条目。就像

'Sr. Engg'- start date 2013-04-01 and end date 2016-03-31

现在同一个人晋升为

'Lead Engg' -start date 2016-04-01 and end date'0000-00-00'

因为我不知道目前职位的结束日期。

我正在使用此表格在工资单中显示它。所以当他想要 March 2016 的工资单时，那么他的工资单应该显示他的角色为Sr Engg当他想要Apr 2016时，那么角色应该是“Lead Engg”。

对于我来说，使用一个查询，但它不起作用。

id  empid   role        startdate   enddate
1   1     Sr Engineer   2013-04-01  2016-03-31
3   2     Asst.Manager  2013-04-01  2016-03-31
2   1     Lead Engg     2016-04-01  0000-00-00
4   2     Manager       2016-04-01  0000-00-00

SELECT * FROM ROLE 
WHERE EMPLOYEE = 1 
    AND ('2016-03' between DATE_FORMAT(STARTDATE,"%Y-%m") AND DATE_FORMAT(ENDDATE,"%Y-%m"));

它返回employee = 1的记录, mar 2016 ，但对于同一个员工 apr 2016 ，它不会返回任何内容。

创建声明:

CREATE TABLE `role` (
  `id` int(11) NOT NULL,
  `employee` bigint(20) DEFAULT NULL,
  `role` varchar(45) DEFAULT NULL,
  `startdate` date DEFAULT NULL,
  `enddate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Fk_role_employee_idx` (`employee`),
  CONSTRAINT `Fk_role_employee` FOREIGN KEY (`employee`) REFERENCES `employee` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入语句:

INSERT INTO `simpayroll`.`role`
(`id`,
`employee`,
`role`,
`startdate`,
`enddate`)
VALUES
(<{id: }>,
<{employee: }>,
<{role: }>,
<{startdate: }>,
<{enddate: }>);

Answer 1

我建议您使用这样的查询:

SELECT
  *
FROM
  ROLE
WHERE
  EMPLOYEE = 1 AND (
    STARTDATE>='2016-03-01'
    AND (ENDDATE<=LAST_DAY('2016-03-01') OR ENDDATE='0000-00-00')
  );

我知道在这里我们重复相同的日期(该月的第一天)两次，但这将是性能最高的，因为我们将日期与没有字符串转换的日期进行比较，并且可以利用索引(如果存在) .

如果ENDDATE为空而不是更可取的0000-00-00，您可以将其修改为:

    ....
    AND (ENDDATE<=LAST_DAY('2016-03-01') OR ENDDATE IS NULL)