select * ,c.[count] from tab a inner join (select userid,count(1) [count] from tab where msgflag=2 group by userid) c on a.userid=c.userid where exists (select 1 from ( select userid,max(time) time from tab group by userid)b where a.userid=b.userid and a.time=b.time )
select a.*,b.[未读消息数] from ( select *,rn=ROW_NUMBER() over(partition by userid order by [time] desc) from 记录聊天消息表 where msgflag=2 )t left join (select userid,COUNT(*) as [未读消息数] from 记录聊天消息表 where msgflag=2 group by userid)b on a.userid=b.userid where rn=1
from tab a inner join (select userid,count(1) [count] from tab where msgflag=2 group by userid) c on a.userid=c.userid
where exists
(select 1 from (
select userid,max(time) time
from tab
group by userid)b where a.userid=b.userid and a.time=b.time )
select a.*,b.[未读消息数]
from
(
select *,rn=ROW_NUMBER() over(partition by userid order by [time] desc) from 记录聊天消息表 where msgflag=2
)t
left join (select userid,COUNT(*) as [未读消息数] from 记录聊天消息表 where msgflag=2 group by userid)b
on a.userid=b.userid
where rn=1