我有两个表,第一个是"用户",第二个是"帖子","用户"有两列:
1 id
2 username
"帖子"有五列:
1 p_id
2 uid
3 post_id
4 content
5 date
定义的帖子在post_id
中具有值"0",回复的值在post_id
中具有值p_id
。我的查询是
SELECT id,username,p_id,uid,post_id,content,date
FROM users
inner join posts
ON users.id=posts.uid
WHERE post_id='0'
ORDER BY p_id DESC
但我想像在论坛中一样按最后的回复排序。
在 SELECT 子句中使用相关的子查询来查找该列的最新答复日期和顺序。如果帖子没有回复,请改用帖子的日期:
SELECT id,username,p_id,uid,post_id,content,date, (
SELECT MAX(date) FROM posts r WHERE r.post_id = p.p_id
) AS last_reply_date
FROM users
inner join posts p
ON users.id=p.uid
WHERE p.post_id='0'
ORDER BY COALESCE(last_reply_date, p.date) DESC