복붙노트

[SQL] SQL 서버 데이터 루프 2005 재귀 쿼리는 - 그것이 가능할까요?

SQL

SQL 서버 데이터 루프 2005 재귀 쿼리는 - 그것이 가능할까요?

나는 표준 상사 / 부하 직원 테이블을 가지고있다. 나는 (ID로 지정) 상사와 그의 부하 직원 (및 subrodinates 등)을 선택해야합니다. 불행하게도 현실 세계의 데이터는 몇 가지 루프를 (예를 들어, 두 회사의 소유자가 자신의 상사로 각각 다른 세트가) 있습니다. CTE를 갖는 단순 재귀 쿼리이 (100 초과의 최대 순환 레벨)에 초크. 직원은 여전히 ​​선택 할 수 있습니까? 나는 그들 각각은 일단 선택 단지, 그들이 선택하는 순서와하지 신경. 추가 : 당신은 내 쿼리를 원하는? OK ... 음 ... 꽤 분명하지만입니다 I 불구하고 - 여기있다 :

with
UserTbl as -- Selects an employee and his subordinates.
(
    select a.[User_ID], a.[Manager_ID] from [User] a WHERE [User_ID] = @UserID
    union all
    select a.[User_ID], a.[Manager_ID] from [User] a join UserTbl b on (a.[Manager_ID]=b.[User_ID])
)
select * from UserTbl

2 추가 : 아, 경우에 그것은 분명하지 않다 - 이것은 생산 시스템과 내가 (기본적으로 보고서의 종류를 추가) 약간의 업그레이드를 수행해야합니다. 따라서, 나는 그것을 피할 수있는 경우 데이터를 수정하지 않으려는 것입니다.

