我不知道如何编写获取最后一条记录的SQL语法(根据最近的帖子,没有回复)。
我的桌子
+-------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+----------------+
| notification_id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| user_id | mediumint(8) unsigned | NO | | NULL | |
| notification_msg | text | NO | | NULL | |
| notification_date | int(11) unsigned | NO | | NULL | |
| private_message | tinyint(1) unsigned | NO | | 0 | |
| has_replied | tinyint(1) unsigned | NO | | 0 | |
| reply_id | mediumint(8) unsigned | NO | | 0 | |
+-------------------+-----------------------+------+-----+---------+----------------+
基本上对于每个线程通知,它应该获取每个通知记录的最后一条记录并检查has_replied
是否0
,如果为 0,那么它应该返回它,以便 PHP 可以读取是否有尚未回复的通知。所以喜欢,它应该像这样返回(伪):
+--------------+-----+-----+
| username | 1 | 4 |
| username2 | 0 | 2 |
+--------------+-----+-----+
其中第二列表示上一篇文章是否已得到回复。
我当前的SQL语法(有效,但没有得到最后一条记录,如果得到回复):
SELECT n.*,
m.user_id,
m.username
FROM notifications n
INNER JOIN members m ON n.user_id = m.user_id
WHERE private_message = 1
AND reply_id = 0
ORDER BY has_replied ASC,
notification_date DESC
Select m.user_id, m.username
, N...
From members As M
Join (
Select user_id, Max( notification_id ) As notification_id
From notifications
Group By user_id
) As UserLastNotification
On UserLastNotification.user_id = m.user_id
Join notifications As N
On N.notification_id = UserLastNotification.notification_id
Where N.private_message = 1
And N.reply_id = 0
Order By N.has_replied, N.notification_date Desc
请注意,这将过滤每个用户的最后一个通知是私人消息,并且reply_id为零。
一个简单的
LIMIT 1
在查询结束时应该足以只返回最后一篇文章。