如果每个uid在昨天都只发起了一个对话的话，那就是： with firstsent as (select uid,min(ts) 'senttime' from tbl_msgs where direction=0 group by uid), firstrespond as (select csid,uid,min(ts) 'respondtime' from tbl_msgs where direction=1 group by csid,uid), tmp as (select csid,respondtime-senttime 'timediff' from firstsent p1 left join firstrespond p2 on p1.uid=p2.uid) select csid,avg(timediff) 'averagetime' from tmp group by csid;