給定表 login_logs 與列 UserName (Varchar)、LoginDate (Datetime) 是成功的 (Boolean)。撰寫一個查詢，回傳用戶名、過去 30 天用戶成功登錄的次數、過去 60 天用戶成功登錄的次數
SELECT UserName, LoginDate, was_successful
FROM login_logs
WHERE LoginDate >= DATEADD(day,-30,GETDATE())
AND LoginDate >= DATEADD(day,-60,GETDATE())
AND was_successful = 'true';
我究竟做錯了什么？
uj5u.com熱心網友回復：
where 子句需要過濾大于today - 60 dayswhich 還涵蓋的日期today - 30 days。然后使用條件聚合：
select username
, last_60 = count(*)
, last_30 = count(case when logindate >= dateadd(day, -30, cast(current_timestamp as date)) then 1 end)
from login_logs
where logindate >= dateadd(day, -60, cast(current_timestamp as date)) and was_successful = 'true'
group by username
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/441798.html
上一篇：while回圈函式聯合函式結束