Форум пользователей MySQL

кавычки, комментарии, ISNULL я подправил

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

т.е. придется кардинально переписывать запрос