Блог

Главная - Вопросы по программированию - Группируйте связанные записи в запросе

Группируйте связанные записи в запросе

#sql-server

#sql-сервер

Вопрос:

Я ищу способ группировать связанные записи. Таблица, с которой я работаю, содержит уникальные пары идентификаторов. Связанные записи — это записи, в которых один из идентификаторов равен тому же идентификатору в другой записи.

Например, если запись содержит 384 и 768 для ID1 и ID2, то другая запись с 384 и 512 для ID1 и ID2 будет связана. Кроме того, другая запись с 256 и 768 также будет связана. Однако записи с 1024 и 1536 не будут связаны.

Пример таблицы и данных приведен ниже.

 CREATE TABLE [dbo].[Identities](
    [AN] [varchar](30) NOT NULL,
    [ID1] [varchar](30) NOT NULL,
    [ID2] [varchar](30) NULL,
    [LastUpdateDate] [datetimeoffset](7) NOT NULL,
    CONSTRAINT [PK_Identities] PRIMARY KEY CLUSTERED 
    (
        [AN] ASC
    ) ON [PRIMARY],
    CONSTRAINT [AK_ID] UNIQUE NONCLUSTERED 
    (
        [ID1] ASC,
        [ID2] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Identities] ([AN], [ID1], [ID2], [LastUpdateDate])
VALUES
    ('00243', '6000086', '2200720', '2016-05-26 17:16:07.0000000 -04:00')
,   ('00267', '6000752', '5700243', '2017-06-21 12:24:55.0000000 -04:00')
,   ('00284', '6000086', '5000385', '2020-02-11 11:56:01.0000000 -05:00')
,   ('00352', '6000752', '2200720', '2016-05-31 20:08:47.0000000 -04:00')
,   ('00360', '6000752', '6000779', '2019-04-17 20:46:15.0000000 -04:00')
,   ('00475', '6200598', '5000385', '2017-06-21 18:29:49.0000000 -04:00')
,   ('00507', '6200599', '5000385', '2017-06-21 18:37:21.0000000 -04:00')
,   ('00558', '6200606', '5000385', '2017-06-21 18:14:51.0000000 -04:00')
,   ('00735', '5500045', '2200721', '2018-10-11 15:30:50.0000000 -04:00')
GO

Из этого примера данных желаемый результат приведен ниже.

 AN      ID1     ID2     LastUpdateDate                      Group
00243   6000086 2200720 2016-05-26 17:16:07.0000000 -04:00  1
00267   6000752 5700243 2017-06-21 12:24:55.0000000 -04:00  1
00284   6000086 5000385 2020-02-11 11:56:01.0000000 -05:00  1
00352   6000752 2200720 2016-05-31 20:08:47.0000000 -04:00  1
00360   6000752 6000779 2019-04-17 20:46:15.0000000 -04:00  1
00475   6200598 5000385 2017-06-21 18:29:49.0000000 -04:00  1
00507   6200599 5000385 2017-06-21 18:37:21.0000000 -04:00  1
00558   6200606 5000385 2017-06-21 18:14:51.0000000 -04:00  1
00735   5500045 2200721 2018-10-11 15:30:50.0000000 -04:00  2

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

Ответ №1:

Я думаю, вам нужно 2 рекурсивных cte, потому что ссылки должны идти в 2 направлениях. Что-то вроде этого:

 WITH cte AS--recursive cte id2
  (SELECT i1.ID1, i1.ID2
   FROM [dbo].[Identities] i1

   UNION ALL 
   
   SELECT c.ID1,i2.ID1
   FROM [dbo].[Identities] i2
   JOIN cte c ON c.ID2 = i2.ID2
   )   ,

cte2 AS--recursive cte id1
  (SELECT i1.ID1, i1.ID2
   FROM [dbo].[Identities] i1

   UNION ALL 
   
   SELECT c.ID2, i2.ID2
   FROM [dbo].[Identities] i2
   JOIN cte c ON c.ID1 = i2.ID1
   )   ,

cte3 AS--union both recursive cte's
  (SELECT *
   FROM cte
   UNION 
   SELECT *
   FROM cte2
   ),
     
cte4 AS--group
  (SELECT id1, min(id2) AS id2, dense_rank() OVER (ORDER BY min(id2)) AS grp
   FROM cte3
   GROUP BY id1)

SELECT i.AN,i.ID1,i.ID2,i.LastUpdateDate,c.grp
FROM cte4 c
JOIN [dbo].[Identities] i ON i.id1 = c.id1