ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Прошу помощи с запросом
16 сообщений из 16, страница 1 из 1
Прошу помощи с запросом
    #39588711
execute
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
execute
Гость
Добрый день.
Не могу написать запрос.
Необходимо для каждого "n" получить список "Id" у которых поле "f" не равно друг другу
Код: 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.
27.
28.
29.
30.
31.
32.
33.
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
*/
...
Рейтинг: 0 / 0
22.01.2018, 22:02
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588751
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
execute,

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

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

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

как у вас по три штуки то в наборе получилось?
...
Рейтинг: 0 / 0
22.01.2018, 23:46
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588752
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
А если id текущий строки входит с результирующий список то почему для строки:
( 4 ,'name_2', 20 )

нет записи 'name_2', 1,2,3,5 ?
...
Рейтинг: 0 / 0
22.01.2018, 23:49
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588847
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
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 так странно сформировал выходные группы......
...
Рейтинг: 0 / 0
23.01.2018, 09:08
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588869
Massa52
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Massa52
Участник
Добрый Э - Эхfelix_ff,
А вот что действительно непонятно, так это почему автор для name_3 так странно сформировал выходные группы......
Может когда 2 или более NULL(поскольку невозможно сравнить), они все уникальные.
...
Рейтинг: 0 / 0
23.01.2018, 09:36
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588873
execute
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
execute
Гость
Добрый Э - Эх,
Да, постановку вы правильно поняли.

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

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

Честно говоря, пока даже не знаю с какой стороны к нему подступиться
...
Рейтинг: 0 / 0
23.01.2018, 09:40
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588874
execute
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
execute
Гость
Massa52,

да, именно так
...
Рейтинг: 0 / 0
23.01.2018, 09:41
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588902
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
executeЧестно говоря, пока даже не знаю с какой стороны к нему подступиться
Чисто мысли. Как можно было бы решить задачу.

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

Как преобразовать это в код запроса (процедуры?)... наверное, можно, но трудоёмко, и не факт что оптимально. Да и вообще в такой реализации это скорее задача для клиента, чем для сервера.
...
Рейтинг: 0 / 0
23.01.2018, 10:25
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588953
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
execute, это чисто Proof of Concept, предполагающий, что в f не будет отрицательных значений (иначе в dense_rank может случиться конфуз с сортировкой, как иначе обеспечить уникальность null-ов, я не придумал), в нем нужно заменить spt_values на корректную таблицу чисел, сделать динамику в джойнах, можно избавиться от unpivot/pivot для сортировки значений в наборах, ну и т.д. - это уже вам задача.

Код: 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.
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
...
Рейтинг: 0 / 0
23.01.2018, 11:22
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588961
RANK,
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RANK,
Гость
если поле результата не обязательно отсортированное, то все эти мои "приседания" с xml и cross apply лишнии, и всё решаеться рекурсивным запросом (поле path2)
Если нужно отсортированный, то так
Код: 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.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
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



npath2resultname_1<a>1</a><a>2</a><a>3</a><a>5</a>1,2,3,5name_1<a>1</a><a>2</a><a>3</a><a>6</a>1,2,3,6name_1<a>1</a><a>2</a><a>4</a><a>5</a>1,2,4,5name_1<a>1</a><a>2</a><a>4</a><a>6</a>1,2,4,6name_1<a>1</a><a>2</a><a>7</a><a>5</a>1,2,5,7name_1<a>1</a><a>2</a><a>7</a><a>6</a>1,2,6,7name_2<a>5</a><a>1</a><a>4</a>1,4,5name_2<a>5</a><a>2</a><a>4</a>2,4,5name_2<a>5</a><a>3</a><a>4</a>3,4,5name_3<a>2</a><a>1</a>1,2name_3<a>3</a><a>1</a>1,3name_3<a>2</a><a>4</a>2,4name_3<a>3</a><a>4</a>3,4
...
Рейтинг: 0 / 0
23.01.2018, 11:27
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588968
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
RANK,, вы с name_3 не так обращаетесь, как хочет автор - посмотрите комментарии выше.
...
Рейтинг: 0 / 0
23.01.2018, 11:30
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588971
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Minamoto,

счас ТС скажет, что могут быть ситуации, в которых количество уникальных значений в наборе - сильно больше, чем 4.
...
Рейтинг: 0 / 0
23.01.2018, 11:32
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588973
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
Добрый Э - ЭхMinamoto,

счас ТС скажет, что могут быть ситуации, в которых количество уникальных значений в наборе - сильно больше, чем 4.Так это ясно. Я ж написал, что нужно это место на динамику переписать )
...
Рейтинг: 0 / 0
23.01.2018, 11:32
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39588988
RANK,
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RANK,
Гость
MinamotoRANK,, вы с name_3 не так обращаетесь, как хочет автор - посмотрите комментарии выше.да, заметил

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

Код: 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.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
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


npath2resultname_1<a>1</a><a>2</a><a>3</a><a>5</a>1,2,3,5name_1<a>1</a><a>2</a><a>3</a><a>6</a>1,2,3,6name_1<a>1</a><a>2</a><a>4</a><a>5</a>1,2,4,5name_1<a>1</a><a>2</a><a>4</a><a>6</a>1,2,4,6name_1<a>1</a><a>2</a><a>7</a><a>5</a>1,2,5,7name_1<a>1</a><a>2</a><a>7</a><a>6</a>1,2,6,7name_2<a>5</a><a>1</a><a>4</a>1,4,5name_2<a>5</a><a>2</a><a>4</a>2,4,5name_2<a>5</a><a>3</a><a>4</a>3,4,5name_3<a>3</a><a>2</a><a>1</a>1,2,3name_3<a>3</a><a>2</a><a>4</a>2,3,4
...
Рейтинг: 0 / 0
23.01.2018, 11:46
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39589043
RANK,
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RANK,
Гость
вот так, имхо, будет "совсем правильно"

Код: 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.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
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


npath2resultname_1<a>1</a><a>2</a><a>3</a><a>5</a>1,2,3,5name_1<a>1</a><a>2</a><a>3</a><a>6</a>1,2,3,6name_1<a>1</a><a>2</a><a>4</a><a>5</a>1,2,4,5name_1<a>1</a><a>2</a><a>4</a><a>6</a>1,2,4,6name_1<a>1</a><a>2</a><a>7</a><a>5</a>1,2,5,7name_1<a>1</a><a>2</a><a>7</a><a>6</a>1,2,6,7name_2<a>5</a><a>1</a><a>4</a>1,4,5name_2<a>5</a><a>2</a><a>4</a>2,4,5name_2<a>5</a><a>3</a><a>4</a>3,4,5name_3<a>3</a><a>2</a><a>1</a><a>4</a>1,2,3,4
...
Рейтинг: 0 / 0
23.01.2018, 13:06
| Ответить | Цитировать | Написать  
Прошу помощи с запросом
    #39589582
execute
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
execute
Гость
RANK,

Похоже, то что нужно...
Не смог я рекурсивный CTE до конца составить сам.
Спасибо!
...
Рейтинг: 0 / 0
23.01.2018, 22:42
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Прошу помощи с запросом
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]