SQL - Как найти значение в структуре данных на уровне дерева

У меня есть две таблицы SQL Server:

Счет (invoice)
Счета-фактуры (invoice_relation)

Таблица

invoice хранит все записи счетов-фактур с помощью фолио транзакций.

invoice_relation таблица хранит любое отношение между счетами.

Это пример того, как счета-фактуры могут быть связаны между собой:

Итак, цель состоит в том, чтобы найти "folio" в таблице invoice с учетом invoicenumber и a folio, но folio иногда не будет folio, что invoice имеет, поэтому мне нужно выполнить поиск по всем отношениям дерева, чтобы узнать, соответствует ли какой-либо счет-фактура номерм счета-фактуры, а также folio является частью отношения.

Например, мне нужно найти номер фокуса и номер счета-фактуры:

Folio: 1003
Номер счета: A1122

В моем запросе мне нужно сначала найти фолио, потому что это мой первичный ключ таблицы invoice. Затем попытайтесь сопоставить A1122 с D1122, который не будет соответствовать, поэтому я должен искать всю древовидную структуру, чтобы найти, есть ли A1122. Результатом будет то, что счет-фактура A1122 был найден в фолио 1000.

Есть ли подсказка о том, как это сделать?

Вот script того, как создавать приведенные выше примеры таблиц с данными:

CREATE TABLE [dbo].[invoice](
    [folio] [int] NOT NULL,
    [invoicenumber] [nvarchar](20) NOT NULL,
    [isactive] [bit] NOT NULL,
 CONSTRAINT [PK_invoice] PRIMARY KEY CLUSTERED 
(
    [folio] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[invoice_relation](
    [relationid] [int] NOT NULL,
    [invoice] [nvarchar](20) NOT NULL,
    [parentinvoice] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_invoice_relation_1] PRIMARY KEY CLUSTERED 
(
    [relationid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1000, N'A1122', 1)
GO
INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1001, N'B1122', 1)
GO
INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1002, N'C1122', 1)
GO
INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1003, N'D1122', 1)
GO
INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1004, N'F1122', 1)
GO
INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1005, N'G1122', 1)
GO
INSERT [dbo].[invoice_relation] ([relationid], [invoice], [parentinvoice]) VALUES (1, N'A1122', N'B1122')
GO
INSERT [dbo].[invoice_relation] ([relationid], [invoice], [parentinvoice]) VALUES (2, N'C1122', N'A1122')
GO
INSERT [dbo].[invoice_relation] ([relationid], [invoice], [parentinvoice]) VALUES (3, N'D1122', N'A1122')
GO
INSERT [dbo].[invoice_relation] ([relationid], [invoice], [parentinvoice]) VALUES (4, N'F1122', N'B1122')
GO
INSERT [dbo].[invoice_relation] ([relationid], [invoice], [parentinvoice]) VALUES (5, N'G1122', N'F1122')
GO

Ответы

Ответ 1

Ваша модель сломана для начала. parentinvoice должен быть в таблице счетов. Это рекурсивная модель базы данных.... поэтому сделайте схему таблицы рекурсивной. Имейте NULL-ключ в столбце, который ссылается на собственную таблицу. Любое время, когда поле (поле родительского счета) равно null, указывает, что это первичный счет-фактура. Любая строка с родителем является частью счета-фактуры.

Когда вы хотите найти значение в структуре уровня дерева, вы переносите свой начальный SQL-запрос в оператор SELECT (.....) '(создаете свою собственную настраиваемую таблицу), которая отфильтровывает то, что вы хотите. Дайте мне знать, если у вас есть вопросы!

Ответ 2

Я все еще не уверен, что вы действительно хотите, я написал что-то похожее на JamieD77, которое должно найти главного родителя, а затем вернуться вниз по дереву, но потом вы получите детей и ранчо, которые напрямую не связаны с A1122.....

Здесь вы можете пройти вверх и вниз по дереву и вернуть всех детей и родителей, непосредственно связанных с номером invoicenumber

DECLARE @InvoiceNumber NVARCHAR(20) = 'A1122'
DECLARE @Folio INT = 1003

;WITH cteFindParents AS (
    SELECT
       i.folio
       ,i.invoicenumber
       ,CAST(NULL AS NVARCHAR(20)) as ChildInvoiceNumber
       ,CAST(NULL AS NVARCHAR(20)) as ParentInvoiceNumber
       ,0 as Level
    FROM
       dbo.invoice i
    WHERE
       i.invoicenumber = @InvoiceNumber

    UNION ALL

    SELECT
       i.folio
       ,i.invoicenumber
       ,c.invoicenumber as ChildInvoiceNumber
       ,i.invoicenumber as ParentInvoiceNumber
       ,c.Level - 1 as Level
    FROM
       cteFindParents c
       INNER JOIN dbo.invoice_relation r
       ON c.invoicenumber = r.invoice
       INNER JOIN dbo.invoice i
       ON r.parentinvoice = i.invoicenumber
)

