Sql группировка по условию, с присвоением порядкового номера

@Nechto · Регистрация: 08.01.2010

Студворк — интернет-сервис помощи студентам

Здравствуйте

Пытаюсь выполнить одновременную группировку по наименнованию и атрибутам, с присвоением одинакового порядкового номер к каждой группе.

исходные данные

SQL
1
2
3
4
5
6
7
8
9
10
11
DECLARE @T TABLE (ID INT, P INT, N1 nvarchar(10), N2 nvarchar(10), ARGM1 INT, ARGM2 INT, ARGM3 nvarchar(4));
INSERT INTO @T (ID, P, N1, N2, ARGM1, ARGM2, ARGM3)
         SELECT 1001 ID, NULL P, 'K' N1, 'N1' N2, 1 A1, 1 A2, '0007' A3 UNION ALL
         SELECT 1002,    NULL,   'N1',   'N2',    1,    1,    '0007'    UNION ALL
     SELECT 1003,    NULL,   'N2',   'D',     1,    1,    '0007'    UNION ALL
     SELECT 1004,    NULL,   'N1',   'N3',    1,    2,    '0007'    UNION ALL
     SELECT 1005,    NULL,   'D',    'R',     1,    1,    '0007'    UNION ALL
     SELECT 1006,    NULL,   'R',    'R1',    1,    1,    '0002'    UNION ALL
     SELECT 1007,    NULL,   'R1',   'TT',    1,    1,    '0002'    UNION ALL
     SELECT 1008,    NULL,   'D1',   'RE',    1,    1,    '0007'    UNION ALL
     SELECT 1009,    NULL,   'D2',   'RE',    1,    1,    '0007';

Группировка по атрибутам получилась

SQL
1
2
3
4
5
SELECT *,  
       -- По аргументам (Все работает так как надо.)
       DENSE_RANK() OVER( partition BY CASE WHEN EXISTS(SELECT 1 FROM @T WHERE  ARGM1 = T.ARGM1 AND ARGM2 = T.ARGM2 AND ARGM3 = T.ARGM3) THEN 1 END 
                          ORDER BY t.ARGM1, t.ARGM2, t.ARGM3) AS TestARGM 
  FROM @T t

А вот по наименнованию не получается.

SQL
1
2
3
4
5
SELECT *,
       -- По наименнованию (Не работает)
       DENSE_RANK() OVER( partition BY CASE WHEN EXISTS(SELECT 1 FROM R1 WHERE ((N1 = t.N1 OR N2 = t.N1) OR (N1 = t.N2 OR N2 = t.N2))) THEN 1 END 
                    ORDER BY t.N1, t.N2) AS TestARGM,
  FROM @T t

В итоге должно получиться так:

SQL
1
2
3
4
5
6
7
8
9
10
11
--  ID       P    N1   N2    A1  A2     A3    ARGMUNION
--------------------------------------------------------
--1001    null    K    N1     1   1     0007         1   
--1002    null    N1   N2     1   1     0007         1 
--1003    null    N2   D      1   1     0007         1
--1004    null    N1   N3     1   2     0007         2 
--1005    null    D    R      1   1     0007         1
--1006    null    R    R1     1   1     0002         3
--1007    null    R1   TT     1   1     0002         3    
--1008    null    D1   RE     1   1     0007         4
--1009    null    D2   RE     1   1     0007         4

@Nechto · 01.03.2015, 16:48 **[ТС]**

Люди добрые, неужели никто не поможет?

@invm · 01.03.2015, 18:17

Сообщение от Nechto

Люди добрые, неужели никто не поможет?

Если сумеете внятно объяснить, что нужно, может и помогут.

@texnix · 02.03.2015, 12:36

сортировка по наименованию:

T-SQL
1
SELECT *  FROM @T t ORDER BY t.N1, t.N2

группировка по наименованию:

T-SQL
1
SELECT t.N1, t.N2  FROM @T t group BY t.N1, t.N2

эта часть не понятна:

В итоге должно получиться так:

потому что при группировке не может выйти полная таблица.

@Nechto · 02.03.2015, 12:42 **[ТС]**

Максимально упрощу вопрос, и распишу по этапам логику.

SQL
1
2
3
4
5
6
7
8
9
-- Исходная таблица
DECLARE @T TABLE (ID INT, N1 nvarchar(10), N2 nvarchar(10));
INSERT INTO @T (ID, N1, N2)
         SELECT 1001 ID, 'A' N1, 'P1' N2 UNION ALL
         SELECT 1002,    'P1',   'P2'    UNION ALL
         SELECT 1003,    'P2',   'B'     UNION ALL
         SELECT 1004,    'C',    'D1'    UNION ALL
         SELECT 1005,    'D1',   'D2'    UNION ALL
         SELECT 1006,    'D2',   'D';

