Início / dba / Perguntas / 190221
Accepted
Biju jose
Asked: 2017-11-07 07:35:58 +0800 CST

Analisar isso para a lista de adjacências

  • 772

Estou tentando analisar esta lista para uma lista de adjacências aqui o exemplo DDL.

CREATE TABLE [dbo].[classification](
    [sno] [float] NULL,
    [classification] [nvarchar](255) NULL,
    [classificationTitle] [nvarchar](255) NULL
) ON [PRIMARY]

INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (1, N'23-11 00 00', N'Site Products')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (2, N'23-11 11 00', N'Ground Anchorages')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (3, N'23-11 11 11', N'Retaining Stabilizing Ground Anchors')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (4, N'23-11 11 11 11', N'Retaining Stabilizing Ground Components')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (5, N'23-11 11 11 11 11', N'Stabilizing Ground Anchor Heads')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (6, N'23-11 11 11 11 13', N'Stabilizing Ground Tendons')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (7, N'23-11 11 11 13', N'Stabilizing Ground Grouted Anchors')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (8, N'23-11 11 11 15', N'Stabilizing Ground Plate Anchors')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (9, N'23-11 11 11 17', N'Stabilizing Ground Rock Bolts')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (10, N'23-11 11 11 19', N'Stabilizing Ground Rock Anchors')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (11, N'23-11 11 11 21', N'Stabilizing Ground Anchor Tiebacks')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (12, N'23-11 11 13', N'Earth Reinforcement Anchors')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (13, N'23-11 11 13 11', N'Earth Reinforcement Soil Nails')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (14, N'23-11 13 00', N'Ground Improvement Products')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (15, N'23-11 13 11', N'Soil Stabilization Products')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (16, N'23-11 13 11 11', N'Soil Stabilization Injectable Chemicals')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (17, N'23-11 13 11 13', N'Soil Stabilization Pressure Grouting')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (18, N'23-11 13 11 15', N'Ground Freezing Soil Stabilization')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (19, N'23-11 13 11 17', N'Soil Stabilization Fills')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (20, N'23-11 13 11 17 11', N'Soil Stabilization Fill Blocks')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (21, N'23-11 13 11 17 13', N'Soil Stabilization Compressible Fill')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (22, N'23-11 13 11 19', N'Other Soil Stabilizations')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (23, N'23-11 13 11 21', N'Piped Field Drainage')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (24, N'23-11 13 11 21 11', N'Field Drainage Land Drainage Pipes')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (25, N'23-11 13 11 23', N'Field Drainage Blocks')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (26, N'23-11 13 11 25', N'Field Drainage Geocomposite Drains')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (27, N'23-11 13 11 25 11', N'Field Drainage Geocomposite Edge Drains')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (28, N'23-11 13 11 25 13', N'Field Drainage Geocomposite In Place Wall Drains')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (29, N'23-11 13 11 27', N'Geotextile Subsurface Drainage Filtration')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (30, N'23-11 15 00', N'Sheeting and Revetments')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (31, N'23-11 15 11', N'Sheeting Geosynthetics')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (32, N'23-11 15 11 11', N'Sheeting Geotextiles')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (33, N'23-11 15 11 13', N'Sheeting Geogrids')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (34, N'23-11 15 11 15', N'Sheeting Geomembranes')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (35, N'23-11 15 11 17', N'Sheeting Geocomposites')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (36, N'23-11 15 11 19', N'Sheeting Mulch Control Netting')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (37, N'23-11 15 11 21', N'Sheeting Synthetic Erosion Controls')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (38, N'23-11 15 11 23', N'Sheeting Re vegetation Mats')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (39, N'23-11 15 11 25', N'Sheeting Turf Reinforcement Mats')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (40, N'23-11 15 13', N'Revetments')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (41, N'23-11 15 13 11', N'Revetment Soil Blankets')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (42, N'23-11 15 13 13', N'Pool Revetments')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (43, N'23-11 15 13 15', N'Trench Revetments')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (44, N'23-11 15 13 17', N'Revetment Rock Linings')
GO
INSERT [dbo].[classification] ([sno], [classification], [classificationTitle]) VALUES (45, N'23-11 15 13 19', N'Revetment Ripraps')
GO

e estou tentando obter essa saída

+----------+---------+
| parentid | childid |
+----------+---------+
| NULL     |       1 |
| 1        |       2 |
| 2        |       3 |
| 3        |       4 |
| 4        |       5 |
| 4        |       6 |
| 3        |       7 |
| 3        |       8 |
| 3        |       9 |
| 3        |      10 |
| 3        |      11 |
| 1        |      12 |
| 12       |      13 |
+----------+---------+

Até agora eu vim com isso.

;with cte as (
select LEFT([classification], LEN([classification]) - 3)as immediateparent,
[classification],sno
FROM [classification]
)
select a.sno,cte.sno from [classification] a 
left join cte on cte.immediateparent =a.[classification]

Mas não consegui encontrar nenhuma ajuda.

sql-server t-sql

2 respostas

  1. Best Answer

    Dê uma chance a isso:

    SELECT  parent.sno parentid, child.sno childid
