在SQL的两行之间插入一行 [英] Insert a row between two rows in SQL

问题描述

我有一个类似这样的表，它显示了哪个用户对哪个父线程发表了评论.

I have a table like this, which shows which user commented on which parent thread.

ParentID  CommentID UserName  CommentDateTime
58         58       Vicky     2016-12-02 11:51:07.270
58         61       Billu     2016-12-02 12:35:40.220
58         62       Rakesh    2016-12-02 12:37:42.133

如果假设对第二行进行注释，则会生成一个新的commentid，它是63

If suppose a comment is made to the 2nd row, a new commentid is generated which is 63

我想编写一个sql查询，该查询按以下顺序列出行:

I want to write a sql query which list the rows in the below order :

ParentID  CommentID UserName  CommentDateTime
58         58       Vicky     2016-12-02 11:51:07.270
58         61       Billu     2016-12-02 12:35:40.220
61         63       Rakesh    2016-12-02 13:37:42.133
58         62       Rakesh    2016-12-02 12:37:42.133

您能帮我为此编写逻辑/SQL吗?

Could you please help me in writing a logic/SQL for this?

推荐答案

如果我做对了，则希望按所需顺序输出记录，而不要将其插入表中.在这里，我们看到大量的帖子，所以我认为您应该使用

If I've got it right you want to output records in the desired order not insert them into the table. Here we see a TREE of posts so I think you should use a recursive CTE. For each node we build a PATH string and then sort nodes using this path string:

WITH CTE AS
(
   SELECT ParentID, CommentID, UserName, CommentDateTime,
          ParentID as ThreadID,
          CAST(ParentID as varchar(MAX)) as PathStr
   FROM T 
   WHERE CommentID = T.ParentID 

   UNION ALL

   SELECT T.ParentID, T.CommentID, T.UserName, T.CommentDateTime,
          CTE.ThreadID,
          PathStr+'-'
          +CAST(T.CommentID as varchar(MAX)) as PathStr
   FROM T 
   JOIN CTE ON CTE.CommentID = T.ParentID
   WHERE T.CommentID <> T.ParentID 
)

SELECT * FROM CTE ORDER BY ThreadID,PathStr

我在示例表中添加了更多节点，因此结果如下:

I've added more nodes to example table so here is the result:

╔══════════╦═══════════╦══════════╦═════════════════════════╦══════════╦═════════════╗
║ ParentID ║ CommentID ║ UserName ║     CommentDateTime     ║ ThreadID ║   PathStr   ║
╠══════════╬═══════════╬══════════╬═════════════════════════╬══════════╬═════════════╣
║       58 ║        58 ║ Vicky    ║ 2016-12-02 11:51:07.270 ║       58 ║ 58          ║
║       58 ║        61 ║ Billu    ║ 2016-12-02 12:35:40.220 ║       58 ║ 58-61       ║
║       61 ║        63 ║ Rakesh   ║ 2016-12-02 13:37:42.133 ║       58 ║ 58-61-63    ║
║       58 ║        62 ║ Rakesh   ║ 2016-12-02 12:37:42.133 ║       58 ║ 58-62       ║
║      158 ║       158 ║ Vicky    ║ 2016-12-02 11:51:07.270 ║      158 ║ 158         ║
║      158 ║       161 ║ Billu    ║ 2016-12-02 12:35:40.220 ║      158 ║ 158-161     ║
║      161 ║       163 ║ Rakesh   ║ 2016-12-02 13:37:42.133 ║      158 ║ 158-161-163 ║
║      161 ║       164 ║ Rakesh   ║ 2016-12-02 13:37:42.133 ║      158 ║ 158-161-164 ║
║      158 ║       162 ║ Rakesh   ║ 2016-12-02 12:37:42.133 ║      158 ║ 158-162     ║
╚══════════╩═══════════╩══════════╩═════════════════════════╩══════════╩═════════════╝

这篇关于在SQL的两行之间插入一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！