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.

Новые блоги и статьи Все статьи Все блоги /
Символьное дифференцирование igorrr37 13.02.2026 / * Программа принимает математическое выражение в виде строки и выдаёт его производную в виде строки и вычисляет значение производной при заданном х Логарифм записывается как: (x-2)log(x^2+2) -. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .	И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»
SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL3_image 8Observer8 10.02.2026 Содержание блога Библиотека SDL3_image содержит инструменты для расширенной работы с изображениями. Пошагово создадим проект для загрузки изображения формата PNG с альфа-каналом (с прозрачным. . .	Установка Qt-версии Lazarus IDE в Debian Trixie Xfce volvo 10.02.2026 В общем, достали меня глюки IDE Лазаруса, собранной с использованием набора виджетов Gtk2 (конкретно: если набирать текст в редакторе и вызвать подсказку через Ctrl+Space, то после закрытия окошка. . .

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

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

Решение