Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как получить конкатенацию значений по группе внутри большого соединения таблиц  [new]
dwp
Member

Откуда: г. Барнаул
Сообщений: 16
есть (#tmp2 tmp3 огромны, секционированы по датам, есть индексы)

+
create table #tmp1(
  zone char(50),
  id int
)
-- truncate table  #tmp1
 insert into #tmp1
 select 'zone1', 1
  union select 'zone1', 2
  union select 'zone1', 3
  union select 'zone1', 4
  union select 'zone2', 5
  union select 'zone2', 6
  union select 'zone2', 7
  union select 'zone3', 8
  union select 'zone3', 9
  union select 'zone3', 10
  union select 'zone4', 11
  union select 'zone4', 12
create table #tmp2(
  id   int,
  dat  smalldatetime,
  id2  int
)

create table #tmp3(
  id   int,
  dat  smalldatetime,
  id2  int
)

insert into #tmp2
        select 1 , '20181001', 10
  union select 1 , '20181001', 11
  union select 1 , '20181001', 12
  union select 1 , '20181001', 13
  union select 1 , '20181001', 14
  union select 1 , '20181001', 15
  union select 1 , '20181001', 16
  union select 2 , '20181001', 10
  union select 2 , '20181001', 11
  union select 2 , '20181001', 12
  union select 2 , '20181001', 13
  union select 2 , '20181001', 14
  union select 2 , '20181001', 15
  union select 2 , '20181001', 16 
  union select 3 , '20181001', 10
  union select 3 , '20181001', 11
  union select 3 , '20181001', 12
  union select 4 , '20181001', 13
  union select 4 , '20181001', 14
  union select 4 , '20181001', 15
  union select 4 , '20181001', 16 
  union select 4 , '20181001', 15
  union select 4 , '20181001', 16 
  union select 5 , '20181001', 15
  union select 5 , '20181001', 16  
  union select 6 , '20181001', 15
  union select 6 , '20181001', 16  
  union select 7 , '20181001', 12
  union select 7 , '20181001', 16  
  union select 8 , '20181001', 15
  union select 8 , '20181001', 16  
  union select 9 , '20181001', 15
  union select 9 , '20181001', 16  
  union select 10 , '20181001', 15
  union select 11 , '20181001', 16  

  union select 1 , '20181002', 11
  union select 1 , '20181002', 12
  union select 1 , '20181002', 13
  union select 1 , '20181002', 14
  union select 1 , '20181002', 15
  union select 1 , '20181002', 16
  union select 2 , '20181002', 10
  union select 2 , '20181002', 11
  union select 2 , '20181002', 12
  union select 2 , '20181002', 13
  union select 2 , '20181002', 14
  union select 2 , '20181002', 15
  union select 2 , '20181002', 16  
  union select 3 , '20181002', 10
  union select 3 , '20181002', 11
  union select 3 , '20181002', 12
  union select 4 , '20181002', 13
  union select 4 , '20181002', 14
  union select 4 , '20181002', 15
  union select 4 , '20181002', 16  
  union select 4 , '20181002', 15
  union select 4 , '20181002', 16 
  union select 5 , '20181002', 15
  union select 5 , '20181002', 16  
  union select 6 , '20181002', 15
  union select 6 , '20181002', 16  
  union select 7 , '20181002', 12
  union select 7 , '20181002', 16  
  union select 8 , '20181002', 15
  union select 8 , '20181002', 16  
  union select 9 , '20181002', 15
  union select 9 , '20181002', 16  
  union select 10, '20181002', 15
  union select 11, '20181002', 16  

  union select 1 , '20181003', 13
  union select 1 , '20181003', 14
  union select 1 , '20181003', 15
  union select 1 , '20181003', 16
  union select 2 , '20181003', 11
  union select 2 , '20181003', 12
  union select 2 , '20181003', 13
  union select 2 , '20181003', 14
  union select 2 , '20181003', 16  
  union select 3 , '20181003', 11
  union select 3 , '20181003', 12
  union select 4 , '20181003', 14
  union select 4 , '20181003', 15
  union select 4 , '20181003', 16  
  union select 4 , '20181003', 16 
  union select 5 , '20181003', 15
  union select 6 , '20181003', 15
  union select 7 , '20181003', 12
  union select 8 , '20181003', 15
  union select 9 , '20181003', 15

    ---------------------------------------------------
  insert into #tmp3
        select 1 , '20181001', 10
  union select 1 , '20181001', 13
  union select 1 , '20181001', 14
  union select 1 , '20181001', 15
  union select 1 , '20181001', 16
  union select 2 , '20181001', 12
  union select 2 , '20181001', 13
  union select 2 , '20181001', 14
  union select 2 , '20181001', 15
  union select 2 , '20181001', 16 
  union select 3 , '20181001', 12
  union select 4 , '20181001', 13
  union select 4 , '20181001', 14
  union select 4 , '20181001', 16 
  union select 5 , '20181001', 16  
  union select 6 , '20181001', 15
  union select 7 , '20181001', 12
  union select 7 , '20181001', 16  
  union select 8 , '20181001', 16  
  union select 9 , '20181001', 15
  union select 10 , '20181001', 15
  union select 11 , '20181001', 16  

  union select 1 , '20181002', 11
  union select 1 , '20181002', 12
  union select 1 , '20181002', 13
  union select 1 , '20181002', 16
  union select 2 , '20181002', 10
  union select 2 , '20181002', 11
  union select 2 , '20181002', 12
  union select 2 , '20181002', 13
  union select 2 , '20181002', 14
  union select 2 , '20181002', 15
  union select 2 , '20181002', 16  
  union select 3 , '20181002', 10
  union select 3 , '20181002', 11
  union select 3 , '20181002', 12
  union select 4 , '20181002', 13
  union select 4 , '20181002', 14
  union select 4 , '20181002', 15
  union select 4 , '20181002', 16  
  union select 4 , '20181002', 15
  union select 4 , '20181002', 16 
  union select 5 , '20181002', 15
  union select 6 , '20181002', 15
  union select 6 , '20181002', 16  
  union select 7 , '20181002', 12
  union select 7 , '20181002', 16  
  union select 8 , '20181002', 16  
  union select 9 , '20181002', 15
  union select 9 , '20181002', 16  
  union select 10, '20181002', 15
  union select 11, '20181002', 16  

  union select 1 , '20181003', 13
  union select 1 , '20181003', 14
  union select 1 , '20181003', 15
  union select 1 , '20181003', 16
  union select 2 , '20181003', 11
  union select 2 , '20181003', 12
  union select 2 , '20181003', 13
  union select 2 , '20181003', 14
  union select 2 , '20181003', 16  
  union select 3 , '20181003', 11
  union select 3 , '20181003', 12
  union select 4 , '20181003', 14
  union select 4 , '20181003', 15
  union select 4 , '20181003', 16  
  union select 4 , '20181003', 16 
  union select 5 , '20181003', 15
  union select 6 , '20181003', 15
  union select 7 , '20181003', 12
  union select 8 , '20181003', 15
  union select 9 , '20181003', 15
