递归CTE查找父记录 [英] Recursive CTE to find parent records
问题描述
首先,我必须承认我对sql server的,但是我认为这是最好的方法。
first i must admit that i'm not very familiar with sql server's recursive CTE's but i think this is the best approach.
我有一个表 tabData
。它的PK名为 idData
,并且有一个自引用FK fiData
。
I have a table tabData
. Its PK is named idData
and there is a self referencing FK fiData
.
因此fiData引用父记录和 SELECT * FROM tabData WHERE idData = fiData
返回父记录的所有数据。这既简单又快速。但是,如何以自然顺序从给定记录中获取所有父母?
假设有一个孩子(idData = 4)和3个父母(第一个父母是idData = 3的记录):
So fiData references the parent record and SELECT * FROM tabData WHERE idData=fiData
returns all data of the parent. This is simple and fast. But how to get all parents from a given record in the natural order?
Say there is one child(idData=4) with 3 parents (first parent is the record with idData=3):
idData fiData
4 3
3 2
2 1
1 NULL
我认为递归CTE是必经之路,但我对其语法不太满意。
那么实现返回所有父母的CTE的正确方法是什么?
I thought recursive CTE is the way to go, but i don't get along well with its syntax. So what is the correct way to implement the CTE which returns all parents?
我尝试了以下操作,但结果却是错误的(3,4而是之3,2,1):
(要测试,我为我和您创建了一个临时表)
I tried following, but it gives me the wrong result(3,4 instead of 3,2,1): (To test it i created a temporary table for me and you)
IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tabData_Temp'))
BEGIN
CREATE TABLE [dbo].[tabData_Temp](
[idData] [int] NOT NULL,
[fiData] [int] NULL,
CONSTRAINT [PK_tabData_Temp] PRIMARY KEY CLUSTERED
(
[idData] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
);
ALTER TABLE [dbo].[tabData_Temp] WITH CHECK ADD CONSTRAINT [FK_tabData_Temp] FOREIGN KEY([fiData])
REFERENCES [dbo].[tabData_Temp] ([idData]);
ALTER TABLE [dbo].[tabData_Temp] CHECK CONSTRAINT [FK_tabData_Temp];
INSERT INTO [dbo].[tabData_Temp](idData,fiData)VALUES(1,NULL);
INSERT INTO [dbo].[tabData_Temp](idData,fiData)VALUES(2,1);
INSERT INTO [dbo].[tabData_Temp](idData,fiData)VALUES(3,2);
INSERT INTO [dbo].[tabData_Temp](idData,fiData)VALUES(4,3);
END
/* here comes the (not working) recursive CTE */
Declare @fiData int;
SET @fiData = 3;
WITH PreviousClaims(idData,fiData)
AS(
SELECT parent.idData,parent.fiData
FROM tabData_temp parent
WHERE parent.idData = @fiData
UNION ALL
SELECT child.idData,child.fiData
FROM tabData_temp child
INNER JOIN PreviousClaims parent ON parent.idData = child.fiData
)
SELECT idData
FROM PreviousClaims;
/* end of recursive CTE */
DROP TABLE [dbo].[tabData_Temp];
谢谢。
推荐答案
更改为:
INNER JOIN PreviousClaims parent ON parent.fiData = child.idData
给我想要的结果。
这篇关于递归CTE查找父记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!