56,677
社区成员
发帖
与我相关
我的任务
分享
drop table tbuser;
create table tbuser(
uid bigint ,
url varchar(100),
udate varchar(20)
);
insert into tbuser
select 1,'www.csdn.net',getdate() union all
select 1,'www.csdn.net',getdate() union all
select 1,'www.163.com',getdate() union all
select 1,'www.baidu.com',getdate() union all
select 1,'www.csdn.net',getdate() union all
select 1,'www.163.com',getdate() union all
select 1,'www.csdn.net',getdate() union all
select 2,'www.baidu.com',getdate() union all
select 2,'www.baidu.net',getdate() union all
select 2,'www.baidu.com',getdate() union all
select 2,'www.baidu.com',getdate() union all
select 2,'www.csdn.net',getdate() union all
select 3,'www.163.com',getdate() union all
select 3,'www.163.net',getdate() union all
select 3,'www.163.com',getdate() union all
select 3,'www.163.com',getdate() union all
select 3,'www.163.com',getdate() union all
select 3,'www.csdn.com',getdate()
select * from (
select row_number() over(partition by uid
order by murlnum desc) as num,* from (
select count(url) as murlnum,uid,url from tbuser group by uid,url
) as source ) a
where a.num=1