SQL Server: Jak uzyskać wszystkie rekordy potomne z identyfikatorem rodzica w tabeli samoobsługowej
Cześć mam tabelę, która odwołuje się do siebie i muszę być w stanie wybrać rodzica i wszystkie jego rekordy potomne z danego identyfikatora rodzica.
Moja tabela wygląda następująco:
ID | ParentID | Name
-----------------------
1 NULL A
2 1 B-1
3 1 B-2
4 2 C-1
5 2 C-2
Więc dla powyższego przykładu chciałbym być w stanie przekazać wartość 1 i uzyskać wszystkie powyższe rekordy.
Do tej pory wymyśliłem następującą rekurencyjną funkcję table-valued-function, ale nie zachowuje się ona zgodnie z oczekiwaniami (zwraca tylko pierwszy rekord).
CREATE FUNCTION [dbo].[SelectBranches]
(
@id INT
,@parentId INT
)
RETURNS @branchTable TABLE
(
ID INT
,ParentID INT
,Name INT
)
AS
BEGIN
IF @branchId IS NOT NULL BEGIN
INSERT INTO @branchTable
SELECT
ID
,ParentID
,Name
FROM
tblLinkAdvertiserCity
WHERE
ID = @id
END
INSERT INTO @branchTable
SELECT
br.ID
,br.ParentID
,br.Name
FROM
@branchTable b
CROSS APPLY
dbo.SelectBranches(NULL, b.ParentID) br
RETURN
END
GO
3 answers
Możesz spróbować tego
DECLARE @Table TABLE(
ID INT,
ParentID INT,
NAME VARCHAR(20)
)
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 1, NULL, 'A'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 2, 1, 'B-1'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 3, 1, 'B-2'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 4, 2, 'C-1'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 5, 2, 'C-2'
DECLARE @ID INT
SELECT @ID = 2
;WITH ret AS(
SELECT *
FROM @Table
WHERE ID = @ID
UNION ALL
SELECT t.*
FROM @Table t INNER JOIN
ret r ON t.ParentID = r.ID
)
SELECT *
FROM ret
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-11-06 09:09:50
Jeśli nie używasz Oracle, twoja struktura tabeli nie jest odpowiednia dla opisanego problemu. To, co próbujesz zrobić, to chwycić hierarchię (przemierzanie struktury drzewa).
Istnieje Artykuł More Trees & Hieraries in SQL , który opisuje jedną metodę rozwiązania problemu hierarchii. Zasadniczo dodaje kolumnę "lineage" opisującą hierarchię do każdego wiersza.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-11-06 09:21:36
Rekurencja w CTE
wygląda trochę drogo, więc napisałem tę funkcję, która wykorzystuje rekurencyjne wywołanie funkcji, ale znacznie szybciej niż CTE
rekurencja.
CREATE FUNCTION [dbo].[Fn_GetSubCategories]
(
@p_ParentCategoryId INT
) RETURNS @ResultTable TABLE
(
Id INT
)
AS
BEGIN
--Insert first level subcategories.
INSERT INTO @ResultTable
SELECT Id FROM Category WHERE ParentCategoryId = @p_ParentCategoryId OR Id = @p_ParentCategoryId
DECLARE @Id INT
DECLARE @ParentCategory TABLE(Id INT)
DECLARE cur_categories CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
SELECT Id FROM Category WHERE ParentCategoryId = @p_ParentCategoryId and Id != @p_ParentCategoryId
OPEN cur_categories
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM cur_categories INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert remaining level sub categories.
IF EXISTS(SELECT 1 FROM Category WHERE ParentCategoryId = @Id AND Id != @Id)
BEGIN
INSERT INTO @ResultTable
SELECT DISTINCT C.Id from Fn_GetSubCategories(@Id) C INNER JOIN @ResultTable R ON C.Id != R.Id
END
FETCH NEXT FROM cur_categories INTO @Id
END
--Delete duplicate records
;WITH CTE AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) AS RN FROM @ResultTable)
DELETE FROM CTE WHERE RN<>1
END
CLOSE cur_categories
DEALLOCATE cur_categories
RETURN
END
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-04-27 12:37:58