Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Прошу помощи с запросом  [new]
execute
Guest
Добрый день.
Не могу написать запрос.
Необходимо для каждого "n" получить список "Id" у которых поле "f" не равно друг другу
declare @t table ( id int, n sysname, f int );
insert into @t values 
  ( 1	,'name_1', 10 )
, ( 2	,'name_1', 20 )
, ( 3	,'name_1', 30 )
, ( 4	,'name_1', 30 )
, ( 5	,'name_1', 40 )
, ( 6	,'name_1', 40 )
, ( 7	,'name_1', 30 )
, ( 1	,'name_2', 10 )
, ( 2	,'name_2', 10 )
, ( 3	,'name_2', 10 )
, ( 4	,'name_2', 20 )
, ( 5	,'name_2', NULL )
, ( 1	,'name_3', 10 )
, ( 2	,'name_3', NULL )
, ( 3	,'name_3', NULL )
, ( 4	,'name_3', 10 )
select * from @t

/* Правильный рекордсет
name_1    1,2,3,5
name_1    1,2,3,6
name_1    1,2,4,5
name_1    1,2,4,6
name_1    1,2,5,7
name_1    1,2,6,7
name_2    1,4,5
name_2    2,4,5
name_2    3,4,5
name_3    1,2,3
name_3    2,3,4
*/
22 янв 18, 22:02    [21128368]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1367
execute,

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

но к примеру возьмем запись 2, 'name_2', 10

для нее список ид у которых f не равно 10 будет id: 4, 5.

как у вас по три штуки то в наборе получилось?
22 янв 18, 23:46    [21128553]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1367
А если id текущий строки входит с результирующий список то почему для строки:
( 4 ,'name_2', 20 )

нет записи 'name_2', 1,2,3,5 ?
22 янв 18, 23:49    [21128557]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
Добрый Э - Эх
Guest
felix_ff,

тут-то как раз все понятно: автору требуются все возможные полные сочетания (без перестановок) УНИКАЛЬНЫХ значений поля f в пределах группы, заданной полем n. Под "полным сочетание" тут подразумевается, что в итоговом рекодрсете длина элементов сочетания должна быть равной числу уникальных значений в f по n. Так как одно и тоже значение f может быть представлено несколькими значениями ID, возникают различные варианты сочетаний. Но все эти варианты сочетаний ID должны дать изначальную полную отсортированную последовательность уникальных f.

Для name_1 в поле f есть 4 уникальных значения - 10, 20, 30, 40. Поэтому на выходе получаются "склейки" из 4-х значений.


Для name_2 уникальных значений в поле f - всего три - 10, 20, NULL. Поэтому склейки будут по три значения.


А вот что действительно непонятно, так это почему автор для name_3 так странно сформировал выходные группы......
23 янв 18, 09:08    [21128982]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
Massa52
Member

Откуда:
Сообщений: 379
Добрый Э - Эх
felix_ff,
А вот что действительно непонятно, так это почему автор для name_3 так странно сформировал выходные группы......

Может когда 2 или более NULL(поскольку невозможно сравнить), они все уникальные.
23 янв 18, 09:36    [21129064]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
execute
Guest
Добрый Э - Эх,
Да, постановку вы правильно поняли.

Ну NULL же неизвестное значение.
Грубо говоря, два разных NULL являются уникальными значениями.

Запрос тестовый, упрощен из требований корявой бизнес логики.

Честно говоря, пока даже не знаю с какой стороны к нему подступиться
23 янв 18, 09:40    [21129075]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
execute
Guest
Massa52,

да, именно так
23 янв 18, 09:41    [21129078]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20533
execute
Честно говоря, пока даже не знаю с какой стороны к нему подступиться

Чисто мысли. Как можно было бы решить задачу.

