自联接查询 [英] Self-join Query
问题描述
是否可以仅使用join进行父子查询而无需遍历临时表?
Is it possible to do parent-child query just using join without looping through temporary table?
数据库示例:
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'，则需要的结果:
Desired result if choosed 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'，则需要的结果:
Desired result if choosed 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到menuid的最长分支中获取所有行，并在parent与menuid相同或与parent不匹配的情况下停止.
UPDATED :
I want to get all rows from the longest branch possible from nodes parent to menuid and stopped if the parent same with menuid or there is no menuid match with 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
对于上面所需的示例，可以尝试以下查询:
For desired sample above can try this query :
sp_get_parent '/master/kapal.aspx'
AND
sp_get_parent '/master/tujuan.aspx'
推荐答案
在SQL Server中，有关如何查询分层数据的每个问题的答案都是使用递归公用表表达式.
In SQL Server, the answer to every question about how to query hierarchical data is using a recursive common table expression.
在您的情况下，由于要获得最长的分支，应添加一个计数列:
In your case, since you want to get the longest branch, you should add a counting column:
;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
此查询将为您提供距离其父级最远的叶子.
This query will get you the leaf that's the furthest away from it's top parent.
更新
根据您的评论，我认为这是您想要的:
Update
Based on your comment, I think this is what you are looking for:
;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应该是这样的:
Update #2
Now that your question is changed, you seem to just want to traverse from leaf to top parent. In that case, your recursive CTE should be something like this:
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
这篇关于自联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