해결법

  1. ==============================

    1.나는 잠시왔다 알고 있지만 나는 모든 단일 솔루션을 시도하고 여기 내 연구 결과를 요약 한 것입니다 나는 내 경험을 공유해야한다고 생각 (AN 어쩌면이 게시물?) :

    나는 잠시왔다 알고 있지만 나는 모든 단일 솔루션을 시도하고 여기 내 연구 결과를 요약 한 것입니다 나는 내 경험을 공유해야한다고 생각 (AN 어쩌면이 게시물?) :

    이 모든 일을하는 데, 나는 그들이 재귀 적 방법을 사용하여이 코드 (C #을) 및 필터 [대상] 직원의 전체 집합을 투기의 아이디어를 내놓았다. 그럼 난 데이터 테이블에 직원의 필터링 된 목록을 작성하고 임시 테이블로 내 저장 프로 시저로 보냅니다. 내 불신에,이 모두 작고 상대적으로 큰 테이블 (I는 35,000 행까지의 테이블을 시도)에 대한 가장 빠르고 가장 유연한 방법으로 증명했다.

  2. ==============================

    2.이 초기 재귀 링크를 작동하지만 더 이상 링크 작동하지 않을 수 있습니다

    이 초기 재귀 링크를 작동하지만 더 이상 링크 작동하지 않을 수 있습니다

    DECLARE @Table TABLE(
        ID INT,
        PARENTID INT
)

INSERT INTO @Table (ID,PARENTID) SELECT 1, 2

INSERT INTO @Table (ID,PARENTID) SELECT 2, 1

INSERT INTO @Table (ID,PARENTID) SELECT 3, 1

INSERT INTO @Table (ID,PARENTID) SELECT 4, 3

INSERT INTO @Table (ID,PARENTID) SELECT 5, 2


SELECT * FROM @Table

DECLARE @ID INT

SELECT @ID = 1

;WITH boss (ID,PARENTID) AS (
    SELECT  ID,
            PARENTID
    FROM    @Table
    WHERE   PARENTID = @ID
),
 bossChild (ID,PARENTID) AS (
    SELECT  ID,
            PARENTID
    FROM    boss
    UNION ALL
    SELECT  t.ID,
            t.PARENTID
    FROM    @Table t INNER JOIN
            bossChild b ON t.PARENTID = b.ID
    WHERE   t.ID NOT IN (SELECT PARENTID FROM boss)
)
SELECT  *
FROM    bossChild
OPTION (MAXRECURSION 0)

    내가 while 루프를 사용하도록 추천 할 것입니다 무엇을, 단지 임시 테이블에 링크를 삽입 이미 존재하지 않는 ID는 이렇게 무한 루프를 제거합니다.

  3. ==============================

    3.아니 당신의 사건에 대한 일반적인 솔루션,하지만 힘 작업 : 당신의 선택 쿼리에서이 수정 :

    아니 당신의 사건에 대한 일반적인 솔루션,하지만 힘 작업 : 당신의 선택 쿼리에서이 수정 :

    select a.[User_ID], a.[Manager_ID] from [User] a join UserTbl b on (a.[Manager_ID]=b.[User_ID])

    될 수 있습니다 :

    select a.[User_ID], a.[Manager_ID] from [User] a join UserTbl b on (a.[Manager_ID]=b.[User_ID]) 
   and a.[User_ID] <> @UserID
  4. ==============================

    4.당신은 재귀 할 필요가 없습니다. 그것은 WHILE 루프에서 수행 할 수 있습니다. 나는 그것이 빠를 보장 : 잘 나를 위해 나는이 두 가지 기술에 타이밍을했던 모든 시간이었다. 이것은 비효율적 들리지만 루프의 수는 재귀 수준이기 때문에 그것을하지 않습니다. 각 반복에서 당신은 루프를 확인하고 발생 위치를 수정할 수 있습니다. 당신이 뭔가를 선호하는 것 같다하지만 당신은 또한 루프가 발생하면 오류가 발생하는 임시 테이블에 제약 조건을 넣을 수 있습니다 더 우아하게 루프를 다루고있다. 또한 수준의 일정 수 이상 while 루프의 반복은 (발견되지 않은 루프를 잡을 수있는 오류를 트리거 할 수 있습니다 - 소년 아, 그것은 종종 발생합니다.

    당신은 재귀 할 필요가 없습니다. 그것은 WHILE 루프에서 수행 할 수 있습니다. 나는 그것이 빠를 보장 : 잘 나를 위해 나는이 두 가지 기술에 타이밍을했던 모든 시간이었다. 이것은 비효율적 들리지만 루프의 수는 재귀 수준이기 때문에 그것을하지 않습니다. 각 반복에서 당신은 루프를 확인하고 발생 위치를 수정할 수 있습니다. 당신이 뭔가를 선호하는 것 같다하지만 당신은 또한 루프가 발생하면 오류가 발생하는 임시 테이블에 제약 조건을 넣을 수 있습니다 더 우아하게 루프를 다루고있다. 또한 수준의 일정 수 이상 while 루프의 반복은 (발견되지 않은 루프를 잡을 수있는 오류를 트리거 할 수 있습니다 - 소년 아, 그것은 종종 발생합니다.

    트릭은 내부가 원래의 임시 테이블에서 가장 최근의 결과와 자식 항목 간의 조인 현재 반복 번호 열을 포함하고,하고, (루트 항목과 뇌관을하는) 임시 테이블에 반복적으로 삽입하는 것입니다 표. 그냥 루프 @@ 행 개수 = 0의 탈출! 어 간단한?

  5. ==============================

    5.난 당신이 얼마 전에이 질문을 알고 있지만, 여기에 무한 재귀 루프를 감지 작동 할 수있는 솔루션입니다. I는 경로를 생성하고 사용자 ID의 경로에 있는지 I는 CTE 상태 체크하고,이 경우는 다시 처리 못해. 도움이 되었기를 바랍니다.

    난 당신이 얼마 전에이 질문을 알고 있지만, 여기에 무한 재귀 루프를 감지 작동 할 수있는 솔루션입니다. I는 경로를 생성하고 사용자 ID의 경로에 있는지 I는 CTE 상태 체크하고,이 경우는 다시 처리 못해. 도움이 되었기를 바랍니다.

    호세

    DECLARE @Table TABLE(
    USER_ID INT,
    MANAGER_ID INT )
INSERT INTO @Table (USER_ID,MANAGER_ID) SELECT 1, 2
INSERT INTO @Table (USER_ID,MANAGER_ID) SELECT 2, 1
INSERT INTO @Table (USER_ID,MANAGER_ID) SELECT 3, 1
INSERT INTO @Table (USER_ID,MANAGER_ID) SELECT 4, 3
INSERT INTO @Table (USER_ID,MANAGER_ID) SELECT 5, 2

DECLARE @UserID INT
SELECT @UserID = 1

;with
UserTbl as -- Selects an employee and his subordinates.
(
    select 
        '/'+cast( a.USER_ID as varchar(max)) as [path],
        a.[User_ID], 
        a.[Manager_ID] 
    from @Table a 
    where [User_ID] = @UserID
    union all
    select
        b.[path] +'/'+ cast( a.USER_ID as varchar(max)) as [path],
        a.[User_ID], 
        a.[Manager_ID] 
    from @Table a 
    inner join UserTbl b 
        on (a.[Manager_ID]=b.[User_ID])
    where charindex('/'+cast( a.USER_ID as varchar(max))+'/',[path]) = 0
)
select * from UserTbl
  6. ==============================

    6.이 자료에서이 같은 루프를 기본적으로 경우 사용자가 직접 검색 논리를해야 할 것이다. 당신은 부하 직원 및 기타 얻을 수있는 상사를 얻기 위해 하나의 CTE를 사용할 수 있습니다.

    이 자료에서이 같은 루프를 기본적으로 경우 사용자가 직접 검색 논리를해야 할 것이다. 당신은 부하 직원 및 기타 얻을 수있는 상사를 얻기 위해 하나의 CTE를 사용할 수 있습니다.

    그들은 말도 서로의 상사되지 않을 것 때문에 다른 생각은 모두 회사의 소유자 보스로 더미 행을하는 것입니다. 이것은 나의 선호하는 옵션입니다.

  7. ==============================

    7.나는 두 가지 방법을 생각할 수있다.

    나는 두 가지 방법을 생각할 수있다.

    1) 당신이 원하는 것보다 더 많은 열을 생산하지만, 확실히 그것을하지 않습니다 같이 Recurse 너무 깊이 확인하기 위해 검사를 포함한다. 그런 다음 중복 사용자 레코드를 제거합니다.

    2) 이미 방문한 사용자를 보유하는 문자열을 사용합니다. 작동하지 않았다없는 하위 쿼리의 생각처럼.

    접근 1 :

    ; with TooMuchHierarchy as (
    select "User_ID"
        , Manager_ID 
        , 0 as Depth
    from "User" 
    WHERE "User_ID" = @UserID
    union all
    select U."User_ID"
        , U.Manager_ID
        , M.Depth + 1 as Depth
    from TooMuchHierarchy M
    inner join "User" U 
        on U.Manager_ID = M."user_id"
    where Depth < 100) -- Warning MAGIC NUMBER!!
, AddMaxDepth as (
    select "User_ID"
        , Manager_id
        , Depth
        , max(depth) over (partition by "User_ID") as MaxDepth
    from TooMuchHierarchy)
select "user_id", Manager_Id 
from AddMaxDepth
where Depth = MaxDepth

    깊이 <100가 최대 재귀 오류가 발생에서 당신을 계속 것입니다 라인입니다. 이 숫자는 작게, 덜 기록은 필요 폐기 할 것을 생산 될 것이다. 그래서 조직도의 깊이가 저장되는대로 적어도 대형으로되어 있는지 확인, 너무 작게 직원은 반환되지 않습니다. 회사로 maintence 악몽의 비트는 성장한다. 그것은 더 할 필요가 있다면, 더 재귀를 허용하는 모든 일에 옵션 (MAXRECURSION ... 수 ...)를 추가합니다.

    접근법 2 :

    ; with Hierarchy as (
    select "User_ID"
        , Manager_ID 
        , '#' + cast("user_id" as varchar(max)) + '#' as user_id_list
    from "User" 
    WHERE "User_ID" = @UserID
    union all
    select U."User_ID"
        , U.Manager_ID
        , M.user_id_list + '#' + cast(U."user_id" as varchar(max)) + '#' as user_id_list
    from Hierarchy M
    inner join "User" U 
        on U.Manager_ID = M."user_id"
    where user_id_list not like '%#' + cast(U."User_id" as varchar(max)) + '#%')
select "user_id", Manager_Id 
from Hierarchy
  8. ==============================

    8.트리거 또는 점검 제한 조건에 싸여 UDF로 수행 할 수 있습니다 -보다 더 낫다 솔루션은 데이터를 정리하고 미래에 어떤 루프가없는 있는지 확인하는 것입니다.

    트리거 또는 점검 제한 조건에 싸여 UDF로 수행 할 수 있습니다 -보다 더 낫다 솔루션은 데이터를 정리하고 미래에 어떤 루프가없는 있는지 확인하는 것입니다.

    여기 입증 그러나, 당신은 멀티 문 UDF를 사용할 수 있습니다 무한 루프를 방지. 부품 하나

    (가)주기를 필터링 가입에 당신은 NOT IN () 절을 추가 할 수 있습니다.

  9. ==============================

    9.이것은까지 추격하는 프로젝트와 계층 관계 나무 아래로 사용되는 코드의 I입니다.

    이것은까지 추격하는 프로젝트와 계층 관계 나무 아래로 사용되는 코드의 I입니다.

    캡처 부하 직원에 대한 사용자 정의 함수 :

    CREATE FUNCTION fn_UserSubordinates(@User_ID INT)
RETURNS @SubordinateUsers TABLE (User_ID INT, Distance INT) AS BEGIN
    IF @User_ID IS NULL
        RETURN

    INSERT INTO @SubordinateUsers (User_ID, Distance) VALUES ( @User_ID, 0)

    DECLARE @Distance INT, @Finished BIT
    SELECT @Distance = 1, @Finished = 0

    WHILE @Finished = 0
    BEGIN
        INSERT INTO @SubordinateUsers
            SELECT S.User_ID, @Distance
                FROM Users AS S
                JOIN @SubordinateUsers AS C
                    ON C.User_ID = S.Manager_ID
                LEFT JOIN @SubordinateUsers AS C2
                    ON C2.User_ID = S.User_ID
                WHERE C2.User_ID IS NULL
        IF @@RowCount = 0
            SET @Finished = 1

        SET @Distance = @Distance + 1
    END

    RETURN
END

    캡처 관리자에 대한 사용자 정의 함수 :

    CREATE FUNCTION fn_UserManagers(@User_ID INT)
RETURNS @User TABLE (User_ID INT, Distance INT) AS BEGIN
    IF @User_ID IS NULL
        RETURN

    DECLARE @Manager_ID INT

    SELECT @Manager_ID = Manager_ID
    FROM UserClasses WITH (NOLOCK)
    WHERE User_ID = @User_ID

    INSERT INTO @UserClasses (User_ID, Distance)
        SELECT User_ID, Distance + 1
        FROM dbo.fn_UserManagers(@Manager_ID)

    INSERT INTO @User (User_ID, Distance) VALUES (@User_ID, 0)

    RETURN
END
  10. ==============================

    10.당신은 사용자 ID가 세트에 이미 추가에서 재귀 쿼리를 방지하기 위해 어떤 방법이 필요합니다. 재귀 테이블의 언급 하위 쿼리로 더블 그러나, 허용되지 않습니다 당신은 이미 목록에 사용자를 제거하기 위해 다른 솔루션이 필요합니다 (당신에게 반 감사합니다).

    당신은 사용자 ID가 세트에 이미 추가에서 재귀 쿼리를 방지하기 위해 어떤 방법이 필요합니다. 재귀 테이블의 언급 하위 쿼리로 더블 그러나, 허용되지 않습니다 당신은 이미 목록에 사용자를 제거하기 위해 다른 솔루션이 필요합니다 (당신에게 반 감사합니다).

    이 솔루션은 이러한 행을 제거를 제외하고 사용하는 것입니다. 이 매뉴얼에 따라 작동합니다. 노동 조합 형태의 사업자와 연결 여러 재귀 문이 허용됩니다. 반복의 특정 숫자 후 재귀 결과 집합 반환 비어있는 목록 수단과 재귀 정지 이미 사용자를 제거.

    with UserTbl as -- Selects an employee and his subordinates.
(
    select a.[User_ID], a.[Manager_ID] from [User] a WHERE [User_ID] = @UserID
    union all
    (
      select a.[User_ID], a.[Manager_ID] 
        from [User] a join UserTbl b on (a.[Manager_ID]=b.[User_ID])
        where a.[User_ID] not in (select [User_ID] from UserTbl)
      EXCEPT
        select a.[User_ID], a.[Manager_ID] from UserTbl a 
     )
)
select * from UserTbl;

    다른 옵션은 고정 된 횟수의 반복 한 후 쿼리를 중지하거나 MAXRECURSION 쿼리 옵션 힌트를 사용하는 수준의 변수를 하드 코딩하는 것입니다,하지만 난 그게 당신이 원하는 것을하지 않습니다 같아요.

    11. from https://stackoverflow.com/questions/1192945/sql-server-2005-recursive-query-with-loops-in-data-is-it-possible by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] "DISTINCT SELECT는"서로 다른 경우를 무시  (0) 2020.07.06
[SQL] 상호 친구가 가입하여 SQL (MySQL의)  (0) 2020.07.06
[SQL] 오라클에서 SELECT 문의 결과 목록을 쉼표로 구분 [중복]  (0) 2020.07.06
[SQL] 엑셀 2013 이상 65536 행 오류 쿼리  (0) 2020.07.06
[SQL] PHP와 SQL 경로 저장 파일을 업로드  (0) 2020.07.06
,

티스토리툴바