查询在线咨询列表sql

mysql

要求：根据发布（招聘id）id,以及对话双方的id查询聊天列表以及未读条数

建表 id  aid 发消息人的id  bid 收消息人的id cid发布id read是否已读

 

 select z.cid,z.unionid,x.notRead from (
select cid, 
 case  when aid='1' then bid 
       when  bid = '1' then aid 
 else aid  end as unionid
 from abc 
 where aid = 1 or bid = 1
 GROUP BY cid ,unionid) z
 LEFT JOIN(
 select count(*) notRead,bid,cid from abc where  `read`=0  GROUP BY bid ,cid
 )x on x.cid = z.cid and x.bid = z.unionid