我正在尝试在简单的消息传递系统上工作。这是一个包含每个用户发送的最后一条消息的页面。这是我的桌子
-消息
id person1 person2 message mess_date
1 2 q hii 2016-07-11 00:00:00.000000
2 q 2 hello 2016-07-10 00:00:00.000000
3 2 q how r u 2016-07-12 00:00:00.000000
4 2 1 message 2016-07-10 00:00:00.000000
5 q 2 nooooo 2016-07-13 00:00:00.000000
-登录
id roll
1 q
2 1
3 2
现在我正在尝试使用此查询获取消息 -
suppose $roll="2";
select login.roll as userid, m1.id, m1.message, m1.mess_date
from message as m1,login
where (
(m1.person1=? and login.roll=m1.person2) or
(m1.person2=? and login.roll=m1.person1)
)
group by login.roll
order by m1.mess_date desc');
$stmt->bind_param('ss', $roll, $roll);
输出有 2 个结果:
q - hiii - 2016-07-11 00:00:00.000000
1 - message - 2016-07-10 00:00:00.000000
但我希望它按mess_date排序,即
q - nooooo - 2016-07-13 00:00:00.000000
1 - message - 2016-07-10 00:00:00.000000
您的
SQL 组未正确使用,请尝试以下操作:
假设 id 总是随着时间的推移而增长,否则你需要通过 max(mess_date) 来做:
SELECT msg1.*
FROM (
SELECT login.roll AS userid, m1.id, m1.message, m1.mess_date
FROM message AS m1,login
WHERE (
(m1.person1=? AND login.roll=m1.person2) OR
(m1.person2=? AND login.roll=m1.person1)
)
) AS msg1, (
SELECT msg.userid, max(msg.id) AS maxid
FROM (
SELECT login.roll AS userid, m1.id, m1.message, m1.mess_date
FROM message AS m1,login
WHERE (
(m1.person1=? AND login.roll=m1.person2) OR
(m1.person2=? AND login.roll=m1.person1)
)
) AS msg
GROUP BY userid
) AS msg2
WHERE msg1.userid = msg2.userid
AND msg1.id = msg2.maxid
ORDER BY msg1.mess_date DESC
$stmt->bind_param('ssss', $roll, $roll, $roll, $roll);