Представь, что в каждой группе по n мы пронумеровали записи (тупо ROW_NUMBER() OVER (PARTITION BY n)) - как-то, порядок пока не важен.
Далее начинаем рекурсивный CTE. К каждой записи первичного подзапроса добавляем поле (а хоть бы и CSV), где будем в некоей форме накапливать создаваемую для этой записи группу (начально там будет только номер начальной записи, а в ходе CTE туда будут добавляться следующие записи). Соответственно в рекурсивном подзапросе присоединяем к такой записи запись из основной таблицы по условиям: 1) равенство n; 2) неравенство f ни одному из уже находящихся в накопленной последовательности записей; 3) номер присоединяемой записи больше максимального номера уже накопленной последовательности. Последнее условие гарантирует прекращение рекурсии. Второе - корректно считает все NULL уникальными.
По завершении рекурсии остаётся отобрать из полученной кучи записи с максимальной длиной построенного CSV-поля (для чего номера записей в группе при добавлении в CSV-список придётся форматировать с ведущими нулями). Оставшиеся CSV будут содержать требуемые итоговые наборы.

Как преобразовать это в код запроса (процедуры?)... наверное, можно, но трудоёмко, и не факт что оптимально. Да и вообще в такой реализации это скорее задача для клиента, чем для сервера.
23 янв 18, 10:25    [21129270]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
execute, это чисто Proof of Concept, предполагающий, что в f не будет отрицательных значений (иначе в dense_rank может случиться конфуз с сортировкой, как иначе обеспечить уникальность null-ов, я не придумал), в нем нужно заменить spt_values на корректную таблицу чисел, сделать динамику в джойнах, можно избавиться от unpivot/pivot для сортировки значений в наборах, ну и т.д. - это уже вам задача.

with dr as (
select  id, n, f, dense_rank() over (partition by n order by case when f is null then -id else f end) as dr
from    @t
), numbers as (
    select  t.n, sv.number
    from    (select  n, max(dr) cnt from dr group by n) as t
            inner join master..spt_values as sv on sv.type = 'p' and sv.number >= 1 and sv.number <= t.cnt
), blocks as (
select distinct numbers.n, dr1.id id1, dr2.id id2, dr3.id id3, dr4.id id4
from numbers
        left join dr dr1 on dr1.n = numbers.n and dr1.dr = 1
        left join dr dr2 on dr2.n = numbers.n and dr2.dr = 2
        left join dr dr3 on dr3.n = numbers.n and dr3.dr = 3
        left join dr dr4 on dr4.n = numbers.n and dr4.dr = 4
), blocks_to_sort as (
select n, rn, id, col
from   (select blocks.n
             , row_number() over (order by n) as rn
             , blocks.id1
             , blocks.id2
             , blocks.id3
             , blocks.id4 from blocks) p
unpivot (id for col in (id1, id2, id3, id4)) as unpvt
)
select  n, stuff((select N',' + cast(id as nvarchar(max)) from blocks_to_sort bs1 where bs.n = bs1.n and bs.rn = bs1.rn order by id for xml path ('')), 1, 1, '') as rs
from    (select distinct n, rn from blocks_to_sort) bs
23 янв 18, 11:22    [21129573]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
RANK,
Guest
если поле результата не обязательно отсортированное, то все эти мои "приседания" с xml и cross apply лишнии, и всё решаеться рекурсивным запросом (поле path2)
Если нужно отсортированный, то так
declare @t table ( id int, n sysname, f int );
insert into @t values 
  ( 1	,'name_1', 10 )
, ( 2	,'name_1', 20 )
, ( 3	,'name_1', 30 )
, ( 4	,'name_1', 30 )
, ( 5	,'name_1', 40 )
, ( 6	,'name_1', 40 )
, ( 7	,'name_1', 30 )
, ( 1	,'name_2', 10 )
, ( 2	,'name_2', 10 )
, ( 3	,'name_2', 10 )
, ( 4	,'name_2', 20 )
, ( 5	,'name_2', NULL )
, ( 1	,'name_3', 10 )
, ( 2	,'name_3', NULL )
, ( 3	,'name_3', NULL )
, ( 4	,'name_3', 10 )

;with t as
	(select *, DENSE_RANK()over(partition by n order by f) as rn from @t),
