php – 获取每组中的最后一行？
发布时间：2020-12-13 22:29:55 所属栏目：PHP教程 来源：网络整理
导读：你好基本上我试图将facebook的邮件系统复制到我的网站上. 这是逻辑…… “当user1创建一个新消息发送给user7时,创建一个新线程,其thread_id为1(表：messages_thread),并在表中插入一个新条目：消息为message_id 1(表：消息).当user7 REPLYS时到user1的消息,
|
你好基本上我试图将facebook的邮件系统复制到我的网站上.
这是逻辑…… 现在,当用户1创建发送给用户7的新消息时,创建了线程2,并创建了消息3.当user7回复thread2时,会创建消息4(希望你得到逻辑.) 一切都好.唯一的问题是我需要在线程中选择最新的消息但是我遇到了sql的问题, 这个我现在的sql … SELECT max(message_id) message_id,m.thread_id,m.body,m.user_id,m.to_id,m.message_status,m.new,m.date,u.id,u.displayname,u.username,u.profile_img FROM messages m INNER JOIN users u ON u.id = m.user_id WHERE to_id = 7 AND (message_status = 'unread' or message_status='read' or message_status='saved') group by thread_id Order by message_id Desc LIMIT 10 产生这个…… +------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+ | message_id | thread_id | body | user_id | to_id | message_status | new | date | id | displayname | username | profile_img | +------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+ | 6 | 2 | Really nice | 1 | 7 | read | 0 | 1298617367 | 1 | Kenny Blake | imkenee | 28_1 | | 4 | 1 | Whats good with you? | 1 | 7 | read | 0 | 1298607438 | 1 | Kenny Blake | imkenee | 28_1 | +------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+ 这是好的,但是一个小问题,它选择每个组中的第一行,并尝试选择每组中的最新(最后一行) 我怎样才能做到这一点？这是表格.谢谢！ 表：Messages_thread +----+---------+----------------+-------------+-----------+---------------+-------------+------------+ | id | user_id | subject | from_status | to_status | from_s_delete | to_s_delete | date | +----+---------+----------------+-------------+-----------+---------------+-------------+------------+ | 1 | 1 | Hey Kenny | unread | unread | 0 | 0 | 1298607438 | | 2 | 7 | Check out this | unread | unread | 0 | 0 | 1298617344 | +----+---------+----------------+-------------+-----------+---------------+-------------+------------+ 表消息 +------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+ | message_id | thread_id | user_id | to_id | body | message_status | is_sent_deleted | new | date | +------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+ | 1 | 1 | 1 | 7 | Whats good with you? | read | 0 | 0 | 1298607438 | | 2 | 1 | 7 | 1 | Nothing Kenny just chilling. Whats up with you though???? | read | 0 | 0 | 1298607473 | | 4 | 1 | 1 | 7 | Just posted victor how are you man? | read | 0 | 0 | 1298607956 | | 5 | 2 | 7 | 1 | Look at this poem.... | read | 0 | 0 | 1298617344 | | 6 | 2 | 1 | 7 | Really nice | read | 0 | 0 | 1298617367 | | 7 | 2 | 7 | 1 | Yea i know right :) | unread | 0 | 0 | 1298617383 | +------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+ 解决方法
将子线程中的线程分组,该子查询将返回每个线程的最后一条消息：
SELECT m.message_id,u.profile_img
FROM messages m
INNER JOIN users u ON u.id = m.user_id
INNER JOIN (
SELECT MAX(message_id) MaxMsgIDForThread
FROM messages
WHERE to_id = 7
AND (message_status = 'unread'
or message_status='read'
or message_status='saved')
GROUP BY thread_id
) g ON m.message_id = g.MaxMsgIDForThread
Order by m.message_id Desc
LIMIT 10
WHERE可能需要移动到外部查询,现在它将选择符合条件的最后一条消息,如果要在不满足条件时完全跳过线程,则将其移动到外部查询. 您还应该考虑将消息状态存储为 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |