力扣上有这么一道SQL试题求最晚的入职日期。这里简单创建一个表。
CREATE TABLE employees
(name VARCHAR(10) NOT NULL,
in_date date NOT NULL)
--indate表示入职日期
INSERT INTO employees
VALUES('BOB','2019-11-20'),
('JAM','2020-01-02'),
('MING','2020-02-02'),
('XIA','2020-02-25'),
('AIM','2020-02-25')
在确认入职日期没有重复时,mysql可以用 limit 函数
SELECT name , in_date FROM employees
ORDER BY in_date DESC LIMIT 0,1
如果有相同的最晚入职日期,通用写法可以这样写
SELECT name , in_date FROM employees
WHERE in_date = SELECT MAX(in_date)
FROM employees
但是SQL Sever目前好像还不支持limit函数,只支持top。那么对于上述问题也可以改写为(同样是在没有重复的情况下)
SELECT TOP 1 * FROM employees ORDER BY in_date DESC
当然通用情况下也可以使用子查询,改写成
SELECT * FROM employees
WHERE in_date = (SELECT TOP 1 in_date
FROM employees
ORDER BY in_date desc)
这里在拓展一下,如果是要取倒数第三入职的信息呢?mysql很方便可以使用limit
SELECT name , in_date FROM employees
ORDER BY in_date DESC LIMIT 2,1
但sql不支持,所以sql实现可能要麻烦一些,第一种可以使用top,首先倒序取出倒数后三名的信息,再用top取出正序排名第一的就是入职倒数第三晚的。这里表述可能不清,可以直接看代码。
SELECT TOP 1 *
FROM (SELECT TOP 3 *
FROM employees
ORDER BY in_date DESC) t
ORDER BY in_date
第二种可以用到窗口函数。rank(),dense_rank(),row_number()关于这三者排序的区别可以参考文章
https://blog.csdn.net/m0_46412065/article/details/104951592
代码如下
SELECT name,in_date
FROM (SELECT name,in_date,
DENSE_RANK()OVER(ORDER BY in_date DESC) as req
FROM employees) t
WHERE req=3
最后补充一下,第二种方法比较通用,不管有没有重复数据都可行,且拓展性较高。如果将dense_rank改为row_number则可以实现任意取行,比如取倒数第8行,可以自行尝试。