, cteFindChildren as (
    SELECT *
    FROM
       cteFindParents

    UNION ALL

    SELECT
       i.folio
       ,i.invoicenumber
       ,i.invoicenumber AS ChildInvoiceNumber
       ,c.invoicenumber AS ParentInvoiceNumber
       ,Level + 1 as Level
    FROM
       cteFindChildren c
       INNER JOIN dbo.invoice_relation r
       ON c.invoicenumber = r.parentinvoice
       INNER JOIN dbo.invoice i
       ON r.invoice = i.invoicenumber
    WHERE
       c.Level = 0
)

SELECT *
FROM
    cteFindChildren

Но В зависимости от того, что именно вы ищете, вы действительно можете получить пару двоюродных братьев, которые не нужны.....

-------------- Здесь был метод поиска верхнего родителя и получения всего дерева

DECLARE @InvoiceNumber NVARCHAR(20) = 'A1122'
DECLARE @Folio INT = 1003

;WITH cteFindParents AS (
    SELECT
       i.folio
       ,i.invoicenumber
       ,CAST(NULL AS NVARCHAR(20)) as ChildInvoiceNumber
       ,0 as Level
    FROM
       dbo.invoice i
    WHERE
       i.invoicenumber = @InvoiceNumber

    UNION ALL

    SELECT
       i.folio
       ,i.invoicenumber
       ,c.invoicenumber as ChildInvoiceNumber
       ,c.Level + 1 as Level
    FROM
       cteFindParents c
       INNER JOIN dbo.invoice_relation r
       ON c.invoicenumber = r.invoice
       INNER JOIN dbo.invoice i
       ON r.parentinvoice = i.invoicenumber

)

, cteGetTopParent AS  (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY LEVEL DESC) as RowNum
    FROM
       cteFindParents
)

, cteGetWholeTree AS (
    SELECT
       p.folio
       ,p.invoicenumber
       ,p.invoicenumber as TopParent
       ,p.invoicenumber as Parent
       ,CAST(p.invoicenumber AS NVARCHAR(1000)) as Hierarchy
       ,0 as Level
    FROM
       cteGetTopParent p
    WHERE
       RowNum = 1

    UNION ALL

    SELECT
       i.folio
       ,i.invoicenumber
       ,c.TopParent
       ,c.invoicenumber AS Parent
       ,CAST(c.TopParent + '|' + (CASE WHEN Level > 0 THEN c.invoicenumber + '|' ELSE '' END) + i.invoicenumber  AS NVARCHAR(1000)) as Hierarchy
       ,Level + 1 as Level
    FROM
       cteGetWholeTree c
       INNER JOIN dbo.invoice_relation r
       ON c.invoicenumber = r.parentinvoice
       INNER JOIN dbo.invoice i
       ON r.invoice = i.invoicenumber
)

SELECT *
FROM
    cteGetWholeTree

Ответ 3

Я был немного неясен в отношении ваших реальных требований, поэтому я решил, что здесь может быть уместна функция, которая может быть использована в таблице. Я добавил несколько дополнительных элементов, и если они нежелательны, их достаточно легко удалить (т.е. TITLE, Nesting, TopInvoice, TopFolio). Кроме того, вы можете заметить клавиши диапазона (R1/R2). Они выполняют множество функций: последовательность презентаций, критерии выбора, показатели родителя/листа и, возможно, самое важное нерекурсивное агрегирование.

Чтобы вернуть всю иерархию

Select * from [dbo].[udf_SomeFunction](NULL,NULL)

Чтобы вернуть счет и ВСЕ его потомков

Select * from [dbo].[udf_SomeFunction]('A1122',NULL)

Возврат PATH файла

Select * from [dbo].[udf_SomeFunction](NULL,'1003')

Чтобы вернуть Folio Limited в счет-фактуру

Select * from [dbo].[udf_SomeFunction]('A1122','1003')

Следующий код требует SQL 2012 +