cte as (
	select top 1 with ties *, f as last_f, cast('<a>'+cast(id as varchar)+'</a>' as varchar(max)) as path2, 1 as level
	from t 
	order by rn

	union all

	select t.*, t.f as last_f, cte.path2+'<a>'+cast(t.id as varchar)+'</a>' as path2, cte.level+1
	from t inner join cte on t.n=cte.n and t.rn=cte.rn+1  

)
select n, path2, replace(c.res,' ', ',') as result 
from cte c1

cross apply (select cast(path2 as xml) as p) a
cross apply (select res=(select cast(b.val as varchar) as 'data()' from (select t.c.value('text()[1]','int') as val from a.p.nodes('a') as t(c)) b order by b.val for xml path(''))) c 

where level = (select max(c2.level) from cte c2 where c1.n=c2.n)
order by 1,3


npath2result
name_1<a>1</a><a>2</a><a>3</a><a>5</a>1,2,3,5
name_1<a>1</a><a>2</a><a>3</a><a>6</a>1,2,3,6
name_1<a>1</a><a>2</a><a>4</a><a>5</a>1,2,4,5
name_1<a>1</a><a>2</a><a>4</a><a>6</a>1,2,4,6
name_1<a>1</a><a>2</a><a>7</a><a>5</a>1,2,5,7
name_1<a>1</a><a>2</a><a>7</a><a>6</a>1,2,6,7
name_2<a>5</a><a>1</a><a>4</a>1,4,5
name_2<a>5</a><a>2</a><a>4</a>2,4,5
name_2<a>5</a><a>3</a><a>4</a>3,4,5
name_3<a>2</a><a>1</a>1,2
name_3<a>3</a><a>1</a>1,3
name_3<a>2</a><a>4</a>2,4
name_3<a>3</a><a>4</a>3,4
23 янв 18, 11:27    [21129593]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
RANK,, вы с name_3 не так обращаетесь, как хочет автор - посмотрите комментарии выше.
23 янв 18, 11:30    [21129614]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
Добрый Э - Эх
Guest
Minamoto,

счас ТС скажет, что могут быть ситуации, в которых количество уникальных значений в наборе - сильно больше, чем 4.
23 янв 18, 11:32    [21129625]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Добрый Э - Эх
Minamoto,

счас ТС скажет, что могут быть ситуации, в которых количество уникальных значений в наборе - сильно больше, чем 4.
Так это ясно. Я ж написал, что нужно это место на динамику переписать )
23 янв 18, 11:32    [21129629]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
RANK,
Guest
Minamoto
RANK,, вы с name_3 не так обращаетесь, как хочет автор - посмотрите комментарии выше.
да, заметил

Нуу вот на такой "кривой козе" можно это "объехать" :)
Хотя конечно уже несовсем то ...

declare @t table ( id int, n sysname, f int );
insert into @t values 
  ( 1	,'name_1', 10 )
, ( 2	,'name_1', 20 )
, ( 3	,'name_1', 30 )
, ( 4	,'name_1', 30 )
, ( 5	,'name_1', 40 )
, ( 6	,'name_1', 40 )
, ( 7	,'name_1', 30 )
, ( 1	,'name_2', 10 )
, ( 2	,'name_2', 10 )
, ( 3	,'name_2', 10 )
, ( 4	,'name_2', 20 )
, ( 5	,'name_2', NULL )
, ( 1	,'name_3', 10 )
, ( 2	,'name_3', NULL )
, ( 3	,'name_3', NULL )
, ( 4	,'name_3', 10 )

;with t as
	(select *, DENSE_RANK()over(partition by n order by isnull(f,-id)) as rn from @t),
cte as (
	select top 1 with ties *, f as last_f, cast('<a>'+cast(id as varchar)+'</a>' as varchar(max)) as path2, 1 as level
	from t 
	order by rn

	union all

	select t.*, t.f as last_f, cte.path2+'<a>'+cast(t.id as varchar)+'</a>' as path2, cte.level+1
	from t inner join cte on t.n=cte.n and t.rn=cte.rn+1  

)

select n, path2, replace(c.res,' ', ',') as result 
from cte c1

