CREATE TABLE `messages` (
`message_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`message_project_id` int(7) unsigned NOT NULL DEFAULT '0',
`message_time` int(11) unsigned NOT NULL DEFAULT '0',
`message_from_user_id` int(7) unsigned NOT NULL DEFAULT '0',
`message_to_user_id` int(7) unsigned NOT NULL DEFAULT '0',
`message_details` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`message_id`)
)
CREATE TABLE `project` (
`project_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`project_user_id` int(7) unsigned NOT NULL DEFAULT '0',
`project_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`project_status` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`project_id`)
)
我要检索的是每个项目发给用户#2的最新消息。
用户#2是项目的所有者，因此可以从许多感兴趣的人那里接收消息。
实际页面将为用户#2显示"要做"的事情列表。我要查找用户#2已收到消息但尚未发送的当前打开项目的任何消息
所以如果用户#1向用户#2发送消息，那么消息表中就会有一行
message_id | project_id | message_time | message_from_user_id | message_to_user_id | message_details
30 | 12 | 1304707966 | 1 | 2 | Hello user number two, thank you for your interest in my project
31 | 12 | 1304707970 | 2 | 1 | Hello user number one, Your project looks interesting
32 | 12 | 1304707975 | 3 | 1 | Hello user number one, here is my first message, im user number three. I want to do your project
32 | 13 | 1304707975 | 7 | 1 | Hello user number one, here is my first message, im user number seven. I want to do your other project
什么我已经尝试到目前为止，但不太工作://这将获得每个项目的最新消息，但不按用户
分开SELECT cur.*, p.*
FROM messages cur
LEFT JOIN messages next ON cur.message_project_id = next.message_project_id AND cur.message_time < next.message_time
LEFT JOIN project p ON p.project_id = cur.message_project_id
WHERE next.message_time IS NULL
AND (cur.message_from_user_id = 2 OR cur.message_to_user_id = 2)
AND (p.project_status LIKE 'open' OR p.project_status LIKE 'started')
AND p.project_user_id = 2
ORDER BY cur.message_time DESC
//这将按用户分隔，但不返回最近的消息文本
SELECT *
FROM messages m
LEFT JOIN project p ON p.project_id = m.message_project_id
WHERE (message_from_user_id = 2 OR message_to_user_id = 2 )
AND (p.project_status LIKE 'open' OR p.project_status LIKE 'started')
AND p.project_user_id = 2
GROUP BY project_id
ORDER BY message_time DESC
一旦数据回到Php，我检查一下，看看最近的消息是否是给用户#2，如果是，然后张贴一个"你需要回复"的消息到他的屏幕
如果您提供一些涵盖大多数情况的示例输出，可能会有所帮助。从你的评论和重读几次之后判断，我认为这就是你要找的:
SELECT
<column list>
FROM
Messages M
INNER JOIN Projects P ON
P.project_id = M.message_project_id AND
P.project_status IN ('open', 'started') AND
P.project_user_id = 2
WHERE
M.message_to_user_id = 2 AND
NOT EXISTS (
SELECT *
FROM Messages M2
WHERE
M2.message_from_user_id = 2 AND
M2.message_project_id = M.message_project_id AND
M2.message_to_user_id = M.message_from_user_id AND
M2.message_time >= M.message_time
)
这将为用户获取所有项目的所有消息，其中他还没有为该项目向发送者发送消息。当然你也可以添加一个ORDER BY
可以吗?
<>之前SELECT *来自消息左连接项目p ON (p.p project_id = m.message_project_id)WHERE (m.message_from_user_id = 211 OR m.message_to_user_id = 211)AND p.project_status IN('open'，'started') AND p.project_user_id = 2组由p.p project_idORDER BY m.message_time之前希望有所帮助