Неопределенное количество рекурсии SQL
Имеются три таблицы:
data_report - исходные данные с нужными номерами узлов
| id | node_number | ... |
|---|---|---|
| 1 | 20923 | |
| 2 | 20328 | |
| 3 | 20589 |
node - соотношение id узла и номера узла
| id | node_id | number | ... |
|---|---|---|---|
| 1 | 700 | 20823 | |
| 2 | 701 | 20328 | |
| 3 | 702 | 20589 | |
| 4 | 704 | 30142 | |
| 5 | 809 | 40144 |
node_relations - отношение между узлам (у узлов есть подузлы - в поле head_node_id указывается головной узел)
| id | node_id | head_node_id | ... |
|---|---|---|---|
| 14 | 704 | 702 | |
| 74 | 809 | 704 |
Необходимо извлечь информацию о узлах и подузлах из таблице [node], номера которых взять в таблице [data_report]
Я смог додуматься только до такого решения:
WITH nodes_from_data_report AS (
SELECT node_id FROM node WHERE node_number IN (SELECT data_report.node_number FROM data_report)
), nodes_from_recursive_one AS (
SELECT node_relationships.node_id FROM node_relationships WHERE head_node_id IN (SELECT * FROM nodes_from_data_report)
), nodes_from_recursive_two AS (
SELECT node_relationships.node_id FROM node_relationships WHERE head_node_id IN (SELECT * FROM nodes_from_recursive_one)
), nodes_from_recursive_three AS (
SELECT node_relationships.node_id FROM node_relationships WHERE head_node_id IN (SELECT * FROM nodes_from_recursive_two)
), union_all_nodes AS (
SELECT * FROM nodes_from_data_report
UNION ALL
SELECT * FROM nodes_from_recursive_one
UNION ALL
SELECT * FROM nodes_from_recursive_two
UNION ALL
SELECT * FROM nodes_from_recursive_three
)
SELECT [node].[id]
,[node].[node_id]
,[node].[node_number]
,[node].[description]
,[node].[count]
,[node].[other_info]
FROM [TestRecursive].[dbo].[node]
WHERE node_id IN (SELECT * FROM union_all_nodes)
Самая главная проблема, которую вижу я - это необходимость знать уровень вложенности узлов. Хотелось бы, чтобы он продолжал отпускаться вниз пока не кончатся узлы или пока не будет произведено n погружений (на случай, если там есть зацикливание). Буду рад любой помощи!
Код для создания мини базы для тестов:
USE [master]
GO
/****** Object: Database [TestRecursive] Script Date: 28.09.2022 9:42:50 ******/
CREATE DATABASE [TestRecursive]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TestRecursive', FILENAME = N'D:\DATA\TestRecursive.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'TestRecursive_log', FILENAME = N'D:\logs\TestRecursive_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [TestRecursive] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestRecursive].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TestRecursive] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestRecursive] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestRecursive] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestRecursive] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestRecursive] SET ARITHABORT OFF
GO
ALTER DATABASE [TestRecursive] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TestRecursive] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestRecursive] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestRecursive] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestRecursive] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TestRecursive] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestRecursive] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TestRecursive] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestRecursive] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestRecursive] SET DISABLE_BROKER
GO
ALTER DATABASE [TestRecursive] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TestRecursive] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TestRecursive] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TestRecursive] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TestRecursive] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TestRecursive] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TestRecursive] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TestRecursive] SET RECOVERY FULL
GO
ALTER DATABASE [TestRecursive] SET MULTI_USER
GO
ALTER DATABASE [TestRecursive] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TestRecursive] SET DB_CHAINING OFF
GO
ALTER DATABASE [TestRecursive] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [TestRecursive] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [TestRecursive] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [TestRecursive] SET ACCELERATED_DATABASE_RECOVERY = OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'TestRecursive', N'ON'
GO
ALTER DATABASE [TestRecursive] SET QUERY_STORE = OFF
GO
USE [TestRecursive]
GO
/****** Object: Table [dbo].[data_report] Script Date: 28.09.2022 9:42:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[data_report](
[id] [int] IDENTITY(1,1) NOT NULL,
[node_number] [int] NOT NULL,
[description] [nchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[node] Script Date: 28.09.2022 9:42:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[node](
[id] [int] IDENTITY(1,1) NOT NULL,
[node_id] [int] NOT NULL,
[node_number] [int] NULL,
[description] [nchar](50) NULL,
[count] [int] NULL,
[other_info] [nchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[node_relationships] Script Date: 28.09.2022 9:42:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[node_relationships](
[id] [int] IDENTITY(1,1) NOT NULL,
[node_id] [int] NOT NULL,
[head_node_id] [int] NOT NULL,
[other_info] [nchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: View [dbo].[Recursive] Script Date: 28.09.2022 9:42:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [dbo].[Recursive]
AS
WITH nodes_from_data_report AS (SELECT node_id
FROM dbo.node
WHERE (node_number IN
(SELECT node_number
FROM dbo.data_report))), nodes_from_recursive_one AS
(SELECT node_id
FROM dbo.node_relationships
WHERE (head_node_id IN
(SELECT node_id
FROM nodes_from_data_report AS nodes_from_data_report_2))), nodes_from_recursive_two AS
(SELECT node_id
FROM dbo.node_relationships AS node_relationships_2
WHERE (head_node_id IN
(SELECT node_id
FROM nodes_from_recursive_one AS nodes_from_recursive_one_2))), nodes_from_recursive_three AS
(SELECT node_id
FROM dbo.node_relationships AS node_relationships_1
WHERE (head_node_id IN
(SELECT node_id
FROM nodes_from_recursive_two AS nodes_from_recursive_two_2))), union_all_nodes AS
(SELECT node_id
FROM nodes_from_data_report AS nodes_from_data_report_1
UNION ALL
SELECT node_id
FROM nodes_from_recursive_one AS nodes_from_recursive_one_1
UNION ALL
SELECT node_id
FROM nodes_from_recursive_two AS nodes_from_recursive_two_1
UNION ALL
SELECT node_id
FROM nodes_from_recursive_three AS nodes_from_recursive_three_1)
SELECT id, node_id, node_number, description, count, other_info
FROM dbo.node AS node_1
WHERE (node_id IN
(SELECT node_id
FROM union_all_nodes AS union_all_nodes_1))
GO
SET IDENTITY_INSERT [dbo].[data_report] ON
GO
INSERT [dbo].[data_report] ([id], [node_number], [description]) VALUES (1, 20923, N'Узел 1 для отчёта ')
GO
INSERT [dbo].[data_report] ([id], [node_number], [description]) VALUES (2, 20328, N'Узел 2 для отчёта ')
GO
INSERT [dbo].[data_report] ([id], [node_number], [description]) VALUES (3, 20589, N'Узел 3 для отчёта ')
GO
SET IDENTITY_INSERT [dbo].[data_report] OFF
GO
SET IDENTITY_INSERT [dbo].[node] ON
GO
INSERT [dbo].[node] ([id], [node_id], [node_number], [description], [count], [other_info]) VALUES (1, 700, 20923, N'Информация о узле 20923 ', 4, N'Какая-то инфа 1 ')
GO
INSERT [dbo].[node] ([id], [node_id], [node_number], [description], [count], [other_info]) VALUES (2, 701, 20328, N'Информация о узле 20328 ', 1, N'Какая-то инфа 2 ')
GO
INSERT [dbo].[node] ([id], [node_id], [node_number], [description], [count], [other_info]) VALUES (3, 702, 20589, NULL, NULL, NULL)
GO
INSERT [dbo].[node] ([id], [node_id], [node_number], [description], [count], [other_info]) VALUES (4, 704, 30142, N'Информация о узле 30142 ', NULL, NULL)
GO
INSERT [dbo].[node] ([id], [node_id], [node_number], [description], [count], [other_info]) VALUES (5, 809, 40144, N'Информация о узле 809 ', 20, NULL)
GO
INSERT [dbo].[node] ([id], [node_id], [node_number], [description], [count], [other_info]) VALUES (6, 901, 21541, NULL, NULL, NULL)
GO
INSERT [dbo].[node] ([id], [node_id], [node_number], [description], [count], [other_info]) VALUES (7, 1002, 50478, NULL, NULL, N'Какая-то инфа 7 ')
GO
SET IDENTITY_INSERT [dbo].[node] OFF
GO
SET IDENTITY_INSERT [dbo].[node_relationships] ON
GO
INSERT [dbo].[node_relationships] ([id], [node_id], [head_node_id], [other_info]) VALUES (1, 704, 702, N'Информация об отношениях 1 ')
GO
INSERT [dbo].[node_relationships] ([id], [node_id], [head_node_id], [other_info]) VALUES (2, 809, 704, N'Информация об отношениях 2 ')
GO
SET IDENTITY_INSERT [dbo].[node_relationships] OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "node_1"
Begin Extent =
Top = 6
Left = 38
Bottom = 136
Right = 208
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Recursive'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'Recursive'
GO
USE [master]
GO
ALTER DATABASE [TestRecursive] SET READ_WRITE
GO