Добрый день!
Я прогер, пишу в среде Delphi, мой мозг просто разрывает SQL(
>> скукотища
Твой вариант работает, но считает не то(
Мысль в том, что я не знаю, сколько еще будет: "tbl_ef_576R.cl_GE_NAME " как известных для остальных подзапросов:
1.
2.
3.
4.
5.
6.
7.
8.
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=4 AND tbl_ef_576R.cl_TA=1 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_ARM
Много мусора, сорри, мой кусок можно урезать до:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT tbl_ef_576R.ID_PARENT,cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_TA=1 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
) AS T2
GROUP BY T2.ID_PARENT) AS T2_1
)AS cl_ALL_DEVICE
Засорю всем текстом, и кому лень читать - вот ответ гуру: "в mysql нет коррелированных from подзапросов. т.е. из подзапроса в части from нельзя ссылаться на внешний запрос"
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.
SELECT
tbl_RESULT.ID AS "№№",
tbl_RESULT.cl_INDEX AS "Индекс ФСК",
tbl_ef_MS.cl_NAME AS "МЭС",
tbl_ef_PMS.cl_NAME AS "ПМЭС",
tbl_ef_KV.cl_NAME AS "Класс напряжения",
tbl_RESULT.cl_PS AS "Подстанция",
tbl_RESULT.cl_TITLE_NAME AS "Наименование титула",
tbl_RESULT.clSYSTEM AS "Система",
tbl_RESULT.cl_DATE AS "Подписание КС-14",
tbl_ef_STAGE.cl_NAME AS "Вид/стадия выполнения работ",
tbl_ef_SUPLIER.cl_NAME AS "Поставщик",
tbl_ef_VENDER.cl_NAME AS "Оборудование",
tbl_RESULT.cl_SERVERS AS "Количество серверов",
tbl_RESULT.cl_ARM AS "Количество АРМ-ов",
tbl_RESULT.cl_KONTROLLER AS "Количество контроллеров присоединений",
tbl_RESULT.cl_SHLUZ AS "Серверы, шлюзы ТМ (контроллер среднего уровня)",
tbl_RESULT.cl_SEV AS "Система единого уровня",
tbl_RESULT.cl_MARSHUTIZATOR AS "Коммутаторы и маршрутизаторы",
tbl_RESULT.cl_DEVICES AS "Прочее оборудование, учитываемое при расчете у.е.",
tbl_RESULT.cl_ALL_DEVICE AS "Все оборудование"
FROM
(
SELECT tbl_ef_PS.ID,tbl_ef_PS.cl_INDEX,tbl_ef_PS.cl_MS,tbl_ef_PS.cl_PMS,tbl_ef_PS.cl_KV,tbl_ef_PS.cl_PS,
tbl_ef_TITLE.cl_NAME AS cl_TITLE_NAME,'АСУТП' AS clSYSTEM,tbl_ef_TITLE.cl_DATE,tbl_ef_TITLE.cl_STAGE,tbl_ef_TITLE.cl_SUPLIER,tbl_ef_TITLE.cl_VENDER,
tbl_ef_TITLE.cl_SERVERS,tbl_ef_TITLE.cl_ARM,tbl_ef_TITLE.cl_KONTROLLER,tbl_ef_TITLE.cl_SHLUZ,tbl_ef_TITLE.cl_SEV,tbl_ef_TITLE.cl_MARSHUTIZATOR,
(ISNULL(tbl_ef_TITLE.cl_ALL_DEVICE,0)
-ISNULL(tbl_ef_TITLE.cl_SERVERS,0)
-ISNULL(tbl_ef_TITLE.cl_ARM,0)
-ISNULL(tbl_ef_TITLE.cl_KONTROLLER,0)
-ISNULL(tbl_ef_TITLE.cl_SHLUZ,0)
-ISNULL(tbl_ef_TITLE.cl_SEV,0)
-ISNULL(tbl_ef_TITLE.cl_MARSHUTIZATOR,NULL))AS cl_DEVICES,tbl_ef_TITLE.cl_ALL_DEVICE
FROM tbl_ef_PS
RIGHT JOIN
(SELECT tbl_ef_TITLE.*,
(
--Все устройства
SELECT cl_VALUE
FROM
(
SELECT T2.ID_PARENT, SUM(T2.cl_VALUE) AS cl_VALUE
FROM
(
SELECT tbl_ef_576R.ID,tbl_ef_576R.ID_PARENT, tbl_ef_576R.cl_TA,
tbl_ef_576R.cl_GE_MAIN,tbl_ef_576R.cl_GE_NAME, cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_TA=1 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
) AS T2
GROUP BY T2.ID_PARENT) AS T2_1
)AS cl_ALL_DEVICE,
--Серверы
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=3 AND tbl_ef_576R.cl_TA=1 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_SERVERS,
--АРМ
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=4 AND tbl_ef_576R.cl_TA=1 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_ARM,
--Контроллеры
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=5 AND tbl_ef_576R.cl_TA=1 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_KONTROLLER,
--Шлюз телемеханики
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=9 AND tbl_ef_576R.cl_TA=1 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_SHLUZ,
--СЕВ
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=6 AND tbl_ef_576R.cl_TA=1 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_SEV,
--Маршрутизатор и коммутатор
(
SELECT cl_VALUE
FROM
(
SELECT T2.ID_PARENT, SUM(T2.cl_VALUE) AS cl_VALUE
FROM
(
SELECT tbl_ef_576R.ID,tbl_ef_576R.ID_PARENT, tbl_ef_576R.cl_TA,
tbl_ef_576R.cl_GE_MAIN,tbl_ef_576R.cl_GE_NAME, cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE ((tbl_ef_576R.cl_GE_NAME=1 OR tbl_ef_576R.cl_GE_NAME=2) AND (tbl_ef_576R.cl_TA=1) AND (tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT))
) AS T2
GROUP BY T2.ID_PARENT) AS T2_1
) AS cl_MARSHUTIZATOR
FROM tbl_ef_TITLE AS tbl_ef_TITLE,
(SELECT ID_PARENT, MAX(ID) AS cl_ID FROM tbl_ef_TITLE WHERE tbl_ef_TITLE.cl_SYSTEM=1
GROUP BY ID_PARENT) AS A2
WHERE (tbl_ef_TITLE.ID_PARENT = A2.ID_PARENT and tbl_ef_TITLE.ID = A2.cl_ID)) AS tbl_ef_TITLE
ON tbl_ef_PS.ID=tbl_ef_TITLE.ID_PARENT
WHERE cl_ALL_DEVICE > 0
--ССПИ
UNION ALL
SELECT tbl_ef_PS.ID,tbl_ef_PS.cl_INDEX,tbl_ef_PS.cl_MS,tbl_ef_PS.cl_PMS,tbl_ef_PS.cl_KV,tbl_ef_PS.cl_PS,
tbl_ef_TITLE.cl_NAME,'ССПИ' AS clSYSTEM,tbl_ef_TITLE.cl_DATE,tbl_ef_TITLE.cl_STAGE,tbl_ef_TITLE.cl_SUPLIER,tbl_ef_TITLE.cl_VENDER,
tbl_ef_TITLE.cl_SERVERS,tbl_ef_TITLE.cl_ARM,tbl_ef_TITLE.cl_KONTROLLER,tbl_ef_TITLE.cl_SHLUZ,tbl_ef_TITLE.cl_SEV,tbl_ef_TITLE.cl_MARSHUTIZATOR,
(ISNULL(tbl_ef_TITLE.cl_ALL_DEVICE,0)
-ISNULL(tbl_ef_TITLE.cl_SERVERS,0)
-ISNULL(tbl_ef_TITLE.cl_ARM,0)
-ISNULL(tbl_ef_TITLE.cl_KONTROLLER,0)
-ISNULL(tbl_ef_TITLE.cl_SHLUZ,0)
-ISNULL(tbl_ef_TITLE.cl_SEV,0)
-ISNULL(tbl_ef_TITLE.cl_MARSHUTIZATOR,0))AS cl_DEVICES,tbl_ef_TITLE.cl_ALL_DEVICE
FROM tbl_ef_PS
RIGHT JOIN
(SELECT tbl_ef_TITLE.*,
(
--Все устройства
SELECT cl_VALUE
FROM
(
SELECT T2.ID_PARENT, SUM(T2.cl_VALUE) AS cl_VALUE
FROM
(
SELECT tbl_ef_576R.ID,tbl_ef_576R.ID_PARENT, tbl_ef_576R.cl_TA,
tbl_ef_576R.cl_GE_MAIN,tbl_ef_576R.cl_GE_NAME, cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_TA=2 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
) AS T2
GROUP BY T2.ID_PARENT) AS T2_1
)AS cl_ALL_DEVICE,
--Серверы
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=3 AND tbl_ef_576R.cl_TA=2 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_SERVERS,
--АРМ
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=4 AND tbl_ef_576R.cl_TA=2 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_ARM,
--Контроллеры
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=5 AND tbl_ef_576R.cl_TA=2 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_KONTROLLER,
--Шлюз телемеханики
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=9 AND tbl_ef_576R.cl_TA=2 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_SHLUZ,
--СЕВ
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=6 AND tbl_ef_576R.cl_TA=2 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_SEV,
--Маршрутизатор и коммутатор
(
SELECT cl_VALUE
FROM
(
SELECT T2.ID_PARENT, SUM(T2.cl_VALUE) AS cl_VALUE
FROM
(
SELECT tbl_ef_576R.ID,tbl_ef_576R.ID_PARENT, tbl_ef_576R.cl_TA,
tbl_ef_576R.cl_GE_MAIN,tbl_ef_576R.cl_GE_NAME, cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE ((tbl_ef_576R.cl_GE_NAME=1 OR tbl_ef_576R.cl_GE_NAME=2) AND (tbl_ef_576R.cl_TA=2) AND (tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT))
) AS T2
GROUP BY T2.ID_PARENT) AS T2_1
) AS cl_MARSHUTIZATOR
FROM tbl_ef_TITLE AS tbl_ef_TITLE,
(SELECT ID_PARENT, MAX(ID) AS cl_ID FROM tbl_ef_TITLE WHERE tbl_ef_TITLE.cl_SYSTEM=2
GROUP BY ID_PARENT) AS A2
WHERE (tbl_ef_TITLE.ID_PARENT = A2.ID_PARENT and tbl_ef_TITLE.ID = A2.cl_ID)) AS tbl_ef_TITLE
ON tbl_ef_PS.ID=tbl_ef_TITLE.ID_PARENT
WHERE cl_ALL_DEVICE > 0
--ССПТИ
UNION ALL
SELECT tbl_ef_PS.ID,tbl_ef_PS.cl_INDEX,tbl_ef_PS.cl_MS,tbl_ef_PS.cl_PMS,tbl_ef_PS.cl_KV,tbl_ef_PS.cl_PS,
tbl_ef_TITLE.cl_NAME,'ССПТИ' AS clSYSTEM,tbl_ef_TITLE.cl_DATE,tbl_ef_TITLE.cl_STAGE,tbl_ef_TITLE.cl_SUPLIER,tbl_ef_TITLE.cl_VENDER,
tbl_ef_TITLE.cl_SERVERS,tbl_ef_TITLE.cl_ARM,tbl_ef_TITLE.cl_KONTROLLER,tbl_ef_TITLE.cl_SHLUZ,tbl_ef_TITLE.cl_SEV,tbl_ef_TITLE.cl_MARSHUTIZATOR,
(ISNULL(tbl_ef_TITLE.cl_ALL_DEVICE,0)
-ISNULL(tbl_ef_TITLE.cl_SERVERS,0)
-ISNULL(tbl_ef_TITLE.cl_ARM,0)
-ISNULL(tbl_ef_TITLE.cl_KONTROLLER,0)
-ISNULL(tbl_ef_TITLE.cl_SHLUZ,0)
-ISNULL(tbl_ef_TITLE.cl_SEV,0)
-ISNULL(tbl_ef_TITLE.cl_MARSHUTIZATOR,0)) AS cl_DEVICES,tbl_ef_TITLE.cl_ALL_DEVICE
FROM tbl_ef_PS
RIGHT JOIN
(SELECT tbl_ef_TITLE.*,
(
--Все устройства
SELECT cl_VALUE
FROM
(
SELECT T2.ID_PARENT, SUM(T2.cl_VALUE) AS cl_VALUE
FROM
(
SELECT tbl_ef_576R.ID,tbl_ef_576R.ID_PARENT, tbl_ef_576R.cl_TA,
tbl_ef_576R.cl_GE_MAIN,tbl_ef_576R.cl_GE_NAME, cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_TA=4 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
) AS T2
GROUP BY T2.ID_PARENT) AS T2_1
)AS cl_ALL_DEVICE,
--Серверы
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=3 AND tbl_ef_576R.cl_TA=4 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_SERVERS,
--АРМ
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=4 AND tbl_ef_576R.cl_TA=4 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_ARM,
--Контроллеры
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=5 AND tbl_ef_576R.cl_TA=4 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_KONTROLLER,
--Шлюз телемеханики
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=9 AND tbl_ef_576R.cl_TA=4 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_SHLUZ,
--СЕВ
(SELECT cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE (tbl_ef_576R.cl_GE_NAME=6 AND tbl_ef_576R.cl_TA=4 AND tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT)
)AS cl_SEV,
--Маршрутизатор и коммутатор
(
SELECT cl_VALUE
FROM
(
SELECT T2.ID_PARENT, SUM(T2.cl_VALUE) AS cl_VALUE
FROM
(
SELECT tbl_ef_576R.ID,tbl_ef_576R.ID_PARENT, tbl_ef_576R.cl_TA,
tbl_ef_576R.cl_GE_MAIN,tbl_ef_576R.cl_GE_NAME, cl_VALUE
FROM (SELECT ID_PARENT,SUM(cl_VALUE) AS cl_VALUE
FROM tbl_ef_576R_DETAIL
GROUP BY ID_PARENT) AS T1
RIGHT JOIN tbl_ef_576R ON
T1.ID_PARENT = tbl_ef_576R.ID
WHERE ((tbl_ef_576R.cl_GE_NAME=1 OR tbl_ef_576R.cl_GE_NAME=2) AND (tbl_ef_576R.cl_TA=4) AND (tbl_ef_576R.ID_PARENT=tbl_ef_TITLE.ID_PARENT))
) AS T2
GROUP BY T2.ID_PARENT) AS T2_1
) AS cl_MARSHUTIZATOR
FROM tbl_ef_TITLE AS tbl_ef_TITLE,
(SELECT ID_PARENT, MAX(ID) AS cl_ID FROM tbl_ef_TITLE WHERE tbl_ef_TITLE.cl_SYSTEM=1 OR tbl_ef_TITLE.cl_SYSTEM=2
GROUP BY ID_PARENT) AS A2
WHERE (tbl_ef_TITLE.ID_PARENT = A2.ID_PARENT and tbl_ef_TITLE.ID = A2.cl_ID)) AS tbl_ef_TITLE
ON tbl_ef_PS.ID=tbl_ef_TITLE.ID_PARENT
WHERE cl_ALL_DEVICE > 0
) AS tbl_RESULT
LEFT OUTER JOIN tbl_ef_MS ON cl_MS = tbl_ef_MS.ID
LEFT OUTER JOIN tbl_ef_PMS ON cl_PMS = tbl_ef_PMS.ID
LEFT OUTER JOIN tbl_ef_KV ON cl_KV = tbl_ef_KV.ID
LEFT OUTER JOIN tbl_ef_STAGE ON cl_STAGE = tbl_ef_STAGE.ID
LEFT OUTER JOIN tbl_ef_SUPLIER ON cl_SUPLIER = tbl_ef_SUPLIER.ID
LEFT OUTER JOIN tbl_ef_VENDER ON cl_VENDER = tbl_ef_VENDER.ID
|
