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
2000的库没有ROW_NUMBER()函数
top order by是能唯一确定顺序的,不会出现你说的非最后一次
不然你order by改成id试一下,如果id是自增的话
嗯。order by id 确实就可以了!在管理器中查询到展示3秒才出来 15万对30万数据 是不是慢点啊
(select top 1 Contacts from oa_ContactInfo as b where b.custid=a.id order by addtime desc) as 最新交往内容这样交往次数和交往内容拿到了。我怎么从oa_ContactInfo交往表中拿其它列,比如说交往日期和交往人