在同一个表的id和上级id怎么相互查询

[qq_21880229]

想建立一个视图，现在的tableA 大概是这样的
tableA
id up_id name level
1 0 1 1
2 1 2 2
3 1 3 2
4 2 4 3
5 3 5 3
6 3 6 3
7 2 7 3
8 2 8 3
9 3 9 3
上面这个表的大概作用就是一棵树
level=1 1

level=2 2 3

level=3 4 2 8 5 6 9

我想根据tableA，建立一个视图viewA
id level_3_name level_2_name level_1_name
4 4 2 1
2 2 2 1
8 8 2 1
5 5 3 1
6 6 3 1
9 9 3 1

想了一天，都没想出什么办法..求大神支支招..

[丰云]

又是自关联表。。。。 两类解决方法： 一是嵌套递归追溯，这类方法老套低效高耗隐患大。 二是添加path字段，记录树形结构的路径，这类做法搞笑低耗无公害 是个人都能看出来用第二种方法好，唯一的难点是，需要人的脑筋稍微灵光点，能够理解这其中的妙处。。。。

[丰云]

打字快了打错了，不能修改。。。 二是添加path字段，记录树形结构的路径，这类做法高效低耗无公害

[二月十六]

--测试数据
if not object_id(N'Tempdb..#tableA') is null
	drop table #tableA
Go
Create table #tableA([id] int,[up_id] int,[name] int,[level] int)
Insert #tableA
select 1,0,1,1 union all
select 2,1,2,2 union all
select 3,1,3,2 union all
select 4,2,4,3 union all
select 5,3,5,3 union all
select 6,3,6,3 union all
select 7,2,7,3 union all
select 8,2,8,3 union all
select 9,3,9,3
Go
--测试数据结束
;WITH ctea AS (
SELECT  id,name,up_id
FROM    #tableA
WHERE   id NOT IN ( SELECT  up_id
                    FROM    #tableA )
)
SELECT  ctea.id ,
        ctea.name AS level_3_name ,
        a.name AS level_2_name ,
        b.name AS level_3_name
FROM    ctea
        JOIN #tableA a ON ctea.up_id = a.id
		JOIN #tableA b ON a.up_id = b.id

[听雨停了]

if not object_id(N'Tab') is null
	drop table Tab
Go
Create table Tab([id] int,[up_id] int,[name] int,[level] int)
Insert Tab
select 1,0,1,1 union all
select 2,1,2,2 union all
select 3,1,3,2 union all
select 4,2,4,3 union all
select 5,3,5,3 union all
select 6,3,6,3 union all
select 7,2,7,3 union all
select 8,2,8,3 union all
select 9,3,9,3
--测试数据结束

CREATE VIEW tree_view AS 
SELECT a.id,
       a.name AS level_3_name,
       b.level_2_name,
       b.level_1_name
FROM   Tab a
       INNER JOIN (
                SELECT a.name  AS [level_1_name],
                       b.name  AS [level_2_name]
                FROM   Tab A
                       INNER JOIN Tab b
                            ON  a.name = b.up_id
                WHERE  a.[level] = 1
            ) b
            ON  a.up_id = b.level_2_name

SELECT * FROM Tree_view     

id          level_3_name level_2_name level_1_name
----------- ------------ ------------ ------------
4           4            2            1
5           5            3            1
6           6            3            1
7           7            2            1
8           8            2            1
9           9            3            1     

[RINK_1]

if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[up_id] int,[name] int,[level] int)
Insert #T
select 1,0,1,1 union all
select 2,0,2,1 union all
select 3,1,3,2 union all
select 4,2,4,2 union all
select 5,3,5,3 union all
select 6,3,6,3 union all
select 7,2,7,2 union all
select 8,4,8,3 union all
select 9,3,9,3
Go

select A.ID,
       A.name as level3_name,
       case when A.level=2 then A.name else ISNULL(B.name,A.name) end as level2_name,
       COALESCE(C.NAME,B.NAME,A.NAME) as level3_name
from #T A
outer apply (select * from #T where A.up_id=id) as B
outer apply (select name,id from #T where B.up_id=id) as C
where A.level=指定级别

[qq_16807025]

[吉普赛的歌]

引用 6 楼 yisuylm 的回复:

[quote=引用 1 楼 foren_whb 的回复:] 又是自关联表。。。。 两类解决方法： 一是嵌套递归追溯，这类方法老套低效高耗隐患大。 二是添加path字段，记录树形结构的路径，这类做法搞笑低耗无公害 是个人都能看出来用第二种方法好，唯一的难点是，需要人的脑筋稍微灵光点，能够理解这其中的妙处。。。。

这才是正解！！！ 我看了很多sql方面的问题，版主也很热情，但我感觉都是往沟里带，我觉得很多问题的解决应该借助合理的设计来解决，而不是靠运用脚本的“蛮力”来解决，就楼主这样的问题，增加字段path可以得到很好的解决，我觉得大家应该给初学者正确的思路和方法[/quote] 各有利弊吧。 方法一 维护成本低， 一般情况下其实效率也能接受; 方法二 直观，但维护成本高。 有些地方是两种方式一起在用的。 另外， 按楼主的本意， 以及面向对象的想法， 树形结构数据其实最合适的不是一般数据库， 而是NoSQL， 比如： Mongodb 之类的。 存进去就是树， 取出来也是树， 根本不需要任何的转换， 也不需要你脑子有多“灵光”。

[日月路明]

引用 1 楼 foren_whb 的回复:

又是自关联表。。。。 两类解决方法： 一是嵌套递归追溯，这类方法老套低效高耗隐患大。 二是添加path字段，记录树形结构的路径，这类做法搞笑低耗无公害 是个人都能看出来用第二种方法好，唯一的难点是，需要人的脑筋稍微灵光点，能够理解这其中的妙处。。。。

这才是正解！！！ 我看了很多sql方面的问题，版主也很热情，但我感觉都是往沟里带，我觉得很多问题的解决应该借助合理的设计来解决，而不是靠运用脚本的“蛮力”来解决，就楼主这样的问题，增加字段path可以得到很好的解决，我觉得大家应该给初学者正确的思路和方法

[majiade]

可能还是得看业务吧，好比有些企业，成品F1，F2。BOM下共用半成品 B1，好啦，你告诉我，你的PATH应该存什么内容？ F1 | | B1 M1 ------------------------------------------ F2 | | BS1 M2 | B1