当用户登录到我的应用程序时,该行将插入登录日志表中。我希望在用户登录系统时打印最后一次登录时间。如何获取上次登录时间?
我尝试过max(time)
,但它给了我当前登录时间,但我想要以前的时间。我怎么能拿到这个?
这是我的问题:
select login_time
from tblname
where emp_code = 123
and login_time = (select max(LOGIN_TIME)
from tblname
where emp_code = 123)
您可以使用ROW_NUMBER
:
WITH Cte AS(
SELECT *,
ROW_NUMBER() OVER(ORDER BY LOGIN_TIME DESC) AS rn
FROM tblname
WHERE emp_code = 123
)
SELECT LOGIN_TIME FROM CTE WHERE rn = 2
不使用CTE:
SELECT LOGIN_TIME
FROM (
SELECT *,
ROW_NUMBER() OVER(ORDER BY LOGIN_TIME DESC) AS rn
FROM tblname
WHERE emp_code = 123
) t
WHERE rn = 2
您还可以使用Oracle的ROWNUM
:
SELECT *
FROM (
SELECT login_time
FROM tblname
WHERE emp_code = 123
ORDER BY login_time DESC
) t
WHERE ROWNUM = 2
尝试使用类似这样的WHERE子查询:
SELECT MAX(login_time)
FROM tblname
WHERE emp_code=123 AND
login_time NOT IN
(SELECT MAX(login_time)
FROM tblname
WHERE emp_code=123
)