取出点击量最高的文章100篇，每个作者不超过5篇

--取出点击量最高的文章100篇，每个作者不超过5篇
select top 100 id,title,tips,author 
from ( 
select *,cnt=row_number() over (partition by author order by tips desc) 
from tb 
) t 
where cnt <=5 
order by tips desc 

 

 

 

 

--A B 连表 找出对应号码 最大id所对应的公司id
select top 1000 a.phone, b.* from  dbo.Send_Api a 
left join (
select   SendID , CompanyID , Phone ,  cnt = row_number() over (partition by phone order by SendID desc) from send
) b 
on case when len(a.phone)>11 then substring(a.Phone, 3, 11)  else a.Phone end=b.phone and b.cnt=1