你可以做这样的事情。
- 通过使用ROW_NUMBER()和识别每个父母的第一个和最后一个字母PARTITION BY
- 将前一个的最后一条记录与下一个id的第一条记录相匹配id。
- 检查第二个父 ID 是否有与上面选择的字母匹配的字母
- 使用 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)
