sql 讀取聊天記錄，要分組最新

with?temp?as
(
	select?fid,toid?,MAX(times)?as?maxtime?from?TestA?group?by?fid,toid
),
?temp1?as
(
	select?a.fid,a.toid,a.maxtime,
	case?when?a.fid<=a.toid?
		?then?CAST(a.fid?AS?varchar(10))+CAST(a.toid?AS?varchar(10))
	else
		CAST(a.toid?AS?varchar(10))+CAST(a.fid?AS?varchar(10))
	end?as?tid
	?from?temp?a?,?temp?b?where?a.fid?=?b.toid?and?b.fid?=?a.toid
),
temp2?as
(
	select?*?from?temp?c?where?not?exists
	(
		select?1?from?temp1?d
		where?c.fid=d.fid?and?c.toid=d.toid?and?c.maxtime=d.maxtime
	)
	union
	select?c.fid,c.toid,c.maxtime?from?temp1?c?inner?join??
	(--處理重復(fù)數(shù)據(jù)
		select?max(maxtime)?maxtime,tid?from?temp1?a?group?by?a.tid
	)d?on?c.maxtime=d.maxtime?and?c.tid=?d.tid
)


select?a.fid,a.toid,a.times,a.des?from?TestA?a?inner?join?temp2?b?on
a.fid=b.fid?and?a.times=b.maxtime?and?a.toid=b.toid

?使用的時候注意將TestA表換成你自己的表名即可