FROM    classification child
--get all the matches where parent.classification is a subset of child.classification
OUTER APPLY (
    --only consider the highest ranking result
    SELECT  TOP 1 *
    FROM    classification parent
    --ignore the 00 entries - these appear to represent null values
    WHERE   CHARINDEX(REPLACE(parent.classification, ' 00', ''), child.classification) > 0
    --don't consider equivalent records as a match
    AND parent.sno <> child.sno
    --where the rank is based on the length of the match (assume the longest match is the adjacent node
    ORDER BY LEN(REPLACE(parent.classification, ' 00', '')) DESC
) parent
--based on the results displayed in the question
WHERE   child.sno BETWEEN 1 AND 13
--based on the ordering displayed in the question
ORDER BY child.sno, parent.sno

    Estou assumindo que 00 valores aparecem apenas no final de uma classificação e que podem ser ignorados (ou seja, eles são espaços reservados para null, ou algo nesse sentido). Uma explicação de como funciona a consulta está nos comentários do código. Isso funcionará se as entradas do gráfico na classificação estiverem ou não em ordem por sno.

    • 1

  2. Não tenho certeza se entendi o resultado desejado, mas talvez um CROSS APPLY possa resolver sua dúvida.

    SELECT c1.sno, c1.classification, c2.nextClass, c2.nextSno
FROM   Classification c1
CROSS APPLY (SELECT TOP 1 classification nextClass, sno nextSno
             FROM   classification
             WHERE  classification > c1.classification
             ORDER BY classification) c2
ORDER BY classification;
GO

    sno | classificação | próximaClasse | nextSno
--: | :---------------- | :---------------- | ------:
  1 | 23-11 00 00 | 23-11 11 00 | 2
  2 | 23-11 11 00 | 23-11 11 11 | 3
  3 | 23-11 11 11 | 23-11 11 11 11 | 4
  4 | 23-11 11 11 11 | 23-11 11 11 11 11 | 5
  5 | 23-11 11 11 11 11 | 23-11 11 11 11 13 | 6
  6 | 23-11 11 11 11 13 | 23-11 11 11 13 | 7
  7 | 23-11 11 11 13 | 23-11 11 11 15 | 8
  8 | 23-11 11 11 15 | 23-11 11 11 17 | 9
  9 | 23-11 11 11 17 | 23-11 11 11 19 | 10
 10 | 23-11 11 11 19 | 23-11 11 11 21 | 11
 11 | 23-11 11 11 21 | 23-11 11 13 | 12
 12 | 23-11 11 13 | 23-11 11 13 11 | 13
 13 | 23-11 11 13 11 | 23-11 13 00 | 14
 14 | 23-11 13 00 | 23-11 13 11 | 15
 15 | 23-11 13 11 | 23-11 13 11 11 | 16
 16 | 23-11 13 11 11 | 23-11 13 11 13 | 17
 17 | 23-11 13 11 13 | 23-11 13 11 15 | 18
 18 | 23-11 13 11 15 | 23-11 13 11 17 | 19
 19 | 23-11 13 11 17 | 23-11 13 11 17 11 | 20
 20 | 23-11 13 11 17 11 | 23-11 13 11 17 13 | 21
 21 | 23-11 13 11 17 13 | 23-11 13 11 19 | 22
 22 | 23-11 13 11 19 | 23-11 13 11 21 | 23
 23 | 23-11 13 11 21 | 23-11 13 11 21 11 | 24
 24 | 23-11 13 11 21 11 | 23-11 13 11 23 | 25
 25 | 23-11 13 11 23 | 23-11 13 11 25 | 26
 26 | 23-11 13 11 25 | 23-11 13 11 25 11 | 27
 27 | 23-11 13 11 25 11 | 23-11 13 11 25 13 | 28
 28 | 23-11 13 11 25 13 | 23-11 13 11 27 | 29
 29 | 23-11 13 11 27 | 23-11 15 00 | 30
 30 | 23-11 15 00 | 23-11 15 11 | 31
 31 | 23-11 15 11 | 23-11 15 11 11 | 32
 32 | 23-11 15 11 11 | 23-11 15 11 13 | 33
 33 | 23-11 15 11 13 | 23-11 15 11 15 | 34
 34 | 23-11 15 11 15 | 23-11 15 11 17 | 35
 35 | 23-11 15 11 17 | 23-11 15 11 19 | 36
 36 | 23-11 15 11 19 | 23-11 15 11 21 | 37
 37 | 23-11 15 11 21 | 23-11 15 11 23 | 38
 38 | 23-11 15 11 23 | 23-11 15 11 25 | 39
 39 | 23-11 15 11 25 | 23-11 15 13 | 40
 40 | 23-11 15 13 | 23-11 15 13 11 | 41
 41 | 23-11 15 13 11 | 23-11 15 13 13 | 42
 42 | 23-11 15 13 13 | 23-11 15 13 15 | 43
 43 | 23-11 15 13 15 | 23-11 15 13 17 | 44
 44 | 23-11 15 13 17 | 23-11 15 13 19 | 45

    dbfiddle aqui

    • 0

relate perguntas