Najprostszy sposób na rekurencyjne samouzłączenie?
Jaki jest najprostszy sposób wykonywania rekurencyjnego samoczynnego łączenia w SQL Server? Mam taki stolik:
PersonID | Initials | ParentID
1 CJ NULL
2 EB 1
3 MB 1
4 SW 2
5 YT NULL
6 IS 5
I chcę być w stanie uzyskać rekordy związane tylko z hierarchią zaczynającą się od konkretnej osoby. Więc jeśli poprosiłbym o hierarchię CJ ' a przez PersonID=1, otrzymałbym:
PersonID | Initials | ParentID
1 CJ NULL
2 EB 1
3 MB 1
4 SW 2
A za EBA dostałbym:
PersonID | Initials | ParentID
2 EB 1
4 SW 2
[3]}trochę utknąłem na tej puszce nie mogę myśleć, jak to zrobić, oprócz stałej głębi odpowiedzi opartej na kilku połączeniach. Będzie tak, jak to się dzieje, ponieważ my nie ma wielu poziomów, ale chciałbym zrobić to poprawnie.
Dzięki! Chris. 5 answers
WITH q AS
(
SELECT *
FROM mytable
WHERE ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
UNION ALL
SELECT m.*
FROM mytable m
JOIN q
ON m.parentID = q.PersonID
)
SELECT *
FROM q
Dodając warunek zamówienia, możesz zachować kolejność drzewa:
WITH q AS
(
SELECT m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
FROM mytable m
WHERE ParentID IS NULL
UNION ALL
SELECT m.*, q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
FROM mytable m
JOIN q
ON m.parentID = q.PersonID
)
SELECT *
FROM q
ORDER BY
bc
Zmieniając warunek ORDER BY
możesz zmienić kolejność rodzeństwa.
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-18 16:41:27
Używając CTEs możesz to zrobić w ten sposób
DECLARE @Table TABLE(
PersonID INT,
Initials VARCHAR(20),
ParentID INT
)
INSERT INTO @Table SELECT 1,'CJ',NULL
INSERT INTO @Table SELECT 2,'EB',1
INSERT INTO @Table SELECT 3,'MB',1
INSERT INTO @Table SELECT 4,'SW',2
INSERT INTO @Table SELECT 5,'YT',NULL
INSERT INTO @Table SELECT 6,'IS',5
DECLARE @PersonID INT
SELECT @PersonID = 1
;WITH Selects AS (
SELECT *
FROM @Table
WHERE PersonID = @PersonID
UNION ALL
SELECT t.*
FROM @Table t INNER JOIN
Selects s ON t.ParentID = s.PersonID
)
SELECT *
FROm Selects
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-18 16:37:59
Zapytanie Quassnoi ze zmianą dla dużej tabeli. Parents with more childs then 10: formatowanie jako str(5) The row_number()
WITH q AS ( SELECT m.*, CAST(str(ROW_NUMBER() OVER (ORDER BY m.ordernum),5) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc FROM #t m WHERE ParentID =0 UNION ALL SELECT m.*, q.bc + '.' + str(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.ordernum),5) COLLATE Latin1_General_BIN FROM #t m JOIN q ON m.parentID = q.DBID ) SELECT * FROM q ORDER BY bc
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-18 17:42:15
SQL 2005 lub nowszy, CTE są standardowym sposobem, jak na pokazanych przykładach.
SQL 2000, możesz to zrobić używając UDFs-
CREATE FUNCTION udfPersonAndChildren
(
@PersonID int
)
RETURNS @t TABLE (personid int, initials nchar(10), parentid int null)
AS
begin
insert into @t
select * from people p
where personID=@PersonID
while @@rowcount > 0
begin
insert into @t
select p.*
from people p
inner join @t o on p.parentid=o.personid
left join @t o2 on p.personid=o2.personid
where o2.personid is null
end
return
end
(które będą działać w 2005 roku, to po prostu nie jest standardowy sposób. To powiedziawszy, jeśli znajdziesz ten łatwiejszy sposób pracy, uruchom go)
Jeśli naprawdę musisz to zrobić w SQL7, możesz zrobić mniej więcej powyższe w sproc, ale nie możesz z niego wybrać - SQL7 nie obsługuje UDFs.
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-18 17:18:06
Sprawdź poniżej, aby pomóc zrozumieć koncepcję rekurencji CTE
DECLARE
@startDate DATETIME,
@endDate DATETIME
SET @startDate = '11/10/2011'
SET @endDate = '03/25/2012'
; WITH CTE AS (
SELECT
YEAR(@startDate) AS 'yr',
MONTH(@startDate) AS 'mm',
DATENAME(mm, @startDate) AS 'mon',
DATEPART(d,@startDate) AS 'dd',
@startDate 'new_date'
UNION ALL
SELECT
YEAR(new_date) AS 'yr',
MONTH(new_date) AS 'mm',
DATENAME(mm, new_date) AS 'mon',
DATEPART(d,@startDate) AS 'dd',
DATEADD(d,1,new_date) 'new_date'
FROM CTE
WHERE new_date < @endDate
)
SELECT yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'
FROM CTE
GROUP BY mon, yr, mm
ORDER BY yr, mm
OPTION (MAXRECURSION 1000)
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
2018-09-28 10:00:02