CREATE FUNCTION [dbo].[udf_SomeFunction](@Invoice nvarchar(25),@Folio nvarchar(25))
Returns Table
As  
Return (
with cteBld as (
      Select Seq  = cast(1000+Row_Number() over (Order By Invoice) as nvarchar(500)),I.Invoice,I.ParentInvoice,Lvl=1,Title = I.Invoice,F.Folio
        From (
              Select Distinct 
                     Invoice=ParentInvoice
                    ,ParentInvoice=cast(NULL as nvarchar(20)) 
              From   [Invoice_Relation] 
              Where  @Invoice is NULL and ParentInvoice Not In (Select Invoice from [Invoice_Relation])
              Union  All
              Select Invoice
                    ,ParentInvoice 
              From   [Invoice_Relation] 
              Where  [email protected]
             ) I
        Join Invoice F on I.Invoice=F.InvoiceNumber
      Union  All
      Select Seq  = cast(concat(A.Seq,'.',1000+Row_Number() over (Order by I.Invoice)) as nvarchar(500))
            ,I.Invoice
            ,I.ParentInvoice
            ,A.Lvl+1
            ,I.Invoice,F.folio
      From   [Invoice_Relation] I
      Join   cteBld  A on I.ParentInvoice = A.Invoice 
      Join   Invoice F on I.Invoice=F.InvoiceNumber )
     ,cteR1  as (Select Seq,Invoice,Folio,R1=Row_Number() over (Order By Seq) From cteBld)
     ,cteR2  as (Select A.Seq,A.Invoice,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.Invoice )

Select Top 100 Percent 
       B.R1
      ,C.R2
      ,A.Invoice
      ,A.ParentInvoice
      ,A.Lvl
      ,Title = Replicate('|-----',A.Lvl-1)+A.Title    -- Optional: Added for Readability
      ,A.Folio
      ,TopInvoice  = First_Value(A.Invoice) over (Order By R1) 
      ,TopFolio    = First_Value(A.Folio)   over (Order By R1) 
 From  cteBld A 
 Join  cteR1  B on A.Invoice=B.Invoice 
 Join  cteR2  C on A.Invoice=C.Invoice 
 Where (@Folio is NULL)
    or (@Folio is Not NULL and (Select R1 from cteR1 Where [email protected]) between R1 and R2)
 Order By R1
)

Заключительные мысли:

Это, безусловно, может быть больше того, что вы искали, и есть хорошие шансы, что я ПОЛНОСТЬЮ неправильно понял ваши требования. Тем не менее, будучи TVF, вы можете расширять его с помощью дополнительных предложений WHERE и/или ORDER или даже включить в CROSS APPLY.

Ответ 4

В этом случае используется подход hierarchyid, сначала создающий иерархию для каждой строки, затем выбор строки, где folio равен 1003, а затем поиск всех предков, у которых есть invoicenumber 'A1122'. Это не очень эффективно, но может дать вам несколько разных идей:

;WITH
Allfolios
AS
(
    Select i.folio, i.InvoiceNumber,
          hierarchyid::Parse('/' + 
               CAST(ROW_NUMBER() 
                        OVER (ORDER BY InvoiceNumber) AS VARCHAR(30)
                   ) + '/') AS hierarchy, 1 as level
    from invoice i
    WHERE NOT EXISTS 
        (SELECT * FROM invoice_relation ir WHERE ir.invoice = i. invoicenumber)
    UNION ALL
    SELECT i.folio, i.invoiceNumber,
          hierarchyid::Parse(CAST(a.hierarchy as VARCHAR(30)) + 
                             CAST(ROW_NUMBER() 
                                   OVER (ORDER BY a.InvoiceNumber) 
                               AS VARCHAR(30)) + '/') AS hierarchy, 
          level + 1
    FROM Allfolios A
    INNER JOIN invoice_relation ir
        on a.InvoiceNumber = ir.ParentInvoice
    INNER JOIN invoice i
        on ir.Invoice = i.invoicenumber
),
Ancestors
AS
(
    SELECT folio, invoiceNumber, hierarchy, hierarchy.GetAncestor(1) as AncestorId
    from Allfolios
    WHERE folio = 1003
    UNION ALL
    SELECT af.folio, af.invoiceNumber, af.hierarchy, af.hierarchy.GetAncestor(1)
      FROM Allfolios AF
      INNER JOIN 
            Ancestors a ON Af.hierarchy= a.AncestorId
)
SELECT *
FROM Ancestors
WHERE InvoiceNumber = 'A1122'

Отредактировано для случая, выделенного @jj32, где вы хотите найти корневой элемент в иерархии, в котором находится файл 1003, затем найдите любого потомка этого корня, который имеет номер счета-фактуры "A1122". См. Ниже:

