一个sql有关问题请问
一个sql问题请教
客户和交往是一对多的关系，获取客户的交往次数和最新的交往信息，这个语句怎么修改一下？请高手指教
------解决方案--------------------
select a.CustInfoName,
(select count(b.id) from oa_ContactInfo as b where b.custid=a.id group by custid) as 交往次数,
(select top 1 Contacts from oa_ContactInfo as b where b.custid=a.id order by addtime desc) as 最新交往内容 //有的获取的不对，不是最后一次交往的信息
from oa_CustomerInfo as a
客户和交往是一对多的关系，获取客户的交往次数和最新的交往信息，这个语句怎么修改一下？请高手指教
sql
------解决方案--------------------
select *
from oa_CustomerInfo a inner join
(select custid,Contacts,ROW_NUMBER() OVER(PARTITION BY custid order by addtime desc)rn,
COUNT(id) OVER(PARTITION BY custid )ct from oa_ContactInfo)b
on b.custid=a.id where rn=1