Simulação de CONNECT BY PRIOR do ORACLE no SQL SERVER

Gostaria de obter a funcionalidade de CONNECT BY PRIOR do ORACLE no SQL SERVER 2000/2005/2008?

Por favor me ajude

   

A maneira padrão do SQL de implementar consultas recursivas, conforme implementado, por exemplo, pelo IBM DB2 e pelo SQL Server , é a cláusula WITH . Veja este artigo para um exemplo de conversão de CONNECT BY em um WITH (tecnicamente um CTE recursivo ) – o exemplo é para o DB2, mas acredito que funcionará também no SQL Server.

Edit: aparentemente, o solicitante original requer um exemplo específico, aqui está um do site da IBM cuja URL eu já forneci. Dada uma tabela:

 CREATE TABLE emp(empid INTEGER NOT NULL PRIMARY KEY, name VARCHAR(10), salary DECIMAL(9, 2), mgrid INTEGER); 

onde mgrid referência a um gerente de empid , a tarefa é obter os nomes de todos que se reportam direta ou indiretamente a Joan . Na Oracle, isso é um simples CONNECT :

 SELECT name FROM emp START WITH name = 'Joan' CONNECT BY PRIOR empid = mgrid 

No SQL Server, IBM DB2 ou PostgreSQL 8.4 (bem como no padrão SQL, pelo que vale a pena ;-), a solução perfeitamente equivalente é, em vez disso, uma consulta recursiva (syntax mais complexa, mas, na verdade, ainda mais poder e flexibilidade) ):

 WITH n(empid, name) AS (SELECT empid, name FROM emp WHERE name = 'Joan' UNION ALL SELECT nplus1.empid, nplus1.name FROM emp as nplus1, n WHERE n.empid = nplus1.mgrid) SELECT name FROM n 

A cláusula START WITH do Oracle torna-se o primeiro SELECT nested, o caso base da recursion, a ser UNION ed com a parte recursiva que é apenas outro SELECT .

O sabor específico do SQL Server de WITH está documentado no MSDN , que também fornece diretrizes e limitações para o uso dessa palavra-chave, além de vários exemplos.

A resposta do @Alex Martelli é ótima! Mas ele funciona apenas para um elemento no momento ( WHERE name = 'Joan' ) Se você retirar a cláusula WHERE , a consulta retornará todas as linhas raiz juntas …

Eu mudei um pouco para a minha situação, por isso pode mostrar a tree inteira por uma mesa.

definição de tabela:

 CREATE TABLE [dbo].[mar_categories] ( [category] int IDENTITY(1,1) NOT NULL, [name] varchar(50) NOT NULL, [level] int NOT NULL, [action] int NOT NULL, [parent] int NULL, CONSTRAINT [XPK_mar_categories] PRIMARY KEY([category]) ) 

( level é literalmente o nível de uma categoria 0: root, 1: first level after root, …)

e a consulta:

 WITH n(category, name, level, parent, concatenador) AS ( SELECT category, name, level, parent, '('+CONVERT(VARCHAR (MAX), category)+' - '+CONVERT(VARCHAR (MAX), level)+')' as concatenador FROM mar_categories WHERE parent is null UNION ALL SELECT m.category, m.name, m.level, m.parent, n.concatenador+' * ('+CONVERT (VARCHAR (MAX), case when ISNULL(m.parent, 0) = 0 then 0 else m.category END)+' - '+CONVERT(VARCHAR (MAX), m.level)+')' as concatenador FROM mar_categories as m, n WHERE n.category = m.parent ) SELECT distinct * FROM n ORDER BY concatenador asc 

(Você não precisa concatenar o campo de level , eu fiz apenas para tornar mais legível)

A resposta para esta consulta deve ser algo como:

retorno de sql

Eu espero que isso ajude alguém!

agora, eu estou querendo saber como fazer isso no MySQL … ^^

Eu não usei connect by prior, mas uma pesquisa rápida mostra que ele é usado para estruturas de trees. No SQL Server, você usa expressões de tabela comuns para obter funcionalidade semelhante.