如何在MySQL中选择每行的两个元素的最大值

问题描述：

我有一个表是(My)SQL查询的结果.在此表中，我具有创建后的时间戳和用户注释的创建时间戳.诀窍在于，并非所有帖子都带有评论(因此某些comment_creation为NULL).

I have got a table that is a result of a (My)SQL query. In this table I have the post creation timestamp and the user comment creation timestamp. The trick is that not all posts have a comment (so some comment_creation are NULL).

我想根据帖子或用户评论的最新创建时间对行进行排序.

I would like to order the rows according of the most recent creation time of the post or user comment.

如何获取每一行的max(post_creation, comment_creation)并对其进行排序(DESC顺序)?

How can I get the max(post_creation, comment_creation) of each row and order them (DESC order)?

感谢所有贡献.

答

根据您之前的问题，尝试:

Based on your previous question, try:

SELECT p.id AS post_id, 
       p.author_id AS post_author_id, 
       p.created_date AS post_created,
       c.author_id AS comment_author_id,
       c.created_date AS comment_created,
       p.title, 
       c.content,
       coalesce(c.created_date,p.created_date) AS sort_date
FROM Posts p 
LEFT JOIN Comments c ON p.id = c.post_id
WHERE p.author_id = $userId
UNION ALL
SELECT p.id AS post_id, 
       p.author_id AS post_author_id, 
       p.created_date AS post_created,
       c.author_id AS comment_author_id,
       c.created_date AS comment_created,
       p.title, 
       c.content,
       c.created_date AS sort_date
FROM Posts p 
RIGHT JOIN Comments c ON p.id = c.post_id
WHERE c.author_id = $userId
ORDER BY sort_date

如何在MySQL中选择每行的两个元素的最大值

相关推荐