XML
1
2
3
4
5
6
7
8
9
10
К примеру имеется такой набор полей N1 и N2:
ID  |  N1  |  N2 
---------------
1      A      P1    
2      P1     P2
3      P2     B    
 
4      C      D1     
5      D1     D2     
6      D2     D

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

Берем строку (1, 'A', 'P1'), начинаем искать по полям N1 и N2 совпадения:
Нашли
(1, 'A', 'P1')
(2, 'P1', 'P2')
Данным парам присвоили порядковый номер (1), поскольку номеров не найдено;

Берем строку (2, 'P1', 'P2'), начинаем искать по полям N1 и N2 совпадения:
Нашли
(1, 'A', 'P1')
(2, 'P1', 'P2')
(3, 'P2', 'B')
Присвоили этой паре порядковый номер (1), поскольку номер найден номер;

Берем строку (3, 'P2', 'B'), начинаем искать по полям N1 и N2 совпадения:
Нашли
(2, 'P1', 'P2')
(3, 'P2', 'B')
Присвоили этой паре порядковый номер (1), поскольку номер найден номер;

Далее по аналогии
Берем строку (4, 'C', 'D1'), начинаем искать по полям N1 и N2 совпадения:
Нашли
(4, 'C', 'D1')
Присвоили этой паре порядковый номер (2), поскольку номеров не найдено
...

XML
1
2
3
4
5
6
7
8
9
10
--В результате должно получиться так:
ID  |  N1  |  N2  |  NEW_NOMER
-------------------------------
1      A      P1     1
2      P1     P2     1
3      P2     B      1
 
4      C      D1     2
5      D1     D2     2
6      D2     D      2

Надеюсь я более прозрачно описал, что я хочу получить?

@texnix · 02.03.2015, 12:53

Сообщение от Nechto

Данным парам присвоили порядковый номер (1), поскольку номеров не найдено;

Сообщение от Nechto

Присвоили этой паре порядковый номер (1), поскольку номер найден номер;

нет, нифига не понятно, это не логично:
если не найден, то :=1
иначе :=1

тут нет алгоритма.

@invm · 02.03.2015, 12:57

Сообщение от Nechto

Надеюсь я более прозрачно описал, что я хочу получить?

Догадаться, что вы хотите практически невозможно. Хорошо хоть в конце привели в желаемый результат.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
with s as
(
 select
  t.N1 as root_N1, t.*
 from
  @t t
 where
  not exists(select 1 from @t where N2 = t.N1)
 
 union all
 
 select
  s.root_N1, t.*
 from
  s join
  @t t on t.N1 = s.N2
)
select
 s.ID, s.N1, s.N2, dense_rank() over (order by s.root_N1) as NEW_NOMER
from
 s;

@Nechto · 02.03.2015, 14:03 **[ТС]**

invm,
Спасибо! То что надо! И с аргументами выборку делает правильную. Еще раз спасибо!!!

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Исходная таблица
DECLARE @T TABLE (ID INT, N1 nvarchar(10), N2 nvarchar(10), ARGM1 int, ARGM2 int);
INSERT INTO @T (ID, N1, N2, ARGM1, ARGM2)
         SELECT 1001 ID, 'A' N1, 'P1' N2, 1 A1, 1 A2 UNION ALL
         SELECT 1002,    'P1',   'P2',    1,    2    UNION ALL
         SELECT 1003,    'P2',   'B',     1,    1    UNION ALL
         SELECT 1004,    'C',    'D1',    1,    1    UNION ALL
         SELECT 1005,    'D1',   'D2',    1,    1    UNION ALL
         SELECT 1006,    'D2',   'D',     2,    1;
 
WITH S AS
(
 select T.N1 as ROOT_N1, T.*
   from @T T
  where not exists(select 1 from @T where N2 = T.N1 and argm1 = T.argm1 and argm2 = T.argm2)
 
 union all
 
 select S.ROOT_N1, T.*
   from S 
   join @T T on T.N1 = S.N2 and T.ARGM1 = S.ARGM1 and T.ARGM2 = S.ARGM2
)
 
SELECT M.ID, M.N1, M.N2, M.ARGM1, M.ARGM2,
       DENSE_RANK() OVER (ORDER BY M.ROOT_N1) as NEW_NOMER
  FROM S M;

@Nechto · 03.03.2015, 14:24 **[ТС]**

Извините что сразу же не предусмотрел, что в наборе данных могут быть нулевые N1 или N2.
Пожалуйста уделите еще немного времени моему вопросу.

