使用SQL选择查询查找用户登录和注销活动(Oracle)

y0u0uwnf 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(150)

我有一个UserActivity表，其中包含UserID、DateTime和Activity列。表结构为：

Id    UserID         DateTime             Activity
123    abc      2023-08-24 09:30:50        Login
125    abc      2023-08-24 09:34:23        Login
150    abc      2023-08-24 09:38:04        Logout
178    abc      2023-08-24 13:23:12        Login
200    abc      2023-08-24 15:50:34        Logout
300    abc      2023-08-24 16:43:02        Login
505    abc      2023-08-25 10:32:12        Login
507    abc      2023-08-25 12:36:44        Logout

我要输出，这将返回所有的登录日期与它的通讯注销日期。如果特定登录的注销日期时间不存在，则它应该为空。

输出：

UserID        LoginDateTime            LogoutDateTime
 abc       2023-08-24 09:30:50
 abc       2023-08-24 09:34:23       2023-08-24 09:38:04
 abc       2023-08-24 13:23:12       2023-08-24 15:50:34
 abc       2023-08-24 16:43:02
 abc       2023-08-25 10:32:12       2023-08-25 12:36:44

我已经尝试了下面的查询，但它没有给出正确的输出.

*SELECT
login.UserID,
login.DateTime As Login,
LogOut.DateTime As Logout
from
  UserActivity login
Left JOIN
  UserActivity LogOut ON Login.UserID=Logout.UserID
  AND login.Activity='login.label'
  AND Logout.Activity='logout.label'
  AND TO_CHAR(Login.DateTime, 'YYYY-MM-DD') = TO_CHAR(Logout.DateTime, 'YYYY-MM-DD')
  AND Login.DateTime<Logout.DateTime
Where login.UserID='abc'
  Order by login.UserID,login.DateTime;*

请帮助实现上述结果。

oracle

来源：https://stackoverflow.com/questions/76975880/find-user-login-and-logout-activities-using-sql-select-query-oracle

2条答案

按热度按时间

hjzp0vay1#

不需要自联接表;使用LEAD解析函数更有效：

SELECT userid,
       datetime as LoginDateTime,
       LogoutDateTime
FROM   (
  SELECT userid,
         datetime,
         activity,
         LEAD(CASE activity WHEN 'Logout' THEN datetime END) OVER (
           PARTITION BY userid
           ORDER BY datetime
         ) AS logoutdatetime
  FROM   useractivity u
)
WHERE  activity = 'Login';

其中，对于样本数据：

CREATE TABLE useractivity (
  Id       NUMBER,
  UserID   VARCHAR2(20),
  DateTime DATE,
  Activity VARCHAR2(6)
);

INSERT INTO useractivity (id, userid, datetime, activity)
  SELECT 123, 'abc', TIMESTAMP '2023-08-24 09:30:50', 'Login'  FROM DUAL UNION ALL
  SELECT 125, 'abc', TIMESTAMP '2023-08-24 09:34:23', 'Login'  FROM DUAL UNION ALL
  SELECT 150, 'abc', TIMESTAMP '2023-08-24 09:38:04', 'Logout' FROM DUAL UNION ALL
  SELECT 178, 'abc', TIMESTAMP '2023-08-24 13:23:12', 'Login'  FROM DUAL UNION ALL
  SELECT 200, 'abc', TIMESTAMP '2023-08-24 15:50:34', 'Logout' FROM DUAL UNION ALL
  SELECT 300, 'abc', TIMESTAMP '2023-08-24 16:43:02', 'Login'  FROM DUAL UNION ALL
  SELECT 505, 'abc', TIMESTAMP '2023-08-25 10:32:12', 'Login'  FROM DUAL UNION ALL
  SELECT 507, 'abc', TIMESTAMP '2023-08-25 12:36:44', 'Logout' FROM DUAL;

输出：
| userId|登录时间|登录日期时间|
| --|--|--|
| ABC| 2023-08-24 09：30：50| * 空 *|
| ABC| 2023-08-24 09：34：23| 2023-08-24 09：38：04|
| ABC| 2023-08-24 13：23：12| 2023-08-24 15：50：34|
| ABC| 2023-08-24 16：43：02| * 空 *|
| ABC| 2023-08-25 10：32：12| 2023-08-25 12：36：44|
fiddle

赞(0）回复(0）举报 2023-10-16

nukf8bse2#

SELECT
    L.UserID,
    L.DateTime AS LoginDateTime,
    MAX(R.DateTime) AS LogoutDateTime
FROM
    UserActivity L
LEFT JOIN
    UserActivity R ON L.UserID = R.UserID
                    AND R.Activity = 'Logout'
                    AND R.DateTime > L.DateTime
WHERE
    L.UserID = 'abc'
    AND L.Activity = 'Login'
GROUP BY
    L.UserID, L.DateTime
ORDER BY
    L.UserID, L.DateTime;

赞(0）回复(0）举报 2023-10-16

我来回答

使用SQL选择查询查找用户登录和注销活动(Oracle)

2条答案

相关问题

热门标签

最新问答