我有一个问题,我正在建立一个论坛,我被以下问题困住了。
我有2张表
table: topics
topic_id | name | timestamp | pinned
-------------------------------------------------
1 | topic_1 | 2016-01-01 12:00:00 | 0
2 | topic_2 | 2016-01-01 13:00:00 | 0
3 | topic_3 | 2016-01-01 14:00:00 | 1
4 | topic_4 | 2016-01-01 15:00:00 | 0
5 | topic_5 | 2016-01-01 16:00:00 | 0
table: messages
message_id | topic_id | text | timestamp
---------------------------------------------------
1 | 1 | test | 2016-01-01 12:30:00
2 | 2 | test | 2016-01-01 13:30:00
3 | 2 | test | 2016-01-01 13:45:00
4 | 1 | test | 2016-01-01 14:30:00
5 | 4 | test | 2016-01-01 17:30:00
如您所见,主题#1有2条消息,主题#2也有2条消息,主题#4有1条消息,主题#3和#5根本没有消息。
我想订购他们的固定和时间戳。如何?
->如果主题是固定的,它必须在顶部
->如果主题有消息,按最后消息的时间戳排序
->如果主题没有任何消息,则根据主题本身的时间戳对它们进行排序
在上面的例子中,顺序是(从上到下):topic_3 (pinned so ALWAYS at the top)
topic_4 (last message at 17:00:00)
topic_5 (no messages but started at 16:00:00)
topic_1 (last message at 14:30:00)
topic_2 (last message at 13:45:00)
我希望我的问题足够清楚。
希望有人能帮忙!
提前感谢。
编辑:我尝试了以下操作,但这不起作用
SELECT DISTINCT
t.topic_id,
t.name
FROM
topics AS t
LEFT JOIN messages AS m ON m.topic_id = t.topic_id
ORDER BY
t.pinned DESC,
m.timestamp DESC,
t.timestamp DESC
您的任务很复杂,因为有许多消息附加到一个主题,并且您需要使用具有MAX(时间戳)的消息进行排序。DISTINCT
不能工作,因为它从表中取出一个随机记录。
我必须使用子查询来解决这个问题。如果消息时间戳不为NULL, IFNULL
将返回消息时间戳,否则- topic时间戳将根据任务的要求返回。
SELECT
t.topic_id,
t.name,
IFNULL(m.timestamp, t.timestamp)
FROM
topics AS t
LEFT JOIN
(SELECT
messages.topic_id,
MAX(messages.timestamp) as timestamp
FROM
messages
GROUP BY
messages.topic_id
) AS m
ON m.topic_id = t.topic_id
GROUP BY
t.topic_id
ORDER BY
t.pinned DESC,
IFNULL(m.timestamp, t.timestamp) DESC;
输出:+----------+---------+----------------------------------+
| topic_id | name | IFNULL(m.timestamp, t.timestamp) |
+----------+---------+----------------------------------+
| 3 | topic_3 | 2016-01-01 14:00:00 |
| 4 | topic_4 | 2016-01-01 17:30:00 |
| 5 | topic_5 | 2016-01-01 16:00:00 |
| 1 | topic_1 | 2016-01-01 14:30:00 |
| 2 | topic_2 | 2016-01-01 13:45:00 |
+----------+---------+----------------------------------+
必要时删除时间戳,我用来显示查询获取了正确的记录。