我正在寻找解决方案;查询是:
SELECT MAX(forum_commenti.Data) AS MData, forum_post.id AS id, forum_post.Nome AS Nome, forum_post.Messaggio AS Messaggio, forum_post.Sezione AS Sezione, forum_post.Data AS Data, forum_post.Utente AS Utente, forum_post.Chiuso AS Chiuso, forum_post.Importante AS Importante
FROM forum_post LEFT OUTER JOIN forum_commenti ON forum_post.id = forum_commenti.Post
WHERE forum_post.Importante = 0
AND forum_post.Sezione = '".$_GET['id']."'
GROUP BY id, Nome, Messaggio, Sezione, Data, Utente, Chiuso, Importante
ORDER BY MData IS NOT NULL DESC, Data DESC
LIMIT $start, $per_page
这是一个论坛的查询;我正在尝试显示数据的帖子顺序。我的愿望是:
- 如果帖子没有回复,请使用他自己的日期订购该帖子,否则使用该帖子的最后评论日期。 (其他信息无用(
寻找有关此类问题的旧问题,但是当我尝试这样做时,它给了我问题:
ORDER BY MData IS NOT NULL DESC, Data ASC
它说">不支持引用'MData'(引用组功能("。
我正在将该查询用于 php 函数。
查询中涉及的表包括:
- forum_post:包含论坛的所有帖子
- forum_commenti :包含所有帖子的所有回复,使用"Post"作为forum_post的外键
WHERE 条件对于查询来说是一个无用的点。
我将向您展示示例:
帖子 1 |最后回复的数据是 19/12/2014 10:00:00 , 帖子数据是 19/12/2014 09:00:00获取最后一条评论的数据
帖子 2 |最后回复的数据为空(没有回复(,帖子的数据是 19/12/2014 08:00:00获取帖子的数据
..............等
现在我有这样的东西:
帖子 1 | 2014-12-19 10:00:00
帖子 2 | 19/12/2014 08:00:00
按数据排序
谢谢大家的帮助。
当使用首先选择帖子以及最新评论日期的子查询时,它有效。然后可以根据这两列上的表达式对结果进行排序。我使用IFNULL(last_post_date, own_date)
来选择正确的日期,并出于教学目的在effective_date
列中提供此日期:
SELECT *, IFNULL(last_post_date, own_date) AS effective_date
FROM (
SELECT
forum_post.post_id,
forum_post.date AS own_date,
MAX(forum_comment.date) AS last_post_date
FROM forum_post
LEFT OUTER JOIN forum_comment
ON (forum_post.post_id = forum_comment.post_id)
GROUP BY post_id
) posts
ORDER BY effective_date DESC;
请注意,由于原始表定义不可用,我在答案中使用了略有不同的表设置,但它也适用于任何其他表设置。以下是我的示例表:
CREATE TABLE `forum_post` (
`post_id` int(11) NOT NULL AUTO_INCREMENT,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`post_id`)
);
CREATE TABLE `forum_comment` (
`comment_id` int(11) NOT NULL AUTO_INCREMENT,
`post_id` int(11) DEFAULT NULL,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`comment_id`)
);
我使用以下示例数据进行测试:
forum_post。
+---------+---------------------+
| post_id | date |
+---------+---------------------+
| 1 | 2014-12-01 00:00:00 |
| 2 | 2014-12-02 00:00:00 |
| 3 | 2014-12-02 00:00:00 |
| 4 | 2014-12-03 00:00:00 |
| 5 | 2014-12-03 00:00:00 |
| 6 | 2014-12-04 00:00:00 |
| 7 | 2014-12-06 00:00:00 |
| 8 | 2014-12-09 00:00:00 |
+---------+---------------------+
forum_comment:
+------------+---------+---------------------+
| comment_id | post_id | date |
+------------+---------+---------------------+
| 1 | 1 | 2014-12-01 00:00:00 |
| 2 | 1 | 2014-12-02 00:00:00 |
| 3 | 1 | 2014-12-03 00:00:00 |
| 4 | 2 | 2014-12-23 00:00:00 |
| 5 | 3 | 2014-12-09 00:00:00 |
| 6 | 3 | 2014-12-15 00:00:00 |
| 7 | 5 | 2014-12-15 00:00:00 |
| 8 | 7 | 2014-12-09 00:00:00 |
| 9 | 7 | 2014-12-11 00:00:00 |
+------------+---------+---------------------+
使用该数据,查询将返回以下结果:
+---------+---------------------+---------------------+---------------------+
| post_id | own_date | last_post_date | effective_date |
+---------+---------------------+---------------------+---------------------+
| 2 | 2014-12-02 00:00:00 | 2014-12-23 00:00:00 | 2014-12-23 00:00:00 |
| 3 | 2014-12-02 00:00:00 | 2014-12-15 00:00:00 | 2014-12-15 00:00:00 |
| 5 | 2014-12-03 00:00:00 | 2014-12-15 00:00:00 | 2014-12-15 00:00:00 |
| 7 | 2014-12-06 00:00:00 | 2014-12-11 00:00:00 | 2014-12-11 00:00:00 |
| 8 | 2014-12-09 00:00:00 | NULL | 2014-12-09 00:00:00 |
| 6 | 2014-12-04 00:00:00 | NULL | 2014-12-04 00:00:00 |
| 4 | 2014-12-03 00:00:00 | NULL | 2014-12-03 00:00:00 |
| 1 | 2014-12-01 00:00:00 | 2014-12-03 00:00:00 | 2014-12-03 00:00:00 |
+---------+---------------------+---------------------+---------------------+