SQL select案例分组依据
SQL select案例分组依据
提问于 2016-04-29 02:55:05
我有一个表'LIST_USERS‘。
表描述-
USER_ID NUMBER(8)
LOGIN_ID VARCHAR2(8)
CREATE_DATE TIMESTAMP(6)
LOGIN_DATE TIMESTAMP(6)表数据-
USER_ID LOGIN_ID CREATE_DATE LOGIN_DATE
---------------------------------------------------
101 test1 04/24/2016 null
102 test1 04/24/2016 04/29/2016
103 test2 04/25/2016 null
104 test2 04/26/2016 null
105 test3 04/27/2016 04/28/2016
106 test3 04/27/2016 04/29/2016
107 test4 04/28/2016 04/29/2016
987 test5 04/29/2016 null
109 test5 04/29/2016 null
108 test5 04/29/2016 04/29/2016条件-我需要从基于最大LOGIN_DATE的'LIST_USERS‘表中获取USER_ID和LOGIN_ID。如果LOGIN_DATE为null，则需要根据最大CREATE_DATE获取记录。
我需要得到下面的结果-
USER_ID LOGIN_ID
---------------------
102 test1
104 test2
106 test3
107 test4
108 test5我正在使用下面的查询。但它只会给我LOGIN_ID和'Login_Or_Create_Date‘，但我需要USER_ID和LOGIN_ID。有没有办法让我像上面显示的结果一样得到USER_ID？
select LOGIN_ID,
(case when max(LOGIN_DATE) is null then max(CREATE_DATE)
else max(LOGIN_DATE) end) as Login_Or_Create_Date
from LIST_USERS;回答 2
Stack Overflow用户
发布于 2016-04-29 03:01:34
试试这个：
SELECT USER_ID, LOGIN_ID
FROM (
SELECT USER_ID, LOGIN_ID,
ROW_NUMBER() OVER (PARTITION BY LOGIN_ID
ORDER BY COALESCE(LOGIN_DATE, CREATE_DATE) DESC) AS rn
FROM LIST_USERS) t
WHERE t.rn = 1Stack Overflow用户
发布于 2016-04-29 03:04:01
听起来像是keep dense_rank的工作
select min(user_id) keep (dense_rank last order by coalesce(login_date, create_date))
as user_id,
login_id
from list_users
group by login_id
order by user_id;last保存最近登录/创建日期的记录；coalesce()首先获取登录日期，如果登录日期为空，则返回到创建日期(当然，您也可以使用nvl()代替)。你也可以做first和order by desc -结果是一样的(如果没有空值，看起来不应该有空值)，但是当你想要最新的日期时，last感觉更直观。在CTE中使用数据的演示：
with list_users(user_id, login_id, create_date, login_date) as (
select 101, 'test1', date '2016-04-24', null from dual
union all select 102, 'test1', date '2016-04-24', date '2016-04-29' from dual
union all select 103, 'test2', date '2016-04-25', null from dual
union all select 104, 'test2', date '2016-04-26', null from dual
union all select 105, 'test3', date '2016-04-27', date '2016-04-28' from dual
union all select 106, 'test3', date '2016-04-27', date '2016-04-29' from dual
union all select 107, 'test4', date '2016-04-28', date '2016-04-29' from dual
)
select min(user_id) keep (dense_rank last order by coalesce(login_date, create_date))
as user_id,
login_id
from list_users
group by login_id
order by user_id;
USER_ID LOGIN
---------- -----
102 test1
104 test2
106 test3
107 test4并使用修改后的数据：
with list_users(user_id, login_id, create_date, login_date) as (
select 101, 'test1', date '2016-04-24', null from dual
union all select 102, 'test1', date '2016-04-24', date '2016-04-29' from dual
union all select 103, 'test2', date '2016-04-25', null from dual
union all select 104, 'test2', date '2016-04-26', null from dual
union all select 105, 'test3', date '2016-04-27', date '2016-04-28' from dual
union all select 106, 'test3', date '2016-04-27', date '2016-04-29' from dual
union all select 107, 'test4', date '2016-04-28', date '2016-04-29' from dual
union all select 987, 'test5', date '2016-04-29', null from dual
union all select 109, 'test5', date '2016-04-29', null from dual
union all select 108, 'test5', date '2016-04-29', date '2016-04-29' from dual
)
select min(user_id) keep (dense_rank last order by coalesce(login_date, create_date))
as user_id,
login_id
from list_users
group by login_id
order by user_id;
USER_ID LOGIN
---------- -----
102 test1
104 test2
106 test3
107 test4
108 test5页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/36923244
复制相关文章
相似问题
腾讯云开发者