insert into #tmp2
        select 1 , '20181001', 10
  union select 1 , '20181001', 11
  union select 1 , '20181001', 12
  union select 1 , '20181001', 13
  union select 1 , '20181001', 14
  union select 1 , '20181001', 15
  union select 1 , '20181001', 16
  union select 2 , '20181001', 10
  union select 2 , '20181001', 11
  union select 2 , '20181001', 12
  union select 2 , '20181001', 13
  union select 2 , '20181001', 14
  union select 2 , '20181001', 15
  union select 2 , '20181001', 16 
  union select 3 , '20181001', 10
  union select 3 , '20181001', 11
  union select 3 , '20181001', 12
  union select 4 , '20181001', 13
  union select 4 , '20181001', 14
  union select 4 , '20181001', 15
  union select 4 , '20181001', 16 
  union select 4 , '20181001', 15
  union select 4 , '20181001', 16 
  union select 5 , '20181001', 15
  union select 5 , '20181001', 16  
  union select 6 , '20181001', 15
  union select 6 , '20181001', 16  
  union select 7 , '20181001', 12
  union select 7 , '20181001', 16  
  union select 8 , '20181001', 15
  union select 8 , '20181001', 16  
  union select 9 , '20181001', 15
  union select 9 , '20181001', 16  
  union select 10 , '20181001', 15
  union select 11 , '20181001', 16  

  union select 1 , '20181002', 11
  union select 1 , '20181002', 12
  union select 1 , '20181002', 13
  union select 1 , '20181002', 14
  union select 1 , '20181002', 15
  union select 1 , '20181002', 16
  union select 2 , '20181002', 10
  union select 2 , '20181002', 11
  union select 2 , '20181002', 12
  union select 2 , '20181002', 13
  union select 2 , '20181002', 14
  union select 2 , '20181002', 15
  union select 2 , '20181002', 16  
  union select 3 , '20181002', 10
  union select 3 , '20181002', 11
  union select 3 , '20181002', 12
  union select 4 , '20181002', 13
  union select 4 , '20181002', 14
  union select 4 , '20181002', 15
  union select 4 , '20181002', 16  
  union select 4 , '20181002', 15
  union select 4 , '20181002', 16 
  union select 5 , '20181002', 15
  union select 5 , '20181002', 16  
  union select 6 , '20181002', 15
  union select 6 , '20181002', 16  
  union select 7 , '20181002', 12
  union select 7 , '20181002', 16  
  union select 8 , '20181002', 15
  union select 8 , '20181002', 16  
  union select 9 , '20181002', 15
  union select 9 , '20181002', 16  
  union select 10, '20181002', 15
  union select 11, '20181002', 16  

  union select 1 , '20181003', 13
  union select 1 , '20181003', 14
  union select 1 , '20181003', 15
  union select 1 , '20181003', 16
  union select 2 , '20181003', 11
  union select 2 , '20181003', 12
  union select 2 , '20181003', 13
  union select 2 , '20181003', 14
  union select 2 , '20181003', 16  
  union select 3 , '20181003', 11
  union select 3 , '20181003', 12
  union select 4 , '20181003', 14
  union select 4 , '20181003', 15
  union select 4 , '20181003', 16  
  union select 4 , '20181003', 16 
  union select 5 , '20181003', 15
  union select 6 , '20181003', 15
  union select 7 , '20181003', 12
  union select 8 , '20181003', 15
  union select 9 , '20181003', 15
