SQL按特定顺序排序的查询

Question

我有这些数据:

tbody> <<tr>

parent_id	comment_id	comment_level
空	0 xb2	1
空	0 xb6	1
空	0 xba	1
空	0 xbe	1
空	0 xc110	1
空	0 xc130	1
123	0 xc13580	2
456	0 xc135ac	3
123	0 xc13680	2

我想我明白你的意思，UNION是做不到的。

要实现这一点，每行需要与特定的"父"匹配。有1和comment_level的行。如果comment_level已经是1，则该行是它自己的父行。然后，我们可以首先从父记录DESC中按comment_id排序，然后在给定的匹配父记录组中按本地comment_id升序排序。

你需要这样写:

SELECT t0.*
FROM [MyTable] t0
CROSS APPLY (
SELECT TOP 1 comment_id 
FROM [MyTable] t1 
WHERE t1.comment_level = 1 AND t1.comment_id <= t0.comment_id 
ORDER BY t1.comment_id DESC
) parents
ORDER BY parents.comment_id DESC, 
case when t0.comment_level = 1 then 0 else 1 end, 
t0.comment_id

看到它在这里工作:

https://dbfiddle.uk/qZBb3YjO

可能还有一个解决方案，使用窗口函数会更有效。

---

在这里:

SELECT parent_id, comment_id, comment_level 
FROM (
SELECT t0.*, t1.comment_id as t1_comment_id
, row_number() OVER (PARTITION BY t0.comment_id ORDER BY t1.comment_id desc) rn 
FROM [MyTable] t0
LEFT JOIN [MyTable] t1 ON t1.comment_level = 1 and t1.comment_id <= t0.comment_id
) d
WHERE rn = 1
ORDER BY t1_comment_id DESC,
case when comment_level = 1 then 0 else 1 end,
comment_id

看这里:

https://dbfiddle.uk/me1vGNdM

这些varbinary值不是任意的，它们是hierarchyid值。猜测一下，它们可能是在模式中以这种方式键入的(这不可能是巧合)。我们可以利用这个事实来做我们想做的事情。

with d_raw as (
select * from (values
(NULL, 0xC130  , 1),
(123 , 0xC13580, 2),
(456 , 0xC135AC, 3),
(123 , 0xC13680, 2),
(NULL, 0xC110  , 1),
(NULL, 0xBE    , 1),
(NULL, 0xBA    , 1),
(NULL, 0xB6    , 1),
(NULL, 0xB2    , 1)
) as x(parent_id, comment_id, comment_level)
),
d as (
select parent_id, comment_id = cast(comment_id as hierarchyid), comment_level
from d_raw
)
select *,
comment_id.ToString(), 
comment_id.GetAncestor(comment_id.GetLevel() - 1).ToString()
from d
order by comment_id.GetAncestor(comment_id.GetLevel() - 1) desc, 
comment_id

注意-我使用的cte只是为了将数据转换为正确的格式，最后的SELECT添加了额外的列，只是为了显示正在发生的事情;您可以从查询中省略它们而不会产生任何后果。我认为这个解决方案中唯一有趣的部分是使用comment_id.GetAncestor(comment_id.GetLevel() - 1)来获得根级节点。

这样做的一种方法(可能是唯一的一种方法)是创建两个单独的查询并将它们union在一起。比如:

(select * from table where comment_level = 1 order by comment_id desc)
union
(select * from table where not comment_level = 1 order by comment_id asc)