递归SQL关系查询

Question

我被困在一个SQL查询上，任何人都可以帮助我吗？，下面是我的表[LEVEL]

有列

Id int, Name Varchar(50), ObjectId Int, ParentId Int, LevelId int

以下为记录：

INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('Test',1,NULL,1);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('3rdItem',2,1,2);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('1stItem',3,2,3);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('ehs',4,3,4);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('2ndItem',5,3,4);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('test tom',6,3,4);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('waterweg23',7,3,4);
INSERT INTO [LEVEL] (Name,ObjectId,ParentId,LevelId) VALUES('ehs',4,2,1);

我需要得到具有最大级别Id的父母的对象。

这里记录" ehs“有两个父级，即(3 & 2)，它应该显示只有父3的ehs，因为对象3的id级别高于2。

所以我想得到所有的记录，父母有最大的levelId

有谁可以帮我？?

Answer 1

如果您想要递归的，请尝试如下：

with cte as (
    select L.Name, L.ObjectId, L.LevelID, L.ParentId, LP.LevelId as ParentLevelId
    from [LEVEL] as L
        left outer join [LEVEL] as LP on LP.ObjectID = L.ParentId
    union all
    select L.Name, L.ObjectId, L.LevelID, c.ParentId, c.ParentLevelId
    from [LEVEL] as L
        inner join cte as c on c.ObjectId = L.ParentId
), cte2 as (
    select
        Name, ObjectID, ParentId, LevelId, ParentLevelId,
        row_number() over(partition by Name, ObjectID order by ParentLevelId desc) as row_num
    from cte
)
select
    Name, ObjectID, LevelId, ParentId, ParentLevelId
from cte2
where row_num = 1
order by name asc

sql fiddle

Answer 2

尝尝这个。

DECLARE @LEVEL TABLE (Id int, Name Varchar(50), ObjectId Int, ParentId Int, LevelId int)

INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('Test',1,NULL,1);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('3rdItem',2,1,2);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('1stItem',3,2,3);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('ehs',4,3,4);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('2ndItem',5,3,4);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('test tom',6,3,4);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('waterweg23',7,3,4);
INSERT INTO @LEVEL (Name,ObjectId,ParentId,LevelId) VALUES('ehs',4,2,1);

SELECT
    l.ID,
    l.Name,
    l.ObjectID,
    l.ParentID,
    l.LevelID
FROM
    @LEVEL AS l INNER JOIN (
                        SELECT
                            ObjectID,
                            MAX(LevelID) AS LevelID
                        FROM
                            @LEVEL
                        GROUP BY
                            ObjectID
                        ) AS s
                    ON
                        l.ObjectID = s.ObjectID
                            AND
                        l.LevelID = s.LevelID
ORDER BY
    l.ObjectID