select distinct user_id from(select distinct trunc(login_time) time, user_id,lag(trunc(login_time), 1, null) over(partition by user_id order by user_id,login_time) timetrunc from userLoginTable) where time=timetrunc+1 order by user_id ;
自己去环境试试就知道对错了,多测试测试就知道了 再提供一种不用分析函数的,这个可能好理解一点,但是应该没有分析函数执行速度快 with T AS (SELECT DISTINCT USERID,TRUNC(login_time) login_time FROM userLoginTable) SELECT DISTINCT USERID FROM T T1 WHERE EXISTS(SELECT 1 FROM T WHERE USERID=T1.USERID AND login_time=T1.login_time-1)
再提供一种不用分析函数的,这个可能好理解一点,但是应该没有分析函数执行速度快
with T AS (SELECT DISTINCT USERID,TRUNC(login_time) login_time FROM userLoginTable)
SELECT DISTINCT USERID
FROM T T1
WHERE EXISTS(SELECT 1 FROM T
WHERE USERID=T1.USERID
AND login_time=T1.login_time-1)