我有一个查询,它贯穿于产品关系中的各个级别,并从上到下生成生产产品所需的所有产品的列表。
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不再将其高父项作为子项引用。
暂无答案!
目前还没有任何答案,快来回答吧!