34,590
社区成员
发帖
与我相关
我的任务
分享
select mobile,MIN(title) as title
from #t
group by mobile
; with cte as
(select*
from #t k
where exists(select * from #t where mobile=k.mobile and title+0<k.title+0) )
delete cte
select * from #t
create table TB(mobile varchar(50),title varchar(50))
insert into TB values('11','1100')
insert into TB values('11','1111')
insert into TB values('11','1122')
insert into TB values('22','2200')
insert into TB values('33','3300')
insert into TB values('33','3311')
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY mobile ORDER BY mobile,title) AS ROWID FROM TB
) P
WHERE ROWID = 1
DROP TABLE TB
/*
mobile title ROWID
-------------------------------------------------- -------------------------------------------------- --------------------
11 1100 1
22 2200 1
33 3300 1
select mobile,MAX(title) as title
from #t
group by mobile