Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Прошу помощи с запросом / 16 сообщений из 16, страница 1 из 1
22.01.2018, 22:02
    #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, 23:46
    #39588751
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи с запросом
execute,

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

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

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

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

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

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

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

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

да, именно так
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.01.2018, 10:25
    #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, 11:22
    #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:27
    #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:30
    #39588968
Minamoto
Minamoto
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи с запросом
RANK,, вы с name_3 не так обращаетесь, как хочет автор - посмотрите комментарии выше.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.01.2018, 11:32
    #39588971
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи с запросом
Minamoto,

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

счас ТС скажет, что могут быть ситуации, в которых количество уникальных значений в наборе - сильно больше, чем 4.Так это ясно. Я ж написал, что нужно это место на динамику переписать )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.01.2018, 11:46
    #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, 13:06
    #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, 22:42
    #39589582
execute
execute
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи с запросом
RANK,

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


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