根据 SQL 中的下一条和上一条记录进行排序

根据 SQL 中的下一条和上一条记录进行排序

你可以做这样的事情。

1. 通过使用ROW_NUMBER()和识别每个父母的第一个和最后一个字母PARTITION BY
2. 将前一个的最后一条记录与下一个id的第一条记录相匹配id。
3. 检查第二个父 ID 是否有与上面选择的字母匹配的字母
4. 使用 aLEFT JOIN和使用CASE或ISNULL为id字母匹配的记录设置更高的优先级

Query

;WITH CTE AS (SELECt id,ParentID,letter,ROW_NUMBER()OVER(PARTITION BY parentId ORDER BY ID) first_element,ROW_NUMBER()OVER(PARTITION BY parentId ORDER BY ID DESC) Last_elementFROM Child), CTE2 AS (SELECt c1.id,c1.parentid,c1.letter,c2.parentid as c2parentidFROM CTE c1INNER JOIN CTE c2ON c1.last_element = 1AND c2.first_element = 1AND c1.id +1 = c2.id), CTE3 AS (SELECt C.parentid,C.idFROM CTE2INNER JOIN child C ON CTE2.c2parentid = C.parentidAND C.letter = CTE2.letter)SELECt P.number, C.letterFROM Child CJOIN Parent P ON C.parentId = P.idLEFT JOIN CTE3 ON CTE3.id = C.idORDER BY P.number, ISNULL(CTE3.id,0) DESC, C.letter 

输出

number  letter1   A1   C2   C2   B3   B3   D

编辑

如果您ids不是顺序的，您可以更改CTE1并CTE2像这样使用ROW_NUMBER()OVER(ORDER BY ID) seq_id.

;WITH CTE AS (SELECt id,ParentID,letter,ROW_NUMBER()OVER(ORDER BY ID) seq_id,ROW_NUMBER()OVER(PARTITION BY parentId ORDER BY ID) first_element,ROW_NUMBER()OVER(PARTITION BY parentId ORDER BY ID DESC) Last_elementFROM Child), CTE2 AS (SELECt c1.id,c1.parentid,c1.letter,c2.parentid as c2parentidFROM CTE c1INNER JOIN CTE c2ON c1.last_element = 1AND c2.first_element = 1AND c1.seq_id + 1 = c2.seq_id)