SELECT LAST_LOGIN, LAST_LOGIN_ONE_MONTH_AGO, S_L.TIME, S_L.TIME_ONE_MONTH_AGO
FROM STUDENT_LAST_LOGIN S_L_L
INNER JOIN STUDENT_LOGIN S_L on S_L_L.id = S_L.id
where S_L_L.date <  DATEADD(month, -1, GETDATE())

你需要这样写你的查询。

您需要按如下方式使用windows函数：

SELECT * FROM
(SELECT SLL.STUDENT_ID, 
        SLL.DATE LAST_LOGIN,
        SL.DATE LAST_LOGIN_ONE_MONTHE_AGO, 
        SLL.TIME,
        SL.TIME TIME_ONE_MONTH_AGO,
        ROW_NUMBER() OVER (PARTITION BY SLL.STUDENT_ID ORDER BY SL.DATE DESC NULLS LAST) AS RN 
   FROM STUDENT_LAST_LOGIN SLL LEFT JOIN STUDENT_LOGIN SL
     ON SL.STUDENT_ID = SLL.STUDENT_ID
    AND TRUNC(SL.DATE) = ADD_MONTHS(TRUNC(SLL.DATE),-1)
)
WHERE RN = 1

我只能推测，你想要的是最近的登录，然后是前一个日历月的最新登录。我建议条件聚合：

select sll.student_id,
       max(case when month_seqnum = 1 then last_login end),
       max(case when month_seqnum = 2 then last_login end),
       max(case when month_seqnum = 1 then time end),
       max(case when month_seqnum = 2 then time end)
from (select sll.*,
             row_number() over (partition by student_id, to_char(date, 'YYYY-MM')
                                order by date desc
                               ) as seqnum,
             dense_rank() over (partition by student_id order by to_char(date, 'YYYY-MM')) as month_seqnum
      from student_last_login sll
     ) sll
where month_seqnum in (1, 2) and seqnum = 1
group by student_id;

我认为这会返回您指定的值。