自连接查询

是否可以仅使用 join 进行父子查询而不循环遍历临时表？

数据库样本：

menuid  name                parent  url
----------------------------------------------------------
A0000   Master              A0000   #
A0001   Rekening            A0000   /master/rekening.aspx
A0002   Master Nominal      A0001   /master/nominal.aspx
A0003   Master Satuan Other A0001   /master/satuan.aspx
A0004   Master Kondisi      A0000   /master/kondisi.aspx
A0005   Master Tujuan       A0003   /master/tujuan.aspx
A0006   Master Item         A0003   /master/item.aspx
A0007   Master Warehouse    A0000   /master/warehouse.aspx
A0008   Master Kapal        A0006   /master/kapal.aspx

选择想要的结果uri = '/master/kapal.aspx' :

menuid  name                parent  url
----------------------------------------------------------
A0000   Master              A0000   #
A0001   Rekening            A0000   /master/rekening.aspx
A0003   Master Satuan Other A0001   /master/satuan.aspx
A0006   Master Item         A0003   /master/item.aspx
A0008   Master Kapal        A0006   /master/kapal.aspx

选择想要的结果uri = /master/tujuan.aspx' :

menuid  name                parent  url
----------------------------------------------------------
A0000   Master              A0000   #
A0001   Rekening            A0000   /master/rekening.aspx
A0005   Master Tujuan       A0003   /master/tujuan.aspx

示例查询：

declare @menuid varchar(255) = 'menuid'
declare @parent varchar(255) = 'parent'
declare @temp_parent varchar(255)
declare @i smallint = 0

delete from temp_menu
while (@menuid <> @parent)
begin
  if(@i = 0) 
  begin
    insert into temp_menu
    select * from menu where uri = '/master/kapal.aspx'
    select @menuid = menuid, @parent = parent from menu where uri = '/master/kapal.aspx'
    set @i = 1;
    end
  else
  begin
    insert into temp_menu
    select * from menu where menuid = @parent
    select @menuid = menuid, @temp_parent = parent from menu where menuid = @parent
    set @parent = @temp_parent;
    end
end
select * from temp_menu

具有层次结构的示例：

A0000
|_______________________
|               |       |
A0001           A0004   A0007
|________
|       |
A0002   A0003
        |_______
        |       |
        A0005   A0006
                |
                A0008

更新 ：我想从节点中获取最长分支的所有行parent to menuid并停止，如果parent与menuid或者没有menuid与 匹配parent.

添加脚本和示例

IF OBJECT_ID('dbo.menu', 'U') IS NOT NULL
  DROP TABLE dbo.menu
GO

IF OBJECT_ID('dbo.temp_menu', 'U') IS NOT NULL
  DROP TABLE dbo.temp_menu
GO

IF OBJECTPROPERTY(object_id('dbo.sp_get_parent'), N'IsProcedure') = 1
  DROP PROCEDURE dbo.sp_get_parent
GO

create table dbo.menu (
menuid varchar(255)
, name varchar(255)
, parent varchar(255)
, uri varchar(255)
);

insert into dbo.menu (menuid, name, parent, uri)
values ('A0000', 'Master', 'A0000', '#')
, ('A0001', 'Rekening', 'A0000', '/master/rekening.aspx')
, ('A0002', 'Master Nominal', 'A0001', '/master/nominal.aspx')
, ('A0003', 'Master Satuan Other', 'A0001', '/master/satuan.aspx')
, ('A0004', 'Master Kondisi', 'A0000', '/master/kondisi.aspx')
, ('A0005', 'Master Tujuan', 'A0003', '/master/tujuan.aspx')
, ('A0006', 'Master Item', 'A0003', '/master/item.aspx')
, ('A0007', 'Master Warehouse', 'A0000', '/master/warehouse.aspx')
, ('A0008', 'Master Kapal', 'A0006', '/master/kapal.aspx');

create table dbo.temp_menu (
menuid varchar(255)
, name varchar(255)
, parent varchar(255)
, uri varchar(255)
);

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[sp_get_parent]
@uri VARCHAR (255)
AS

declare @menuid varchar(255) = 'menuid'
declare @parent varchar(255) = 'parent'
declare @temp_parent varchar(255)
declare @i smallint = 0

delete from temp_menu
while (@menuid <> @parent)
begin
  if(@i = 0) 
  begin
    insert into temp_menu
    select * from menu where uri = @uri
    select @menuid = menuid, @parent = parent from menu where uri = @uri
    set @i = 1;
  end
  else
  begin
    insert into temp_menu
    select * from menu where menuid = @parent
    select @menuid = menuid, @temp_parent = parent from menu where menuid = @parent
    set @parent = @temp_parent;
  end
end
select * from temp_menu order by menuid asc
GO

对于上面所需的示例可以尝试以下查询：

sp_get_parent '/master/kapal.aspx'

AND

sp_get_parent '/master/tujuan.aspx'

在 SQL Server 中，有关如何查询分层数据的每个问题的答案都是使用递归公用表表达式。

在您的情况下，由于您想要获得最长的分支，因此您应该添加一个计数列：

;WITH CTE AS
(
     SELECT menuid, name, parent, url, 0 as level
     FROM menu WHERE parent = menuid -- Usually, the parent column is simply nullable
     UNION ALL
     SELECT menu.menuid, menu.name, menu.parent, menu.url, level + 1
     FROM menu 
     INNER JOIN CTE ON menu.parent = CTE.menuid 
     AND menu.parent <> CTE.parent -- This is why parent column is nullable :-)
)

SELECT TOP 1 *
FROM CTE
ORDER BY Level DESC 

此查询将为您提供距其顶层父级最远的叶子。

Update
根据您的评论，我认为这就是您正在寻找的：

;WITH CTERecursion AS
(
     SELECT menuid, 
            name, 
            parent, 
            url, 
            0 as level,
            menuid as TopLevelParent
     FROM menu WHERE parent = menuid -- Usually, the parent column is simply nullable

     UNION ALL
     SELECT menu.menuid, 
            menu.name, 
            menu.parent, 
            menu.url, 
            level + 1,
            TopLevelParent
     FROM menu 
     INNER JOIN CTERecursion CTE ON menu.parent = CTE.menuid 
     AND menu.menuid <> CTE.menuid -- This is why parent column is nullable :-)

), CTELongestPath AS
(
    SELECT TOP 1 TopLevelParent
    FROM CTERecursion
    ORDER BY Level DESC 
)

SELECT menuid, name, parent, url
FROM CTERecursion r
INNER JOIN CTELongestPath l ON r.TopLevelParent = r.TopLevelParent

更新#2
现在你的问题改变了，你似乎只想从叶子遍历到顶层父级。在这种情况下，您的递归 CTE 应该是这样的：

DECLARE @url varchar(100) = '/master/kapal.aspx';

;WITH CTERecursion AS
(
     SELECT menuid, 
            name, 
            parent, 
            url
     FROM menu 
     WHERE url = @url

     UNION ALL
     SELECT menu.menuid, 
            menu.name, 
            menu.parent, 
            menu.url
     FROM menu 
     INNER JOIN CTERecursion CTE ON menu.menuid = CTE.parent
     AND menu.menuid <> CTE.menuid -- This is why parent column is nullable :-)
)

SELECT menuid, name, parent, url
FROM CTERecursion