To query all employees with the third to last joining date in the employee table, we must take into account the employees who joined on the same day, so use to remove duplicates and first find out the third to last date , and then use this date To query all equal employee information
SELECT *FROM `employees`WHERE `hire_date` = ( SELECT DISTINCT `hire_date` FROM `employees` ORDER BY `hire_date` DESC LIMIT 2, 1)
CREATE
drop table if exists `employees`;CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,)
INSERT
INSERT INTO `employees` ( `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)VALUES ( 1, '2021-08-04', 'Georgi', 'Facello', 'M', '1980-06-21' );INSERT INTO `employees` ( `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)VALUES ( 2, '2021-08-20', 'Bezalel', 'Simnel', 'F', '1985-11-21' );INSERT INTO `employees` ( `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)VALUES ( 3, '2021-08-20', 'Parto', 'Bamford', 'M', '1986-08-28' );INSERT INTO `employees` ( `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)VALUES ( 4, '2021-08-20', 'Chirstian', 'Koblick', 'M', '1986-12-01' );
Recommended study: "mysql video tutorial"
The above is the detailed content of MySQL small exercise: How to query all the data on the third to last day in the table. For more information, please follow other related articles on the PHP Chinese website!