Question

我有一个讨论委员会,其组织结构如下:

+-------------------------------+
|       TABLE: MESSAGES         |
+-------------------------------+
| id             | int(11)      |
+-------------------------------+
| parent         | int(11)      |
+-------------------------------+
| author         | int(11)      |
+-------------------------------+
| last_reply     | datetime     |
+-------------------------------+
| written        | datetime     |
+-------------------------------+
| title          | varchar(256) |
+-------------------------------+
| content        | text         |
+-------------------------------+

<代码>last_reply的缺省值为NUL。如果对校正做出答复,该校的所有电文的<代码>last_reply将定在最后答复之日。

What I m trying to achieve is an order of the conversations where both last_reply and written will be considered, but where the last_reply will be priorized over written when available.

So if last_reply is available, we use that value for ordering. If not, we use written.

如果解释不好,我表示歉意。

我迄今最响亮的是:

SELECT *, COALESCE(last_reply,written) AS tmp FROM messages
WHERE parent = 0 ORDER BY written DESC, tmp DESC

这并不正确。

如果你能找到我的话,请指导我正确方向:

感谢。

Answer 1

SELECT * FROM messages
WHERE PARENT=0
ORDER BY IFNULL(last_reply,written) DESC

Answer 2

Try this:

SELECT *, COALESCE(last_reply,0) AS with_replay FROM messages
WHERE parent = 0
ORDER BY with_replay DESC,written DESC

友情链接