есть (#tmp2 tmp3 огромны, секционированы по датам, есть индексы)
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.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
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. Спасибо. |
