tabla - CTE de SQL recursivo y ordenamiento de clasificación personalizado

with en sql

Question

Imagen que está creando un esquema de base de datos para un tablero de discusión enhebrado. ¿Hay una manera eficiente de seleccionar una lista ordenada correctamente para un hilo dado? El código que he escrito funciona pero no se ordena de la manera que me gustaría.

Digamos que tienes estos datos:

ID | ParentID ----------------- 1 | null 2 | 1 3 | 2 4 | 1 5 | 3

Entonces se supone que la estructura debe verse así:

1 |- 2 | |- 3 | | |- 5 |- 4

Idealmente, en el código, queremos que el conjunto de resultados aparezca en el siguiente orden: 1, 2, 3, 5, 4
PROBLEMA: Con el CTE que escribí, en realidad está siendo devuelto como: 1, 2, 4, 3, 5

Sé que esto sería fácil de agrupar / ordenar mediante el uso de LINQ, pero me resisto a hacer esto en la memoria. Parece que es la mejor solución en este momento ...

Aquí está el CTE que estoy usando actualmente:

with Replies as (   
    select c.CommentID, c.ParentCommentID 1 as Level
        from Comment c
        where ParentCommentID is null and CommentID = @ParentCommentID

    union all

    select c.CommentID, c.ParentCommentID, r.Level + 1 as Level
       from Comment c
       inner join Replies r on c.ParentCommentID = r.CommentID
)

select * from Replies

Cualquier ayuda sería apreciada; ¡Gracias!

Soy nuevo en SQL y nunca había escuchado sobre el tipo de datos hierarchyid. Después de leer sobre este comentario , decidí que podría querer incorporar esto en mi diseño. Voy a experimentar con esto esta noche y publicar más información si tengo éxito. Actualizar
Resultado devuelto de mis datos de muestra, utilizando la sugerencia de dance2die:

ID | ParentID | Level | DenseRank ------------------------------------- 15 NULL 1 1 20 15 2 1 21 20 3 1 17 22 3 1 22 15 2 2 31 15 2 3 32 15 2 4 33 15 2 5 34 15 2 6 35 15 2 7 36 15 2 8

Answer 1

Considere almacenar toda la jerarquía (con activadores para actualizarla si cambia) en un campo.

Este campo en su ejemplo tendría: 1 1.2 1.2.3 1.2.5 1.4

entonces solo tienes que ordenar en ese campo, prueba esto y mira:

create table #temp (test varchar (10))
insert into #temp (test)
select ''1''
union select ''1.2''
union select ''1.2.3''
union select ''1.2.5''
union select ''1.4''
select * from #temp order by test asc

Answer 2

Hmmmm, no estoy seguro de si tu estructura es la más adecuada para este problema. De todos modos, no puedo pensar en ordenar los datos como lo quieres dentro de la consulta anterior.

Lo mejor que puedo pensar es si tienes una tabla principal que vincule tus comentarios (por ejemplo, una tabla de temas). Si lo hace, debería poder simplemente unir sus respuestas a eso (tendrá que incluir la columna correcta, obviamente), y luego puede ordenar por el topicID, Level para obtener el orden de clasificación que busca (o cualquier otra información sobre la tabla de tema representa un buen valor para la clasificación).

Answer 3

Debe usar hierarchyid (solo sql2008) o un conjunto de concatenación de cadenas (o bytes).

Answer 4

Estoy seguro de que te encantará esto. Recientemente descubrí la función Dense_Rank () , que es para "clasificar dentro de la partición de un conjunto de resultados" de acuerdo con MSDN

Consulte el código a continuación y cómo se clasifica "CommentID".

Por lo que yo entiendo, estás tratando de dividir tu conjunto de resultados por ParentCommentID.

Preste atención a la columna "denserank".

with Replies (CommentID, ParentCommentID, Level) as 
(
        select  c.CommentID, c.ParentCommentID, 1 as Level
        from    Comment c
        where   ParentCommentID is null and CommentID = 1

        union all

        select  c.CommentID, c.ParentCommentID, r.Level + 1 as Level
        from    Comment c
                inner join Replies r on c.ParentCommentID = r.CommentID
)
select  *,
        denserank = dense_rank() over (partition by ParentCommentID order by CommentID)
from    Replies
order by denserank

Resultado a continuación