当前位置: 动力学知识库 > 问答 > 编程问答 >

SQL Server CTE hierarchy?

问题描述:

I have a table which has hierarchy in it.

Lets start with Id = 5; this is the child. (a given start parameter - from user)

Algorithm:

  • give me the first value which you have encountered for id = 5
  • if you haven't found value for id = 5, go to its parent and give me his id
  • if this parent also doesn't have a value - go to its parent ... etc (until parent has no parent - parentId = 0)

p.s. the result here should be 7.

if 7 value was empty so : 9

end if 9 was also empty so : 1

I'm trying to do this with CTE but no success..

p.s. I want a solution with CTE. :)

What I have tried (don't even look at it - it doesn't work):

WITH cte AS (

SELECT

id,VALUE,parentid

FROM [test].[dbo].[tmp] WHERE id=5

UNION ALL

SELECT

id,VALUE,parentid

FROM [tmp] WHERE parentId=cte.parentId ) //ERROR : The multi-part identifier "cte.parentId" could not be bound.

SELECT * FROM cte

网友答案:

You need to join in your cte:

with cte as (
select
    id,
    value,
    parentid
from
    [tmp]
where 
    id=5
union all
select
    tmp.id,
    coalesce(cte.value, tmp.value) as value,
    tmp.parentid
from  
    [tmp]
    inner join cte on
        tmp.id = cte.parentId
)
select
    max(value) as value
from
    cte

If you're going to reference a table, you need to make sure that you've joined it somewhere in your from clause. In this case, you hadn't, which is why it was throwing the error.

分享给朋友:
您可能感兴趣的文章:
随机阅读: