excuse me, why do you query out 1 where the data is greater than 0, but 2 that place must use > = to go, with > there is no data?
create a table:
CREATE TABLE `contract` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`emp_id` int(11) DEFAULT NULL COMMENT "id",
`sign_time` datetime DEFAULT NULL COMMENT "",
`end_time` datetime DEFAULT NULL COMMENT "",
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT="";
CREATE TABLE `employee_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL COMMENT "",
`stage` varchar(100) DEFAULT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT="";insert data:
INSERT INTO `contract` (`id`, `emp_id`,`sign_time`, `end_time`) VALUES ("25", "83", "2018-11-21 00:00:00", "2018-12-01 15:27:00");
INSERT INTO `contract` (`id`, `emp_id`,`sign_time`, `end_time`) VALUES ("26", "94", "2018-11-21 00:00:00", "2018-12-01 15:23:00");
INSERT INTO `employee_detail` (`id`, `name`, `stage`) VALUES ("83", "", "1");
INSERT INTO `employee_detail` (`id`, `name`, `stage`) VALUES ("94", "", "1");
sql query statement:
SELECT
c.*
FROM
contract c
JOIN employee_detail e ON c.emp_id = e.id
WHERE
e.stage != - 1
AND (
TIMESTAMPDIFF(
MINUTE,
"2018-11-30 09:18:23",
c.end_time
)
) >= 0
AND TIMESTAMPDIFF(
MONTH,
"2018-11-30 09:18:23",
c.end_time
) = 0