在php中从mysql获取两个字段之间的唯一记录

8mmmxcuj  于 2021-06-24  发布在  Mysql
关注(0)|答案(3)|浏览(342)

我有一个表,我的私人信息存储在我和我的朋友做。

我有如图所示的表结构假设我的登录用户\ id是1,现在我想显示他最后五个唯一的对话,以及每个对话的最后一条消息。

xe55xuns

xe55xuns1#

在这里,又长又丑。您可以在一个大的final查询中将临时表替换为子查询。最后是解释。

CREATE TEMPORARY TABLE IF NOT EXISTS t1 AS
    (SELECT message_id, message_from_uid, message_to_uid, message_time
    FROM private_msgs
    WHERE message_from_uid=$UID)
 UNION 
    (SELECT message_id, message_to_uid, message_from_uid, message_time
    FROM private_msgs
    WHERE message_to_uid=$UID);

CREATE TEMPORARY TABLE IF NOT EXISTS t2 AS SELECT * FROM t1;

SELECT m.*
    FROM (t1 LEFT OUTER JOIN t2 
    ON (t1.message_from_uid=t2.message_from_uid AND t1.message_to_uid=t2.message_to_uid AND t1.message_time<t2.message_time)) 
JOIN private_msgs m ON t1.message_id=m.message_id
WHERE t2.message_id IS NULL AND t1.message_from_uid=$UID
ORDER BY t1.message_time DESC;

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;

t1临时表接受$uid为发送方的所有消息,并将它们与$uid为接收方的消息合并,但将$uid作为发送方。在t1中,谁是发送者/接收者并不重要。重要的是这些是$uid和其他人之间的对话。
t2是t1的副本,因为在mysql中不能将临时表与其自身连接起来。
最后我们进行查询。我们加入t1和t2,在每个会话中只获取最新的消息,并将其从最新到最旧排序。在t1和t2中,发送方和接收方是混合的,我们使用私有消息进行最后一次连接以获得原始数据。
对不起,我的英语不是以英语为母语的。

wb1gzix0

wb1gzix02#

不是很优雅但很实用:

SELECT m1.*
    FROM private_msgs m1 LEFT OUTER JOIN private_msgs m2 
    ON (m1.message_from_uid=m2.message_from_uid AND m1.message_to_uid=m2.message_to_uid AND m1.message_time<m2.message_time)
WHERE m2.message_id IS NULL AND m1.message_from_uid=$uid
ORDER BY message_time DESC;

加上限制5或任何你想要的
编辑。抱歉,我没有注意到uid可以作为发送者或接收者。您可以发布带有示例数据的表吗?例如:http://sqlfiddle.com?
无论如何,这应该是关键:

WHERE m2.message_id IS NULL AND (m1.message_from_uid=$uid OR m1.message_to_uid=$uid)
lvjbypge

lvjbypge3#

在我的旧代码中找到:

select m.*
        from private_messages m
        join user_table s ON m.sender_id = s.id
        join user_table r ON m.receiver_id = r.id
        join (select if(sender_id = $UID, receiver_id, sender_id) as user_id_other,
            max(posted_at) AS date_time_max from private_messages
                where (sender_id = $UID OR receiver_id = $UID)
                group by if(sender_id=$UID, receiver_id, sender_id)
                ) AS t
        on if(m.sender_id = $UID, m.receiver_id, m.sender_id) = user_id_other 
        AND m.posted_at=date_time_max
    where m.sender_id = $UID OR m.receiver_id = $UID
    order by m.posted_at desc

对结果不负责,但这会有所帮助。变量本身必须被替换,我没有对代码进行太多修改,如您所见。但是这片。。。代码。。。工作。
你的情况是:

select m.*
    from YOUR_MESSAGES_TABLE m
    --- OPTIONAL: join YOUR_USER_TABLE s ON m.message_from_uid = s.id
    --- OPTIONAL: join YOUR_USER_TABLE r ON m.message_to_uid = r.id
    join (select if(message_from_uid = $YOUR_USER_ID, message_to_uid, message_from_uid) as user_id_other,
        max(message_time) AS date_time_max from YOUR_MESSAGES_TABLE
            where (message_from_uid = $YOUR_USER_ID OR message_to_uid = $YOUR_USER_ID)
            group by if(message_from_uid=$YOUR_USER_ID, message_to_uid, message_from_uid)
            ) AS t
    on if(m.message_from_uid = $UID, m.message_to_uid, m.message_from_uid) = user_id_other 
AND m.message_time=date_time_max
where m.message_from_uid = $UID OR m.message_to_uid = $UID
order by m.message_time desc limit 5

测试:http://sqlfiddle.com/#!9/f66fd8/4层

相关问题