;WITH
Allfolios -- Convert all rows to a hierarchy
AS
(
    Select i.folio, i.InvoiceNumber,
          hierarchyid::Parse('/' + 
               CAST(ROW_NUMBER() 
                        OVER (ORDER BY InvoiceNumber) AS VARCHAR(30)
                   ) + '/') AS hierarchy, 1 as level
    from invoice i
    WHERE NOT EXISTS 
        (SELECT * FROM invoice_relation ir WHERE ir.invoice = i. invoicenumber)
    UNION ALL
    SELECT i.folio, i.invoiceNumber,
          hierarchyid::Parse(CAST(a.hierarchy as VARCHAR(30)) + 
                             CAST(ROW_NUMBER() 
                                   OVER (ORDER BY a.InvoiceNumber) 
                               AS VARCHAR(30)) + '/') AS hierarchy, 
          level + 1
    FROM Allfolios A
    INNER JOIN invoice_relation ir
        on a.InvoiceNumber = ir.ParentInvoice
    INNER JOIN invoice i
        on ir.Invoice = i.invoicenumber
),
Root -- Find Root
AS
(
    SELECT *
    FROM AllFolios AF
    WHERE Level = 1 AND 
    (SELECT hierarchy.IsDescendantOf(AF.hierarchy)  from AllFolios AF2 WHERE folio = 1003) = 1
)
-- Find all descendants of the root element which have an invoicenumber = 'A1122'
SELECT *
FROM ALLFolios
WHERE hierarchy.IsDescendantOf((SELECT TOP 1 hierarchy FROM Root)) = 1 AND
invoicenumber = 'A1122'

Ответ 5

Это было сложно, так как у вас есть отдельная таблица отношений, а корневой счет не находится в ней.

DECLARE @folio INT = 1003,
        @invoice NVARCHAR(20) = 'A1122'


-- find highest level of relationship
;WITH cte  AS (
    SELECT  i.folio,
            i.invoicenumber,
            ir.parentinvoice,
            0 AS [level]
    FROM    invoice i
            LEFT JOIN invoice_relation ir  ON ir.invoice = i.invoicenumber
    WHERE   i.folio = @folio
    UNION ALL 
    SELECT  i.folio,
            i.invoicenumber,
            ir.parentinvoice,
            [level] + 1
    FROM    invoice i
            JOIN invoice_relation ir  ON ir.invoice = i.invoicenumber
            JOIN cte r ON r.parentinvoice = i.invoicenumber
),
-- make sure you get the root folio
rootCte AS (
    SELECT  COALESCE(oa.folio, c.folio) AS rootFolio 
    FROM    (SELECT     *,
                        ROW_NUMBER() OVER (ORDER BY [level] DESC) Rn
                FROM    cte ) c
             OUTER APPLY (SELECT folio FROM invoice i WHERE i.invoicenumber = c.parentinvoice) oa
    WHERE    c.Rn = 1
),
-- get all children of root folio
fullTree AS (
    SELECT  i.folio,
            i.invoicenumber
    FROM    rootCte r
            JOIN invoice i ON r.rootFolio = i.folio
    UNION ALL 
    SELECT  i.folio,
            i.invoicenumber
    FROM    fullTree ft
            JOIN invoice_relation ir ON ir.parentinvoice = ft.invoicenumber
            JOIN invoice i ON ir.invoice = i.invoicenumber
)
-- search for invoice
SELECT  * 
FROM    fullTree
WHERE   invoicenumber = @invoice

Ответ 6

Вот попытка, которая сначала сглаживает отношения, поэтому вы можете путешествовать в любом направлении. Затем рекурсивный CTE работает через уровни:

WITH invoicerelation AS
(
select relationid, invoice, parentinvoice AS relatedinvoice
from invoice_relation
union
select relationid, parentinvoice AS invoice, invoice AS relatedinvoice 
from invoice_relation
),

cteLevels AS
(
select 0 AS relationid, invoice.folio, 
   invoicenumber AS invoice, invoicenumber AS relatedinvoice, 
   0 AS Level
from invoice 
UNION ALL
select invoicerelation.relationid, invoice.folio,
    invoicerelation.invoice, cteLevels.relatedinvoice, 
    Level + 1 AS Level
from invoice INNER JOIN
   invoicerelation ON invoice.invoicenumber = invoicerelation.invoice INNER JOIN
   cteLevels ON invoicerelation.relatedinvoice = cteLevels.invoice
      and (ctelevels.relationid <> invoicerelation.relationid)
)

SELECT cteLevels.folio, relatedinvoice, invoice.folio AS invoicefolio, cteLevels.level
from cteLevels INNER JOIN
    invoice ON cteLevels.relatedinvoice = invoice.invoicenumber
WHERE cteLevels.folio = 1003 AND cteLevels.relatedinvoice = 'a1122'

Я согласен с комментарием SwampDev, что parentinvoice действительно должен находиться в таблице счетов. Это также можно было бы сделать без рекурсивного CTE, если вы знаете максимальное количество уровней разделения между счетами.