牛客每个人最近的登录日期(二)_

Mysql

牛客475559997号

SELECT
user.name AS u_n
,client.name AS c_n
,date
FROM login
JOIN user ON user.id=login.user_id
JOIN client ON client.id=login.client_id
WHERE
(login.user_id,login.date) in
(
SELECT
user_id
,MAX(date)
FROM login
GROUP BY user_id
)
ORDER BY
u_n

发表于 2025-10-23 16:13:08 回复(0)

Mysql

像初雪一样遇见你

with t1 as (
select id,user_id,client_id,date
from login x
where date=(select max(date) from login where user_id=x.user_id)
)
select y.name as u_n,z.name as c_n,date
from t1
join user y 
on t1.user_id=y.id 
join client z 
on t1.client_id=z.id
order by 1

发表于 2025-10-15 18:11:35 回复(0)

Mysql

牛客175881197号

窗口函数解法：

select
u_n,
c_n,
date
from
(
select
u.name as u_n,
c.name as c_n,
l.date,
rank()over(partition by l.user_id order by l.date desc) as rn
from
login as l
inner join user as u on l.user_id = u.id
inner join client as c on l.client_id = c.id
) as data
where
rn = 1
order by
u_n asc

发表于 2025-10-14 13:36:30 回复(0)

Mysql

牛客922811000号

select
    u.name,
    c.name,
    date
from
    login l
left join user u on l.user_id = u.id
left join client c on l.client_id = c.id
where
    (user_id,date) in (select
                                                user_id,
                                                max(date) date
                                            from
                                                login l
                                            group by
                                                user_id)
order by
    u.name

发表于 2025-08-06 16:03:29 回复(0)

Mysql

jacky酱

select

u.name as u_n,

c.name as c_n,

date

from (

select

user_id,

client_id,

date,

row_number() over (partition by user_id order by date desc) as rk

from login

) as a

join user u

on a.user_id = u.id

join client c

on a.client_id = c.id

where rk = 1

order by u.name;

发表于 2025-07-15 14:30:32 回复(0)

Mysql

专注当下喲

select b.name as u_n,c.name as c_n,a.date

from login a

inner join user b

inner join client c

inner join (select user_id,max(date) as date from login

group by user_id ) d

on a.user_id=b.id

and a.client_id=c.id

and a.user_id=d.user_id

where a.date=d.date

order by b.name

11行代码搞定！

发表于 2025-03-27 15:53:10 回复(0)

Mysql

某航

select u.name u_n,c.name c_n, date

from login l join user u on l.user_id = u.id

join client c on l.client_id = c.id

where (user_id,date) in (select user_id,max(date) from login group by user_id)

order by u.name

发表于 2025-03-26 15:37:51 回复(0)

Mysql

JoinRan

这个题有点意思，一不注意就踩坑：
查询数据：
fh pc 2020-10-13
wangchao ios 2020-10-13

SQL（错误）
select
u.name as u_n,
c.name as c_n,
max(date) as date
from
login l
inner join `user` u on
u.id = l.user_id
inner join client c on
c.id = l.client_id
group by
l.user_id
order by
u.name;

发表于 2025-03-10 10:31:23 回复(0)

Mysql

勇敢妞妞不怕困难2号

窗口函数果然yyds

with res as (select

u.name as u_n,

c.name as c_n,

date,

row_number() over(partition by u.name order by date desc) as ranking

from login l

join user u on l.user_id=u.id

join client c on l.client_id=c.id)

select

u_n,

c_n,

date

from res where ranking=1

发表于 2025-03-03 19:21:15 回复(0)

Mysql

AAAAA实习战神

写了第一解法发现错误，理由是c.name无法聚合，因为里面的值不是一样的

select u.name u_n, c.name c_n, max(date)
from login l left join user u on l.user_id = u.id 
    left join client c on l.client_id = c.id
group by l.user_id
order by u_n

修正后用其他解法

select u.name u_n, c.name c_n, date
from login l left join user u on l.user_id = u.id 
    left join client c on l.client_id = c.id
where (user_id, date) in (select user_id, max(date) from login group by user_id) 
order by u_n

发表于 2025-02-22 19:47:55 回复(1)

Mysql

刷牛客的coder很爱吃香菜

select
    user.name u_n,
    client.name c_n,
    login.date date
from
    login
    inner join user on login.user_id = user.id
    join client on login.client_id = client.id
where
    (user.name, date) in (
        select
            u.name,
            max(date)
        from
            login l
            inner join user u on l.user_id = u.id
            join client c on l.client_id = c.id
        group by
            u.name
    )
order by
    u_n

发表于 2025-01-07 18:04:33 回复(0)

Mysql

R_RSQL

利用窗口函数给每个user_id按时间倒序排序，取出排名第一即可
select name_1 u_n
,name_2 c_n
,date
from(
select u.name name_1
,c.name name_2
,date
,row_number()over(partition by user_id order by date desc) r --根据date倒序排序 r=1即最近时间
from login l
join user u
on l.user_id=u.id
join client c
on l.client_id=c.id
) a
where r =1
order by u_n

发表于 2024-12-24 15:30:17 回复(0)

Mysql

牛客314124244号

with

t as (

SELECT

l.id,

l.user_id,

l.date,

l.client_id,

DENSE_RANK() OVER (

PARTITION BY

l.user_id

ORDER BY

l.date DESC

) AS ranking

FROM

login l

)

SELECT

u.name as u_n,

c.name as c_n,

t.date

from

t

JOIN client c on c.id = t.client_id

join user u on u.id = t.user_id

WHERE

t.ranking = 1

order by u.name asc

发表于 2024-10-17 10:06:17 回复(0)

Mysql

雨季不再来__

SELECT u.name u_n, c.name c_n, date

FROM login l

JOIN user u ON l.user_id=u.id

JOIN client c ON l.client_id=c.id

WHERE (l.user_id,date) IN

(

SELECT user_id,max(date)

FROM login

GROUP BY user_id

)

order by u_n

发表于 2024-09-19 10:11:03 回复(0)

Mysql

只想搞钱的王心凌男孩很想去重庆

select t1.name as u_n,t3.name as c_n,date_1

from

(

select user_id,name,date_1

from

(

select user_id,max(date) as date_1

from login

group by user_id

)a

left join

(

select id,name from user

)b

on a.user_id = b.id

)t1

inner join

(

select user_id,client_id,date from login

)t2

on t1.user_id = t2.user_id and t1.date_1 = t2.date

left join

(

select id,name from client

)t3

on t2.client_id = t3.id

order by t1.name

发表于 2024-09-08 20:40:57 回复(0)

Mysql

夕流夕流吸溜吸溜

select u.name,c.name,l.date
from login l left join user u on u.id = l.user_id 
    left join client c on c.id = l.client_id
where (l.user_id,l.date) in(
    select user_id,max(date)
    from login
    group by user_id
)
order by u.name asc

发表于 2024-08-21 14:46:54 回复(0)

牛客每个人最近的登录日期(二)

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题

id	user_id	client_id	date
1	2	1	2020-10-12
2	3	2	2020-10-12
3	2	2	2020-10-13
4	3	2	2020-10-13

id	name
1	tm
2	fh
3	wangchao

id	name
1	pc
2	ios
3	anroid
4	h5