insert into #tmp3
        select 1 , '20181001', 10
  union select 1 , '20181001', 11
  union select 1 , '20181001', 12
  union select 1 , '20181001', 13
  union select 1 , '20181001', 14
  union select 1 , '20181001', 15
  union select 1 , '20181001', 16
  union select 2 , '20181001', 10
  union select 2 , '20181001', 11
  union select 2 , '20181001', 12
  union select 2 , '20181001', 13
  union select 2 , '20181001', 14
  union select 2 , '20181001', 15
  union select 2 , '20181001', 16 
  union select 3 , '20181001', 10
  union select 3 , '20181001', 11
  union select 3 , '20181001', 12
  union select 4 , '20181001', 13
  union select 4 , '20181001', 14
  union select 4 , '20181001', 15
  union select 4 , '20181001', 16 
  union select 4 , '20181001', 15
  union select 4 , '20181001', 16 
  union select 5 , '20181001', 15
  union select 5 , '20181001', 16  
  union select 6 , '20181001', 15
  union select 6 , '20181001', 16  
  union select 7 , '20181001', 12
  union select 7 , '20181001', 16  
  union select 8 , '20181001', 15
  union select 8 , '20181001', 16  
  union select 9 , '20181001', 15
  union select 9 , '20181001', 16  
  union select 10 , '20181001', 15
  union select 11 , '20181001', 16  

  union select 1 , '20181002', 11
  union select 1 , '20181002', 12
  union select 1 , '20181002', 13
  union select 1 , '20181002', 14
  union select 1 , '20181002', 15
  union select 1 , '20181002', 16
  union select 2 , '20181002', 10
  union select 2 , '20181002', 11
  union select 2 , '20181002', 12
  union select 2 , '20181002', 13
  union select 2 , '20181002', 14
  union select 2 , '20181002', 15
  union select 2 , '20181002', 16  
  union select 3 , '20181002', 10
  union select 3 , '20181002', 11
  union select 3 , '20181002', 12
  union select 4 , '20181002', 13
  union select 4 , '20181002', 14
  union select 4 , '20181002', 15
  union select 4 , '20181002', 16  
  union select 4 , '20181002', 15
  union select 4 , '20181002', 16 
  union select 5 , '20181002', 15
  union select 5 , '20181002', 16  
  union select 6 , '20181002', 15
  union select 6 , '20181002', 16  
  union select 7 , '20181002', 12
  union select 7 , '20181002', 16  
  union select 8 , '20181002', 15
  union select 8 , '20181002', 16  
  union select 9 , '20181002', 15
  union select 9 , '20181002', 16  
  union select 10, '20181002', 15
  union select 11, '20181002', 16  

  union select 1 , '20181003', 13
  union select 1 , '20181003', 14
  union select 1 , '20181003', 15
  union select 1 , '20181003', 16
  union select 2 , '20181003', 11
  union select 2 , '20181003', 12
  union select 2 , '20181003', 13
  union select 2 , '20181003', 14
  union select 2 , '20181003', 16  
  union select 3 , '20181003', 11
  union select 3 , '20181003', 12
  union select 4 , '20181003', 14
  union select 4 , '20181003', 15
  union select 4 , '20181003', 16  
  union select 4 , '20181003', 16 
  union select 5 , '20181003', 15
  union select 6 , '20181003', 15
  union select 7 , '20181003', 12
  union select 8 , '20181003', 15
  union select 9 , '20181003', 15
  union select 10 , '20181001', 15
  union select 11 , '20181001', 16  
