我正试图按时间戳对我为论坛创建的两个表进行排序。
我已经找过了,但还没有找到。请帮助。
我的第一个表是"forum_posts"和时间戳是"forum_timestamp"我的第二个表是"forum_posts_replys"是"fpr_timestamp"
使用以下查询,我可以按时间戳或其他方式排序,但我想对两者都进行排序。如果在一个主题中没有任何回复,并且它刚刚发布,我希望看到它在顶部,但如果在另一个主题中有回复,我希望该回复首先显示。
(SELECT fp.*, fpr.*
FROM forum_posts fp
LEFT JOIN forum_posts_replys fpr
ON fp.forum_post_id = fpr.fpr_post_id
WHERE `fp`.`forum_id`='$f_id'
GROUP BY fp.forum_post_id)
ORDER BY `forum_timestamp` DESC
我也尝试了GREATEST(),但没有运气。
我试图使用这个页面是主题列表页面。该页面仅显示主题及其标题和回复计数以及每个帖子发布的时间。
我的当前查询06/14/14:
SELECT sub.*
FROM
(SELECT
fp.forum_post_id as forum_post_id, fp.forum_id as forum_id,
fp.forum_user_id as forum_user_id, fp.forum_title as forum_title,
fp.forum_content as forum_content, fp.forum_edit_date as forum_edit_date,
fp.forum_timestamp as forum_timestamp, fpr.id as id,
fpr.fpr_post_id as fpr_post_id, fpr.fpr_id as fpr_id,
fpr.fpr_user_id as fpr_user_id, fpr.fpr_title as fpr_title,
fpr.fpr_content as fpr_content, fpr.fpr_edit_date as fpr_edit_date,
fpr.fpr_timestamp as fpr_timestamp,
ifnull(fpr.fpr_timestamp,forum_timestamp) as tstamp
FROM forum_posts fp
LEFT JOIN forum_posts_replys fpr
ON fp.forum_post_id = fpr.fpr_post_id
WHERE `fp`.`forum_id`='$f_id'
GROUP BY fp.forum_post_id) sub
ORDER BY tstamp DESC
似乎只按forum_timestamp
使用子查询
SELECT fp.*, fpr.*
from
(SELECT fp.*, fpr.*, ifnull(fpr.fpr_timestamp,forum_timestamp) as tstamp
FROM forum_posts fp
LEFT JOIN forum_posts_replys fpr
ON fp.forum_post_id = fpr.fpr_post_id
WHERE `fp`.`forum_id`='$f_id'
GROUP BY fp.forum_post_id) sub
order by tstamp desc
编辑:明确列出需要的字段,并为子查询使用表别名:
SELECT sub.*
from
(SELECT fp.field_1 as f1, fp.field_2 as f2, fpr.field_1 as f3, fpr.field_2 as f4, ifnull(fpr.fpr_timestamp,forum_timestamp) as tstamp
FROM forum_posts fp
LEFT JOIN forum_posts_replys fpr
ON fp.forum_post_id = fpr.fpr_post_id
WHERE `fp`.`forum_id`='$f_id'
GROUP BY fp.forum_post_id) sub
order by tstamp desc
在您的帮助下,我能够解决这个问题,使其工作!
这是最后一个完美运行的查询。
SELECT sub.*
FROM
(SELECT
fp.forum_post_id as forum_post_id, fp.forum_id as forum_id,
fp.forum_user_id as forum_user_id, fp.forum_title as forum_title,
fp.forum_content as forum_content, fp.forum_edit_date as forum_edit_date,
fp.forum_timestamp as forum_timestamp, fpr.id as id,
fpr.fpr_post_id as fpr_post_id, fpr.fpr_id as fpr_id,
fpr.fpr_user_id as fpr_user_id, fpr.fpr_title as fpr_title,
fpr.fpr_content as fpr_content, fpr.fpr_edit_date as fpr_edit_date,
fpr.fpr_timestamp as fpr_timestamp,
GREATEST(fp.forum_timestamp, COALESCE(fpr.fpr_timestamp, '00-00-00 00:00:00')) AS tstamp
FROM forum_posts fp
LEFT JOIN forum_posts_replys fpr
ON fp.forum_post_id = fpr.fpr_post_id
WHERE fp.forum_id=$f_id
ORDER BY tstamp DESC
) sub
GROUP BY forum_post_id
ORDER BY tstamp DESC