gpt4 book ai didi

mysql - 用于聊天的复杂 Mysql 查询,按 desc 排序,最后留下 NULL

转载 作者:行者123 更新时间:2023-11-29 19:26:37 27 4
gpt4 key购买 nike

我有两个表 sms_contacts 和 sms_twoway_chat。联系人表使用 id 作为 PK,contact_type 用于 3 种 psooible 类型的联系人(0,1 和 2)sms_twoway_chat 保存两个联系人之间的所有聊天。

我需要 contact_type 0 的所有联系人,无论他们是否有聊天我只需要 contact_type 1 和 2 且已收到回复的联系人我需要未读 (is_read = 0) 和最近的聊天回复 (date_time desc ) 位于顶部

我所做的是在顶部显示 Null,例如聊天表中没有任何记录的类型 0 的联系人显示在顶部,而他们应该在最后

CREATE TABLE IF NOT EXISTS `sms_contacts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`contacts_name` varchar(50) DEFAULT NULL,
`contacts_phone` varchar(11) DEFAULT NULL,
`date_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`date_time_unix` bigint(20) DEFAULT NULL,
`contact_type` int(1) DEFAULT '0' COMMENT '0=>contact 1=>group , 2=>instant msg',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;


CREATE TABLE IF NOT EXISTS `sms_twoway_chat` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`inbound_id` varchar(50) DEFAULT NULL,
`sender_id` int(11) DEFAULT NULL,
`receiver_id` int(11) DEFAULT NULL,
`is_read` int(1) DEFAULT '0',
`msg_body` text,
`date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`date_time_unix` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=89 ;

这是我尝试过的

SELECT a.*
FROM ( SELECT c.id,c.`contacts_name` FROM sms_contacts c
LEFT JOIN sms_twoway_chat tc ON c.id = tc.`receiver_id`
WHERE user_id = 1 AND c.contact_type = 0
ORDER BY -tc.`is_read`, tc.date_time DESC
) a
UNION
SELECT b.*
FROM ( SELECT c.id,c.`contacts_name` FROM sms_contacts c
INNER JOIN sms_twoway_chat tc ON c.id = tc.`receiver_id`
WHERE user_id = 1 AND c.contact_type IN(1,2)
ORDER BY -tc.`is_read`,tc.date_time DESC
) b;

What about this as a solution ??

SELECT *
FROM (
SELECT c.id AS i, 1 AS dt
FROM sms_contacts c
WHERE user_id = 1 AND c.contact_type = 0
) t1
LEFT JOIN
(SELECT *
FROM (
SELECT DISTINCT sender_id AS i, MAX(date_time) AS dt
FROM sms_twoway_chat
WHERE `inbound_id` <> "" AND `receiver_id` =1
GROUP BY sender_id
ORDER BY MAX(date_time) DESC, sender_id
)t3 )t2
ON t1.i = t2.i
ORDER BY t2.dt DESC

最佳答案

这可能对某人有帮助

SELECT *
FROM (
SELECT c.id AS i, 1 AS dt, c.`contacts_name`
FROM sms_contacts c
WHERE user_id = 1 AND c.contact_type = 0
) t1
LEFT JOIN
(SELECT *
FROM (
SELECT DISTINCT sender_id AS i, MAX(date_time) AS dt, 2 AS contacts_name

FROM sms_twoway_chat
WHERE `inbound_id` <> "" AND `receiver_id` =1
GROUP BY sender_id
ORDER BY MAX(date_time) DESC, sender_id
)t3 )t2
ON t1.i = t2.i
ORDER BY t2.dt DESC LIMIT 0,100

关于mysql - 用于聊天的复杂 Mysql 查询,按 desc 排序,最后留下 NULL,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42078369/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com