select s.Zone, mho.id2 , count(distinct mho.id) com , count(mho.dat) com2, STUFF((SELECT ',' + rtrim(cast(ss.id as char(4)))
                                                  from #tmp1 ss where ss.zone = s.zone --and ss.id = mho.id
                                          FOR XML PATH('')), 1, 1, '') as ss
 from #tmp1 s
 inner join #tmp2 mho on mho.id = s.Id 
 inner join #tmp2 reo on reo.Id = s.id and reo.id2 = mho.id2 
/*CROSS APPLY (select STUFF((SELECT ',' + rtrim(cast(b.id as char(4)))
                                              --from ds ss where ss.zone = b.zone --and ss.id = mho.id
                                          FOR XML PATH('')), 1, 1, '') as ss
               from (select a.zonename, a.idshop
                       from ds a where  a.zone = s.zone and a.id = mho.id) b
                           group by b.zone) la*/
group by s.Zone, mho.id2
--having zone = 'zone1'
--order by zone


это неверный результат, в списке должны быть только значения(id) засветившиеся в соединении в группе.
zone1 10 3 81 1,2,3,4
zone1 11 3 198 1,2,3,4
zone1 12 3 198 1,2,3,4
zone1 13 3 198 1,2,3,4
zone1 14 3 243 1,2,3,4
zone1 15 3 198 1,2,3,4
zone1 16 3 243 1,2,3,4
zone2 12 1 81 5,6,7
zone2 15 2 162 5,6,7
zone2 16 3 108 5,6,7
zone3 15 3 198 8,9,10
zone3 16 2 72 8,9,10
zone4 16 1 36 11,12
верный результат не буду приводит полный
в первой строке должно быть
zone1 10 3 81 1,2,3
по zone1 для id=4 нет id2=10 в группе

смысл сводится к тому что за год их вовсе не соединить. соединяется по месяцам.
но нужно получить активные за год по по всему соединению складывая кусочки по месяцам.
значение не аддитивное не складывается из кусочков за месяц( в одном периоде одни засветились в другом другие)
что то изменять выставляя где то признак, что такой id засветился нельзя из за ограничений DML в функциях.
оставалось использовать строку всех попавших значений, и обработать кусочки при склейке.
НО
либо я не могу включить внутрь конкатенации id, так как FOR XML не является агрегатом и не "прячет" нужный параметр(и получаю просто полный список а не список из подгруппы)
либо я вынужден подать на вход FOR XML, полное соединение трех таблиц, но тогда это всё не вывозится
или делать промежуточный результат , что тоже не подходит из за громоздкости

подскажите решение, пожалуйста. В оракле бы такое влет решил, а тут пока не получается

Модератор: Убирайте, пожалуйста, портянки в тег spoiler. Спасибо.


Сообщение было отредактировано: 9 окт 18, 12:40
9 окт 18, 12:37    [21699153]     Ответить | Цитировать Сообщить модератору
 Re: Как получить конкатенацию значений по группе внутри большого соединения таблиц  [new]
dwp
Member

Откуда: г. Барнаул
Сообщений: 16
select s.Zone, mho.id2 , count(distinct mho.id) com , count(mho.dat) com2, STUFF((SELECT ',' + rtrim(cast(ss.id as char(4)))
                                                  from #tmp1 ss where ss.zone = s.zone --and ss.id = mho.id
                                          FOR XML PATH('')), 1, 1, '') as ss
 from #tmp1 s
 inner join #tmp2 mho on mho.id = s.Id 
 inner join [b]#tmp3[/b] reo on reo.Id = s.id and reo.id2 = mho.id2 
/*CROSS APPLY (select STUFF((SELECT ',' + rtrim(cast(b.id as char(4)))
                                              --from ds ss where ss.zone = b.zone --and ss.id = mho.id
                                          FOR XML PATH('')), 1, 1, '') as ss
               from (select a.zonename, a.idshop
                       from ds a where  a.zone = s.zone and a.id = mho.id) b
                           group by b.zone) la*/
group by s.Zone, mho.id2
--having zone = 'zone1'
order by zone

исправил опечатку
9 окт 18, 12:40    [21699155]     Ответить | Цитировать Сообщить модератору
 Re: Как получить конкатенацию значений по группе внутри большого соединения таблиц  [new]
dwp
Member

Откуда: г. Барнаул
Сообщений: 16
еще подправил пропущенное отношение

select s.Zone, mho.id2 , count(distinct mho.id) com , count(mho.dat) com2, STUFF((SELECT ',' + rtrim(cast(ss.id as char(4)))
                                                  from #tmp1 ss where ss.zone = s.zone --and ss.id = mho.id
                                          FOR XML PATH('')), 1, 1, '') as ss
 from #tmp1 s
 inner join #tmp2 mho on mho.id = s.Id 
 inner join #tmp3 reo on reo.Id = s.id and reo.id2 = mho.id2 and reo.dat = mho.dat
group by s.Zone, mho.id2dwp,
9 окт 18, 12:59    [21699175]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить