Вобщем промучался я с отчетной формой почти месяц. В итоге ничего другого как временные таблицы не пришло в голову. Хотелось решить задачу исключительно MYSQL, не привлекая программную часть (переборы). Получилась вот такая серия запросов. Я почти уверен, что это можно сделать и без временных таблиц с кучей вложенных select-ов, но к сожалению не имею достаточно знаний.
Вопрос, костыль или нет? Если поможете переделать, буду рад.
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.
DROP TABLE IF EXISTS treatment.reports_template;
CREATE TABLE treatment.reports_template (
group_parent int(11) DEFAULT NULL,
groupid int(11) DEFAULT NULL,
groupid2 int(11) DEFAULT NULL,
torep int(11) DEFAULT NULL,
KODnom varchar(10) DEFAULT NULL,
NAIM varchar(255) DEFAULT NULL,
all0 int(11) DEFAULT NULL,
all1 int(11) DEFAULT NULL,
ud0 int(11) DEFAULT NULL,
view1 int(11) DEFAULT NULL,
ud1 int(11) DEFAULT NULL,
raz0 int(11) DEFAULT NULL,
raz1 int(11) DEFAULT NULL,
otk0 int(11) DEFAULT NULL,
otk1 int(11) DEFAULT NULL,
view0 int(11) DEFAULT NULL
)
ENGINE = INNODB
AVG_ROW_LENGTH = 496
CHARACTER SET cp1251
COLLATE cp1251_general_ci;
#Создание каркаса отчета с первоначальными суммами
INSERT INTO reports_template
(select kodvopr.group_parent AS group_parent,
kodvopr.groupid AS groupid,
kodvopr.groupid2 AS groupid2,
kodvopr.torep AS torep,
kodvopr.KODnom AS KODnom,
kodvopr.NAIM as NAIM,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') then 1 else 0 end) AS All0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') then 1 else 0 end) AS All1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (resultview = 'УДОВЛЕТВОРЕНО') then 1 else 0 end) AS ud0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (resultview = 'УДОВЛЕТВОРЕНО') then 1 else 0 end) As ud1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (resultview = 'РАЗЪЯСНЕНО') then 1 else 0 end) AS raz0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (resultview = 'РАЗЪЯСНЕНО') then 1 else 0 end) As raz1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (resultview = 'ОТКАЗАHО') then 1 else 0 end) As otk0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (resultview = 'ОТКАЗАHО') then 1 else 0 end) AS otk1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (control=1) AND (resultview='') then 1 else 0 end)AS view0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (control=1) AND (resultview='') then 1 else 0 end) AS view1
From kodvopr
LEFT JOIN register ON kodvopr.KODnom = register.themeid
group by KODnom ORDER BY kodvopr.KOD11,kodvopr.groupid);
DROP TABLE IF EXISTS treatment.reports_template2;
CREATE TABLE treatment.reports_template2 (
group_parent int(11) DEFAULT NULL,
groupid int(11) DEFAULT NULL,
groupid2 int(11) DEFAULT NULL,
torep int(11) DEFAULT NULL,
KODnom varchar(10) DEFAULT NULL,
all0 int(11) DEFAULT NULL,
all1 int(11) DEFAULT NULL,
ud0 int(11) DEFAULT NULL,
view1 int(11) DEFAULT NULL,
ud1 int(11) DEFAULT NULL,
raz0 int(11) DEFAULT NULL,
raz1 int(11) DEFAULT NULL,
otk0 int(11) DEFAULT NULL,
otk1 int(11) DEFAULT NULL,
view0 int(11) DEFAULT NULL
)
ENGINE = INNODB
AVG_ROW_LENGTH = 496
CHARACTER SET cp1251
COLLATE cp1251_general_ci;
# запись результатов сумм по группам первой вложенности
INSERT INTO reports_template2
(select kodvopr.group_parent AS group_parent,
kodvopr.groupid AS groupid,
kodvopr.groupid2 AS groupid2,
kodvopr.torep AS torep,
kodvopr.KODnom AS KODnom,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') then 1 else 0 end) AS All0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') then 1 else 0 end) AS All1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (resultview = 'УДОВЛЕТВОРЕНО') then 1 else 0 end) AS ud0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (resultview = 'УДОВЛЕТВОРЕНО') then 1 else 0 end) As ud1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (resultview = 'РАЗЪЯСНЕНО') then 1 else 0 end) AS raz0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (resultview = 'РАЗЪЯСНЕНО') then 1 else 0 end) As raz1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (resultview = 'ОТКАЗАHО') then 1 else 0 end) As otk0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (resultview = 'ОТКАЗАHО') then 1 else 0 end) AS otk1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (control=1) AND (resultview='') then 1 else 0 end)AS view0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (control=1) AND (resultview='') then 1 else 0 end) AS view1
From kodvopr
LEFT JOIN register ON kodvopr.KODnom = register.themeid
group by groupid ORDER BY kodvopr.KOD11,kodvopr.groupid);
# запись сумм первой вложенности в таблицу отчета
UPDATE reports_template,reports_template2
SET
reports_template.all0=reports_template2.All0,
reports_template.all1=reports_template2.All1,
reports_template.ud0=reports_template2.ud0,
reports_template.ud1=reports_template2.ud1,
reports_template.view0=reports_template2.view0,
reports_template.view1=reports_template2.view1,
reports_template.raz0=reports_template2.raz0,
reports_template.raz1=reports_template2.raz1,
reports_template.otk0=reports_template2.otk0,
reports_template.otk1=reports_template2.otk1
WHERE (reports_template.groupid =reports_template2.groupid AND reports_template.torep=0);
TRUNCATE TABLE reports_template2;
# запись результатов сумм по группам второй вложенности
INSERT INTO reports_template2
(select kodvopr.group_parent AS group_parent,
kodvopr.groupid AS groupid,
kodvopr.groupid2 AS groupid2,
kodvopr.torep AS torep,
kodvopr.KODnom AS KODnom,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') then 1 else 0 end) AS All0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') then 1 else 0 end) AS All1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (resultview = 'УДОВЛЕТВОРЕНО') then 1 else 0 end) AS ud0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (resultview = 'УДОВЛЕТВОРЕНО') then 1 else 0 end) As ud1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (resultview = 'РАЗЪЯСНЕНО') then 1 else 0 end) AS raz0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (resultview = 'РАЗЪЯСНЕНО') then 1 else 0 end) As raz1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (resultview = 'ОТКАЗАHО') then 1 else 0 end) As otk0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (resultview = 'ОТКАЗАHО') then 1 else 0 end) AS otk1,
sum(case when (datecreate between '2018-01-01' AND '2018-10-09') AND (control=1) AND (resultview='') then 1 else 0 end)AS view0,
sum(case when (datecreate between '2017-01-01' AND '2017-10-09') AND (control=1) AND (resultview='') then 1 else 0 end) AS view1
From kodvopr
LEFT JOIN register ON kodvopr.KODnom = register.themeid
group by group_parent ORDER BY kodvopr.KOD11,kodvopr.groupid);
# запись сумм второй вложенности в таблицу отчета
UPDATE reports_template,reports_template2
SET
reports_template.all0=reports_template2.All0,
reports_template.all1=reports_template2.All1,
reports_template.ud0=reports_template2.ud0,
reports_template.ud1=reports_template2.ud1,
reports_template.view0=reports_template2.view0,
reports_template.view1=reports_template2.view1,
reports_template.raz0=reports_template2.raz0,
reports_template.raz1=reports_template2.raz1,
reports_template.otk0=reports_template2.otk0,
reports_template.otk1=reports_template2.otk1
WHERE (reports_template.group_parent=reports_template.groupid2 AND reports_template.torep=0);
Далее после вывода отчета 2 таблицы дропаются. |
