Рекурсивный запрос SQL Server для получения верхнего родителя

Мне нужна ваша помощь в создании рекурсивных запросов с использованием CTE в SQL Server.

У меня номер заказа в качестве входного параметра. Мне нужно отобразить данные о его верхней родительской отправке. даже если я ищу его дочерние элементы .. т.е. если я ищу заказ № 3, я должен получить его верхний родительский заказ №. то есть 20.

Вот моя структура таблицы ..

CREATE TABLE #TblSerialNo
    (
      [SRno] [char](20) NOT NULL ,
      [CustSrNo] [varchar](75) NULL
    )
CREATE TABLE #TblSerialReleation
    (
      [SRno] [char](20) NOT NULL ,
      [ChildSRno] [char](20) NOT NULL
    )
CREATE TABLE #TblDespatch
    (
      [SRno] [char](20) NOT NULL ,
      OrderNo INT NOT NULL
    )

INSERT  INTO #TblSerialNo   VALUES  ( 'TS1', 'DD123CV1' )
INSERT  INTO #TblSerialNo   VALUES  ( 'TS2', 'DD123CV2' )
INSERT  INTO #TblSerialNo  VALUES   ( 'TS3', 'DD123CV3' )

INSERT  INTO #TblSerialNo VALUES  ( 'BS1', 'DD12sfs3CV1' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS2', 'DD1et23CV2' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS1', 'DD12e3CV1' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS2', 'DD12fe3CV2' )

INSERT  INTO #TblSerialNo VALUES  ( 'BS1aa', 'DD12d3CV1' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS1ab', 'DDd123CV2' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS1ac', 'DD1r23CV3' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS2aa', 'DDs123CV4' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS2ab', 'DD12d3CV1' )
INSERT  INTO #TblSerialNo VALUES  ( 'BS2ac', 'DD1s23CV2' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS1aa', 'DD1s23CV3' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS1ab', 'DD12s3CV4' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS1ac', 'DD123dCV1' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS2aa', 'DDa123CV2' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS2ab', 'DDa123CV3' )
INSERT  INTO #TblSerialNo VALUES  ( 'CS2ac', 'DDa123CV4' )

--================  Relation table   ==============

INSERT  INTO #TblSerialReleation  VALUES  ( 'TS1', 'BS1' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'TS1', 'BS2' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'TS2', 'CS1' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'TS2', 'CS2' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS1', 'BS1aa' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS1', 'BS1ab' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS1', 'BS1ac' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS2', 'BS2aa' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS2', 'BS2ab' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'BS2', 'BS2ac' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS1', 'CS1aa' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS1', 'CS1ab' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS1', 'CS1ac' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS2', 'CS2aa' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS2', 'CS2ab' )
INSERT  INTO #TblSerialReleation  VALUES  ( 'CS2', 'CS2ac' )
--===========  Despatch 
INSERT  INTO #TblDespatch  VALUES  ( 'CS2ac', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS2ab', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS2ac', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS1aa', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS1ac', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS2ac', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'CS2ac', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'TS1', 1 )
INSERT  INTO #TblDespatch  VALUES  ( 'TS3', 2 )
INSERT  INTO #TblDespatch  VALUES  ( 'TS2', 3 )
INSERT  INTO #TblDespatch  VALUES  ( 'BS2ab', 20 )

DROP TABLE #TblDespatch
DROP TABLE #TblSerialNo
DROP TABLE #TblSerialReleation

Заранее спасибо.


sql sql-server common-table-expression recursive-query
person Leo    schedule 15.02.2013    source источник
comment
Я чувствую, что нам здесь чего-то не хватает - как они связываются (если бы вы сделали это вручную)? Я не вижу взаимосвязи в приведенных выше данных.   -  person sgeddes    schedule 15.02.2013
comment
@sgeddes, похоже, находится в таблице TblSerialReleation   -  person Cristian Lupascu    schedule 15.02.2013
comment
@ w0lf - я вижу, что это должно быть там - но я не могу понять, как TS2 совпадает с BS2ab (20) в таблице отправки - я этого не вижу :)   -  person sgeddes    schedule 15.02.2013
comment
@sgeddes правильно, там есть недостающие звенья :)   -  person Cristian Lupascu    schedule 15.02.2013
comment
Может ли запись serialNo иметь более одного родителя?   -  person Yuriy Rozhovetskiy    schedule 15.02.2013
comment
Есть связь между всей таблицей и TblSerialNo. СРно это д ключ ..   -  person Leo    schedule 16.02.2013
comment
Предположим, что между всей таблицей существует правильное отношение внешнего ключа. TblSerialReleation содержат отношения между родителем и потомком. Ребенок может быть родителем. Есть также родители без детей. мы можем отправить любого дочернего или родительского элемента из любой иерархии.   -  person Leo    schedule 16.02.2013
comment
Может ли запись serialNo иметь более одного родителя? НЕТ..   -  person Leo    schedule 18.02.2013

Ответы (1)


arrow_upward
2
arrow_downward

Глядя на ваши данные, кажется, что отношение идет наоборот (BS2ab (Порядок 20) является потомком от TS1 (Порядок 3) до BS2.

В этом случае, начиная с дочернего элемента (BS2ab), вы можете найти его верхний родительский элемент с помощью следующего оператора:

;with ParentOrders as(
  select 
    convert(char(20), 'BS2ab')as SRno,
    0 as Level 

  union  all

  select r.SRno, o.Level + 1
  from ParentOrders o
  join TblSerialReleation r
    on o.SRNo = r.ChildSRno 
)
select top 1 SRNO 
  from ParentOrders
  order by Level desc

Вот рабочий образец запроса: http://www.sqlfiddle.com/#!3/e253e/6

person Cristian Lupascu    schedule 15.02.2013