cross apply (select cast(path2 as xml) as p) a
cross apply (select res=(select cast(b.val as varchar) as 'data()' from (select t.c.value('text()[1]','int') as val from a.p.nodes('a') as t(c)) b order by b.val for xml path(''))) c 

where level = (select max(c2.level) from cte c2 where c1.n=c2.n)
order by 1,3

npath2result
name_1<a>1</a><a>2</a><a>3</a><a>5</a>1,2,3,5
name_1<a>1</a><a>2</a><a>3</a><a>6</a>1,2,3,6
name_1<a>1</a><a>2</a><a>4</a><a>5</a>1,2,4,5
name_1<a>1</a><a>2</a><a>4</a><a>6</a>1,2,4,6
name_1<a>1</a><a>2</a><a>7</a><a>5</a>1,2,5,7
name_1<a>1</a><a>2</a><a>7</a><a>6</a>1,2,6,7
name_2<a>5</a><a>1</a><a>4</a>1,4,5
name_2<a>5</a><a>2</a><a>4</a>2,4,5
name_2<a>5</a><a>3</a><a>4</a>3,4,5
name_3<a>3</a><a>2</a><a>1</a>1,2,3
name_3<a>3</a><a>2</a><a>4</a>2,3,4
23 янв 18, 11:46    [21129715]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
RANK,
Guest
вот так, имхо, будет "совсем правильно"

declare @t table ( id int, n sysname, f int );
insert into @t values 
  ( 1	,'name_1', 10 )
, ( 2	,'name_1', 20 )
, ( 3	,'name_1', 30 )
, ( 4	,'name_1', 30 )
, ( 5	,'name_1', 40 )
, ( 6	,'name_1', 40 )
, ( 7	,'name_1', 30 )
, ( 1	,'name_2', 10 )
, ( 2	,'name_2', 10 )
, ( 3	,'name_2', 10 )
, ( 4	,'name_2', 20 )
, ( 5	,'name_2', NULL )
, ( 1	,'name_3', -2 )
, ( 2	,'name_3', NULL )
, ( 3	,'name_3', NULL )
, ( 4	,'name_3', 10 )

;with t as
	(select *, DENSE_RANK()over(partition by n order by case when f is null then -id else 0 end, f) as rn from @t),
cte as (
	select top 1 with ties *, f as last_f, cast('<a>'+cast(id as varchar)+'</a>' as varchar(max)) as path2, 1 as level
	from t 
	order by rn

	union all

	select t.*, t.f as last_f, cte.path2+'<a>'+cast(t.id as varchar)+'</a>' as path2, cte.level+1
	from t inner join cte on t.n=cte.n and t.rn=cte.rn+1  

)
select n, path2, replace(c.res,' ', ',') as result 
from cte c1

cross apply (select cast(path2 as xml) as p) a
cross apply (select res=(select cast(b.val as varchar) as 'data()' from (select t.c.value('text()[1]','int') as val from a.p.nodes('a') as t(c)) b order by b.val for xml path(''))) c 

where level = (select max(c2.level) from cte c2 where c1.n=c2.n)
order by 1,3

npath2result
name_1<a>1</a><a>2</a><a>3</a><a>5</a>1,2,3,5
name_1<a>1</a><a>2</a><a>3</a><a>6</a>1,2,3,6
name_1<a>1</a><a>2</a><a>4</a><a>5</a>1,2,4,5
name_1<a>1</a><a>2</a><a>4</a><a>6</a>1,2,4,6
name_1<a>1</a><a>2</a><a>7</a><a>5</a>1,2,5,7
name_1<a>1</a><a>2</a><a>7</a><a>6</a>1,2,6,7
name_2<a>5</a><a>1</a><a>4</a>1,4,5
name_2<a>5</a><a>2</a><a>4</a>2,4,5
name_2<a>5</a><a>3</a><a>4</a>3,4,5
name_3<a>3</a><a>2</a><a>1</a><a>4</a>1,2,3,4
23 янв 18, 13:06    [21130239]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с запросом  [new]
execute
Guest
RANK,

Похоже, то что нужно...
Не смог я рекурсивный CTE до конца составить сам.
Спасибо!
23 янв 18, 22:42    [21132363]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить