分组并对每个组进行排序

Question

我有一个mysql表，用于存储带有模式的注释和子注释：id、replyTo、text，如果注释不是子注释，则replyTo可能为null。

目前，我正在执行以下查询：

(select id as replyTo, text from Comments WHERE replyTo IS NULL) 
UNION 
(select  replyTo,text from Comments WHERE replyTo IS NOT NULL) ORDER BY replyTo;

这让我按顺序给出了评论：

1. comment
2. subcomment

所以如果我有：

+----+---------+-----------+
| id | replyTo | text      |
+----+---------+-----------+
|  1 |    NULL | comment 1 |
|  2 |    NULL | comment 2 |
|  3 |       1 | sub 1     |
|  4 |       2 | sub 2     |
+----+---------+-----------+

这意味着：

+---------+-----------+
| replyTo | text      |
+---------+-----------+
|       1 | comment 1 |
|       1 | sub 1     |
|       2 | comment 2 |
|       2 | sub 2     |
+---------+-----------+

我的问题是，我怎样才能让他们倒过来，就是这样：

+-+------------+
|2|comment 2   |
|2|sub 2       |
|1|comment 1   |
|1|sub 1       |
+-+------------+

换句话说，我想按照replyTo ASC对元素进行排序，按replyTo对它们进行分组(而不是mysql，它将根据属性删除所有重复的行)，然后按replyTo DESC对组进行排序。

Answer 1

试试这个：

select replyTo, text from
(
select id as replyTo, text from Comments WHERE replyTo IS NULL 
UNION 
select  replyTo,text from Comments WHERE replyTo IS NOT NULL ORDER BY replyTo
)
order by replyTo DESC;

Answer 2

试一试

SELECT COALESCE(replyTo,id) AS replyTo, text
FROM Comments
ORDER BY COALESCE(replyTo,id) DESC, id