我有一个查询，它贯穿于产品关系中的各个级别，并从上到下生成生产产品所需的所有产品的列表。

with 
extension_table as (
    select
      dc_x_str_03 as warehouse
    , dc_x_str_04 as product
    , dc_x_str_10 as stk_class
    from extension_table with (nolock)
),
stock as (
    select
       a.warehouse
    , a.product
    , long_description
    , stk_class
    from stock a with (nolock)
    inner join extension_table b
    on a.warehouse = b.warehouse
    and a.product = b.product

    union all

    select 
      '' as warehouse
    , code as product
    , long_description
    , '' as stk_class
    from services_table with (nolock)
),
relationship_table as (
    select 
      assembly_warehouse
    , product_code
    , usage_quantity
    , component_whouse
    , component_code
    , b.stk_class
    , kind
    from relationship_table a with (nolock)
    left join extension_table b 
    on a.component_whouse=b.warehouse
    and a.component_code=b.product
),
extension_table_relationship as (
    select
      a.warehouse            as assembly_warehouse
    , a.product                as product_code
    , 1                        as usage_quantity
    , b.warehouse            as component_whouse
    , b.product                as component_code
    , b.stk_class            as stk_class
    , 'P'                    as kind
    from extension_table a
    inner join extension_table b
    on a.product = b.product
    where ( b.stk_class like 'MT%' or b.stk_class like 'PT%') 
    and a.stk_class like 'TT%'
),
relationship_union as (
    select * from extension_table_bom
    union all
    select * from relationship_table
),
relationship_run as (
SELECT
  warehouse as assembly_warehouse
, product as assembly_code
, cast(1 as float) as usage_quantity
, warehouse as component_warehouse
, product as component_code
, stk_class
, cast('P' as char(1)) as kind
, cast(warehouse as char(2)) as assy_wh1
, cast(product as char(20)) as assy_pr1
, cast(warehouse as char(2)) as assy_wh
, cast(product as char(20)) as assy_pr
, 1    as level
, CAST(    '->' + RTRIM(warehouse) + RTRIM(product) AS NVARCHAR(MAX)) AS matpath
FROM stock

union all

SELECT
  relationship_run.assembly_warehouse
, relationship_run.assembly_code
, cast(relationship_run.usage_quantity * relationship_union.usage_quantity as float) as usage_quantity
, stock.warehouse as component_warehouse
, stock.product as component_code
, stock.stk_class
, cast(relationship_union.kind as char(1))as [kind]
    , cast(relationship_union.assembly_warehouse as char(2)) as assy_wh1
    , cast(relationship_union.product_code as char(20)) as assy_pr1
    , case    when cast(    (select assembly_warehouse 
                        from relationship_table 
                        where component_whouse=stock.warehouse 
                        and component_code=stock.product
                        and assembly_warehouse=relationship_union.assembly_warehouse 
                        and product_code=relationship_union.product_code) as char(2)) IS NULL then 
                                                                                cast(    (select assembly_warehouse 
                                                                                        from relationship_table 
                                                                                        where component_whouse=relationship_union.assembly_warehouse 
                                                                                        and component_code=relationship_union.product_code
                                                                                        and assembly_warehouse=relationship_run.assy_wh1
                                                                                        and product_code=relationship_run.assy_pr1) as char(2))
            else cast(    (select assembly_warehouse 
                        from relationship_table 
                        where component_whouse=stock.warehouse 
                        and component_code=stock.product
                        and assembly_warehouse=relationship_union.assembly_warehouse 
                        and product_code=relationship_union.product_code) as char(2))
      end as assy_wh
    , case    when cast(    (select product_code 
                        from relationship_table 
                        where component_whouse=stock.warehouse 
                        and component_code=stock.product
                        and assembly_warehouse=relationship_union.assembly_warehouse 
                        and product_code=relationship_union.product_code) as char(20)) IS NULL then 
                                                                                    cast(    (select product_code 
                                                                                            from relationship_table 
                                                                                            where component_whouse=relationship_union.assembly_warehouse 
                                                                                            and component_code=relationship_union.product_code
                                                                                            and assembly_warehouse=relationship_run.assy_wh1
                                                                                            and product_code=relationship_run.assy_pr1) as char(20))
            else cast(    (select product_code 
                        from relationship_table 
                        where component_whouse=stock.warehouse 
                        and component_code=stock.product
                        and assembly_warehouse=relationship_union.assembly_warehouse 
                        and product_code=relationship_union.product_code) as char(20))
      end as assy_pr

, case    when relationship_run.stk_class LIKE 'TT%' then relationship_run.level else relationship_run.level + 1 end as [level]
, CAST(relationship_run.matpath + '->' + RTRIM(stock.warehouse) + RTRIM(stock.product) AS NVARCHAR(MAX)) AS [matpath]
FROM stock
inner join relationship_union 
on stock.warehouse=relationship_union.component_whouse
and stock.product=relationship_union.component_code
inner join relationship_run
on relationship_union.assembly_warehouse=relationship_run.component_warehouse
and relationship_union.product_code=relationship_run.component_code
)
SELECT 
assembly_warehouse, assembly_code, usage_quantity, component_warehouse, component_code, stk_class, kind
, assy_wh as parent_assy_wh, assy_pr as parent_assy_prod, level, matpath
FROM relationship_run
WHERE 
stk_class NOT LIKE 'TT%' 

option (maxrecursion 200)

上面的查询很有效，我很满意（我相信有些人会发现问题：）。问题是有一些错误的数据导致它进入一个无限循环，因此在最后的maxrecursion 200行。
造成这种情况的原因是，在与联合cte的关系中，我们会得到如下项目：

AL A000000003   AE A000000003                  
AE A000000003   AL A000000003

由于第一个项将第二个项作为子项，而第二个项将第一个项作为子项，因此上述操作会导致循环。在这种关系中，我需要运行一个查询，它将查找所有具有这种循环的项，但我不太确定如何查找。循环也有可能发生在更深一层，例如，项目a由项目b、c、d组成，项目d由项目a组成-这将导致循环，因此我需要找出这个问题并删除/调整数据，以便项目d不再将其高父项作为子项引用。