XML
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Исходная таблица
DECLARE @T TABLE (ID INT, N1 nvarchar(10), N2 nvarchar(10));
INSERT INTO @T (ID, N1, N2)
         SELECT 1001 ID, 'P1' N1, 'A' N2 UNION ALL
         SELECT 1002,    'P1',   'P2'    UNION ALL
         SELECT 1003,    'P2',   'B'     UNION ALL
         SELECT 1004,    'C',    'D1'    UNION ALL
         SELECT 1005,    'D1',   'D2'    UNION ALL
         SELECT 1006,    'D2',   'D'     UNION ALL
         SELECT 1007,    null,   null    UNION ALL   
         SELECT 1008,    null,   null    UNION ALL
         SELECT 1009,    null,   'M'     UNION ALL
;

XML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--В результате должно получиться так:
ID  |  N1  |  N2  |  NEW_NOMER
-------------------------------
1      P1     A      1
2      P1     P2     1
3      P2     B      1
 
4      C      D1     2
5      D1     D2     2
6      D2     D      2
 
7      null   null   3
8      null   null   3
 
9      null   M      4

@invm · 03.03.2015, 14:32

А подумать не?

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
with s as
(
 select
  t.N1 as root_N1, t.N2 as root_N2, t.*
 from
  @t t
 where
  not exists(select 1 from @t where N2 = t.N1)
 
 union all
 
 select
  s.root_N1, s.root_N2, t.*
 from
  s join
  @t t on t.N1 = s.N2
)
select
 s.ID, s.N1, s.N2, dense_rank() over (order by s.root_N1, s.root_N2) as NEW_NOMER
from
 s;

@Nechto · 03.03.2015, 15:41 **[ТС]**

Дело в том, что в примере поиск идет так: N1 ищем в N2 либо N2 ищем в N1.

Видите здесь P1 два раза повторяется в N1

XML
1
2
3
SELECT 1001,    'P1',   'A'     UNION ALL
SELECT 1002,    'P1',   'P2'    UNION ALL
SELECT 1003,    'P2',   'B'     UNION ALL

А надо чтобы поиск был такой: (N1 искали в N1 и N2) и (N2 искали в N1 и N2).

Добавлено через 50 минут
Если без воды, то группирует не правильно в этих данных:

XML
1
2
3
SELECT 1001,    'P1',   'A'     UNION ALL
SELECT 1002,    'P1',   'P2'    UNION ALL
SELECT 1003,    'P2',   'B'     UNION ALL

Поскольку 'P1' два раза повторяется в N1.

Новые блоги и статьи Все статьи Все блоги /
Модель микоризы: классовый агентный подход anaschu 02.01.2026 Раньше это было два гриба и бактерия. Теперь три гриба, растение. И на уровне агентов добавится между грибами или бактериями взаимодействий. До того я пробовал подход через многомерные массивы,. . .	Учёным и волонтёрам проекта «Einstein@home» удалось обнаружить четыре гамма-лучевых пульсара в джете Млечного Пути Programma_Boinc 01.01.2026 Учёным и волонтёрам проекта «Einstein@home» удалось обнаружить четыре гамма-лучевых пульсара в джете Млечного Пути Сочетание глобально распределённой вычислительной мощности и инновационных. . .	Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Programma_Boinc 28.12.2025 Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Налог на собак: https:/ / ********/ gallery/ V06K53e Финансовый отчет в Excel: https:/ / ********/ gallery/ bKBkQFf Пост отсюда. . .	Кто-нибудь знает, где можно бесплатно получить настольный компьютер или ноутбук? США. Programma_Boinc 26.12.2025 Нашел на реддите интересную статью под названием Anyone know where to get a free Desktop or Laptop? Ниже её машинный перевод. После долгих разбирательств я наконец-то вернула себе. . .	Thinkpad X220 Tablet — это лучший бюджетный ноутбук для учёбы, точка. Programma_Boinc 23.12.2025 Рецензия / Мнение/ Перевод Нашел на реддите интересную статью под названием The Thinkpad X220 Tablet is the best budget school laptop period . Ниже её машинный перевод. Thinkpad X220 Tablet —. . .
PhpStorm 2025.3: WSL Terminal всегда стартует в ~ and_y87 14.12.2025 PhpStorm 2025. 3: WSL Terminal всегда стартует в ~ (home), игнорируя директорию проекта Симптом: После обновления до PhpStorm 2025. 3 встроенный терминал WSL открывается в домашней директории. . .	Как объединить две одинаковые БД Access с разными данными VikBal 11.12.2025 Помогите пожалуйста !! Как объединить 2 одинаковые БД Access с разными данными.	Новый ноутбук volvo 07.12.2025 Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro	Музыка, написанная Искусственным Интеллектом volvo 04.12.2025 Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .	От async/await к виртуальным потокам в Python IndentationError 23.11.2025 Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .

@Nechto 2 / 2 / 2 Регистрация: 08.01.2010 Сообщений: 22
	01.03.2015, 16:48 [ТС]
	Люди добрые, неужели никто не поможет? 0

Sql группировка по условию, с присвоением порядкового номера

Решение