来自同一张表的递归查询 [英] Recursive query from the same table

问题描述

我在一张表格 ProdHistory 中有不同的产品序列号，正如表格名称所暗示的那样，其中包含生产历史.
例如，我有产品序列号 SER001，它使用具有自己序列号的零件.
我们还生产这些部件，因此使用相同的表 ProdHistory 来跟踪其子部件.
子部分以及是否有子子部分也是如此.

I have different product serial numbers in one table ProdHistory which contains, as the table name suggest, production history.
For example I have product serial SER001 which uses parts with its own serial number.
We also produce these parts thus uses the same table ProdHistory to track its subparts.
The same goes with the subparts and if it has sub-sub parts.

示例表

IF OBJECT_ID('tempDB.dbo.#SAMPLETable') IS NOT NULL DROP TABLE #SAMPLETable
CREATE TABLE #SAMPLETable
(
    ITEMSEQ INT IDENTITY(1,1),
    SERIAL NVARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS,
    ITEMID NVARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS,
    PARTSERIAL NVARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS,
    PARTID NVARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS,
    CREATEDDATETIME DATETIME
)

INSERT INTO 
       #SAMPLETable (SERIAL,ITEMID,PARTSERIAL,PARTID,CREATEDDATETIME) 
VALUES ('SER0001','ASY-1342','ITM0001','PRT-0808','2017-01-17'),
       ('SER0001','ASY-1342','ITM0002','PRT-0809','2017-01-17'),
       ('SER0001','ASY-1342','ITM0003','PRT-0810','2017-01-17'),
       ('SER0001','ASY-1342','ITM0004','PRT-0811','2017-01-17'),
       ('ITM0001','PRT-0808','UNT0001','PRT-2020','2017-01-16'),
       ('ITM0002','PRT-0809','UNT0002','PRT-2021','2017-01-16'),
       ('ITM0002','PRT-0809','UNT0003','PRT-2022','2017-01-16'),
       ('ITM0003','PRT-0810','UNT0004','PRT-2023','2017-01-16'),
       ('UNT0002','PRT-2021','DTA0000','PRT-1919','2017-01-15'),
       ('UNT0003','PRT-2022','DTA0001','PRT-1818','2017-01-15'),
       ('DTA0001','PRT-1818','LST0001','PRT-1717','2017-01-14')

问题是，如果我只得到主序列号，我如何返回与其关联的所有零件和子零件序列号?

The question is, if I'm given just the main serial number, how can I return all the parts and subparts serial associated with it?

示例结果:

MainSerial SubSerial1 SubSerial2 SubSerial3 SubSerial4
-------------------------------------------------------
SER0001    ITM0001    UNT0001
SER0001    ITM0002    UNT0002    DTA0000
SER0001    ITM0002    UNT0003    DTA0001    LST0001
SER0001    ITM0003    UNT0004
SER0001    ITM0004   

在上面，一个序列号有多少个零件和子零件是不确定的.
我没有发布我的代码，因为我现在正在做的是一一查询.
如果我知道子部分的数量，我可以嵌套Joins，但事实并非如此.

In above, it is not definite how many parts and subparts there are for a serial number.
I did not post my code since what I'm doing right now is to query it one by one.
If I have known number of subparts, I can do nested Joins, however it is not.

另一个问题是，如果我只给出了上面的任何一个子部分，是否有可能返回相同的结果?

Another question is, if I'm just given any of the subparts above, is it possible to return the same result?

推荐答案

我认为一种方法是像这样使用动态 SQL:

I think a way is to use Dynamic SQL like this:

-- Variables to generate SQL query string dynamically
declare @cols nvarchar(max) = '', @joins nvarchar(max) = '', @sql nvarchar(max) = '';    

-- Using CTE to iterate parent-child records
with cte(i, cols, joins, itemId, serial, partId, partSerial) as (
    select 
        1, -- Level or depth of hierarchically tree 
        N's1.serial MainSerial, s1.partSerial SubSerial'+cast(1 as varchar(max)), 
        N'yourTable s'+cast(1 as varchar(max)), 
        s.itemId, s.serial, s.partId, s.partSerial
    from yourTable s
    -- A way to filter root-parents is filtering items those are not in parts
    where s.itemId not in (select si.partId from yourTable si)
    union all
    select 
        i+1, 
        cols + N', s'+cast(i+1 as varchar(max))+N'.partSerial SubSerial'+cast(i+1 as varchar(max)), 
        joins + N' left join yourTable s'+cast(i+1 as varchar(max))+N' on s'+cast(i as varchar(max))+N'.partId = s'+cast(i+1 as varchar(max))+N'.itemId', 
        st.itemId, st.serial, st.partId, st.partSerial
    from cte 
    join #sampleTable st on cte.partId = st.itemId
)
-- Now we need only strings of deepest level
select top(1) 
    @cols = cols, @joins = joins
from cte
order by i desc;

-- Finalize and executing query string
set @sql = N'select ' + @cols + N' from ' + @joins + N' where s1.itemId not in (select s.partId from yourTable s)';
exec(@sql);

附加说明:生成的查询是:

Additional Note: Generated query is:

select s1.serial MainSerial
    , s1.partSerial SubSerial1
    , s2.partSerial SubSerial2
    , s3.partSerial SubSerial3
    , s4.partSerial SubSerial4 
  --, ...
from yourTable s1 
  left join yourTable s2 on s1.partId = s2.itemId 
  left join yourTable s3 on s2.partId = s3.itemId 
  left join yourTable s4 on s3.partId = s4.itemId 
--left join ...
where s1.itemId not in (select s.partId from yourTable s);

这篇关于来自同一张表的递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！