[笔记] -- MySQL从聊天关系中获取最后一条消息

1. 问题

之前项目中咨询师端的聊天列表展示功能突然丢失了一个用户的消息(聊天列表中不展示 但是数据库中存在)

其他用户表现为正常, 然后我被迫又要去找问题 我看了半天也没看出个所以然

我是真的不想看那坨屎SQL 之前数据表设计的有问题 那坨SQL漏洞百出(我修修改改不下三次)

今天我终于绷不住了 给重构了

具体表现为

正常获取聊天内容为四条 应该将最新的消息展示出来但不知为何

查询子语句中的结果为

用尽很多办法也无济于事

最后只能重构一下SQL

2. 解决办法

这是之前子查询语句

SELECT
	TCC1.CustomerID,
	TCC1.Content,
	TCC1.MessageType,
	TCC1.CreateTime,
	TCC1.DoctorID,
	TCC1.DCType,
	TCC1.CReadFlag,
	TCC1.DReadFlag,
	TCC1.MsgTimestamp 
FROM
	TChatContent TCC1 
WHERE
	TCC1.DoctorID = '123456788' 
	AND TCC1.CustomerID = '6803568562326732800' 
	AND NOT EXISTS (
	SELECT
		* 
	FROM
		TChatContent TCC2 
	WHERE
		TCC1.DoctorID = TCC2.DoctorID 
		AND TCC1.CustomerID = TCC2.CustomerID 
		AND TCC1.STATUS != 9 
		AND (
			( TCC1.MsgTimestamp < TCC2.MsgTimestamp ) 
			OR ( TCC1.MsgTimestamp = TCC2.MsgTimestamp AND TCC1.ContentID < TCC2.ContentID ) 
		) 
	) 
GROUP BY
	TCC1.CustomerID

写法也非常的不优雅后来我使用least和greatest函数处理了下

要从对话中获取最后一条消息, 可以使用逐个查询来获取最后一个ID(如果它是递增)或最后一个时间戳

DoctorID将是ID较低的用户

CustomerID是具有较高ID的用户

SELECT
  LEAST(CustomerID, DoctorID) as DoctorID,
  GREATEST(CustomerID, DoctorID) as CustomerID,
  MAX(ContentID) as ContentID,
  MAX(MsgTimestamp) as MsgTimestamp
FROM
  TChatContent
	WHERE DoctorID = '123456788' 
	AND DATE_FORMAT(TCC.CreateTime,'%Y-%m-%d') between #{startDate} and #{endDate}
	AND Status!=9
GROUP BY
  LEAST(CustomerID, DoctorID),
  GREATEST(CustomerID, DoctorID)

最终的查询语句

        SELECT
        IFNULL( TC.PhoneNo, '') AS phoneNo,
        IFNULL( TC.CustomerID, '') AS customerID,
        IFNULL( TCC.DoctorID, '') AS doctorID,
        IFNULL( TCC.CreateTime, IFNULL( TCC.CreateTime, '')) AS createTime,
        IFNULL( TCS.DReadFlag OR TCS.DReadFlag = '', '1') AS readFlag,
        IFNULL( TCC.DReadFlag OR TCC.DReadFlag = '', '1') AS dReadFlag,
        IFNULL( TCC.CReadFlag OR TCC.CReadFlag = '', '1') AS cReadFlag,
        IFNULL( TCC.Content, '') AS content,
        IFNULL( TCC.MessageType, '') AS messageType,
        IFNULL( TCC.DCType, '') AS dcType,
        TC.NickName nickName,
        TC.AvatarURL avatarURL,
        TCS.CToDRemark,
        TCS.DToCRemark
        FROM TChatSession TCS LEFT JOIN (
        select TCC.*
        from
        TChatContent TCC
        inner join (
        select
        least(CustomerID, DoctorID) as DoctorID,
        greatest(CustomerID, DoctorID) as CustomerID,
        max(ContentID) as ContentID,
        max(MsgTimestamp) as MsgTimestamp
        from
        TChatContent
        WHERE DoctorID = #{doctorID}
        and DATE_FORMAT(CreateTime,'%Y-%m-%d') between #{startDate} and #{endDate}
        AND Status!=9
        group by
        least(CustomerID, DoctorID),
        greatest(CustomerID, DoctorID)
        ) MSGINFO
        on  TCC.ContentID = MSGINFO.ContentID
        ) AS TCC ON TCC.CustomerID = TCS.CustomerID AND TCC.DoctorID = TCS.DoctorID
        LEFT JOIN TCustomer TC ON TCC.CustomerID = TC.CustomerID
        WHERE ContentID > 0
        ORDER BY TCS.DReadFlag ASC, TCC.MsgTimestamp DESC
        limit #{page}, #{pageSize};

最终的查询结果

最新的消息已经可以展示啦

3. 总结

使用LEAST函数与GREATEST函数组合MAX()函数即可实现此功能

GREATEST 是查询组内最大数

LEAST 反之