SELECT
DL.CJNAME as JG--DL.CJNAME,DL.BZNAME
,SUM(DL16_1.DL16GDNL) AS S21,COUNT(DL16_1.DLOID) AS S22,SUM(DL16_2.DL16GDNL) S23,COUNT(DL16_2.DLOID) S24,SUM(DL16_3.DL16GDNL) AS S25,COUNT(DL16_3.DLOID) AS S26,SUM(DL16_4.DL16GDNL) AS S27,COUNT(DL16_4.DLOID) AS S28,COUNT(DL16_5.DLOID) AS S29,COUNT(DL16_6.DLOID) AS S30
,SUM(DL24.DL24GL) AS S31,COUNT(DL24.DLOID) AS S32
,COUNT(DL9_1.DLOID) AS S126,COUNT(DL9_2.DLOID) AS S127,COUNT(DL9_3.DLOID) AS S128
,COUNT(DL19_1.DLOID) AS S131,COUNT(DL19_2.DLOID) AS S132,COUNT(DL19_3.DLOID) AS S133
,COUNT(DL23_1.DLOID) AS S141,COUNT(DL23_2.DLOID) AS S142,COUNT(DL23_3.DLOID) AS S143
,COUNT(DL22_1.DLOID) AS S146,COUNT(DL22_2.DLOID) AS S147,COUNT(DL22_3.DLOID) AS S148
,COUNT(DL11_1.DLOID) AS S151,COUNT(DL11_2.DLOID) AS S152,COUNT(DL11_3.DLOID) AS S153
,COUNT(DL14_1.DLOID) AS S156,COUNT(DL14_2.DLOID) AS S157,COUNT(DL14_3.DLOID) AS S158
,COUNT(DL12_1.DLOID) AS S161,COUNT(DL12_2.DLOID) AS S162,COUNT(DL12_3.DLOID) AS S163
,COUNT(DL13_1.DLOID) AS S166,COUNT(DL13_2.DLOID) AS S167,COUNT(DL13_3.DLOID) AS S168
,COUNT(DL26_1.DLOID) AS S177,COUNT(DL26_2.DLOID) AS S178,COUNT(DL26_3.DLOID) AS S179
,SUM(DL34_1.DL34JZMJ) AS S183,SUM(DL34_2.DL34JZMJ) AS S184,SUM(DL34_3.DL34JZMJ) AS S185,SUM(DL34_4.DL34JZMJ) AS S186 ,SUM(DL34_5.DL34JZMJ) AS S187
,SUM(DL34_1.DL34ZDMJ) AS S188,SUM(DL34_2.DL34ZDMJ) AS S189,SUM(DL34_3.DL34ZDMJ) AS S190,SUM(DL34_4.DL34ZDMJ) AS S191 ,SUM(DL34_5.DL34ZDMJ) AS S192
FROM
EMIS_DLDEV_REPORT DL
,(SELECT DLOID,dl16gdnl FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL16\' AND DL16LX LIKE \'发电所\') DL16_1
,(SELECT DLOID,dl16gdnl FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL16\' AND DL16LX LIKE \'变电所\') DL16_2
,(SELECT DLOID,dl16gdnl FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL16\' AND DL16LX LIKE \'配电所\') DL16_3
,(SELECT DLOID,dl16gdnl FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL16\' AND DL16LX LIKE \'车间变电所\') DL16_4
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL16\' AND DL16LX LIKE \'开闭所\') DL16_5
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL16\' AND DL16LX LIKE \'箱变\') DL16_6
,(SELECT DLOID,DL24GL FROM EMIS_DLDEV_REPORT DL24 WHERE DEVTYPE=\'DL24\' AND DL24SBMC=\'发电车\') DL24
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL9\' AND JDRESULT!=NULL) DL9_1
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL9\' AND JDRESULT in (\'优良\',\'合格\')) DL9_2
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL9\' AND JDRESULT in (\'优良\')) DL9_3
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL19\' AND JDRESULT!=NULL) DL19_1
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL19\' AND JDRESULT in (\'优良\',\'合格\')) DL19_2
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL19\' AND JDRESULT in (\'优良\')) DL19_3
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL23\' AND JDRESULT!=NULL) DL23_1
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL23\' AND JDRESULT in (\'优良\',\'合格\')) DL23_2
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL23\' AND JDRESULT in (\'优良\')) DL23_3
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL22\' AND JDRESULT!=NULL) DL22_1
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL22\' AND JDRESULT in (\'优良\',\'合格\')) DL22_2
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL22\' AND JDRESULT in (\'优良\')) DL22_3
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL11\' AND JDRESULT!=NULL) DL11_1
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL11\' AND JDRESULT in (\'优良\',\'合格\')) DL11_2
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL11\' AND JDRESULT in (\'优良\')) DL11_3
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL14\' AND JDRESULT!=NULL) DL14_1
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL14\' AND JDRESULT in (\'优良\',\'合格\')) DL14_2
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL14\' AND JDRESULT in (\'优良\')) DL14_3
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL12\' AND JDRESULT!=NULL) DL12_1
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL12\' AND JDRESULT in (\'优良\',\'合格\')) DL12_2
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL12\' AND JDRESULT in (\'优良\')) DL12_3
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL13\' AND JDRESULT!=NULL) DL13_1
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL13\' AND JDRESULT in (\'优良\',\'合格\')) DL13_2
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL13\' AND JDRESULT in (\'优良\')) DL13_3
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL26\' AND JDRESULT!=NULL) DL26_1
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL26\' AND JDRESULT in (\'优良\',\'合格\')) DL26_2
,(SELECT DLOID FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL26\' AND JDRESULT in (\'优良\')) DL26_3
,(SELECT DLOID,DL34JZMJ,DL34ZDMJ FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL34\' AND DL34LB=\'电力\') DL34_1
,(SELECT DLOID,DL34JZMJ,DL34ZDMJ FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL34\' AND DL34LB=\'电力\' AND DL34XZ=\'供电所\') DL34_2
,(SELECT DLOID,DL34JZMJ,DL34ZDMJ FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL34\' AND DL34LB=\'电力\' AND DL34XZ=\'电力工区\') DL34_3
,(SELECT DLOID,DL34JZMJ,DL34ZDMJ FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL34\' AND DL34LB=\'电力\' AND DL34XZ=\'变配电所\') DL34_4
,(SELECT DLOID,DL34JZMJ,DL34ZDMJ FROM EMIS_DLDEV_REPORT WHERE DEVTYPE=\'DL34\' AND DL34LB=\'电力\' AND DL34XZ NOT IN (\'供电所\',\'电力工区\',\'变配电所\')) DL34_5
WHERE
1 = 1
AND DL.DLOID=DL16_1.DLOID(+) AND DL.DLOID=DL16_2.DLOID(+) AND DL.DLOID=DL16_2.DLOID(+) AND DL.DLOID=DL16_3.DLOID(+) AND DL.DLOID=DL16_4.DLOID(+) AND DL.DLOID=DL16_5.DLOID(+) AND DL.DLOID=DL16_6.DLOID(+)
AND DL.DLOID=DL24.DLOID(+)
AND DL.DLOID=DL9_1.DLOID(+) AND DL.DLOID=DL9_2.DLOID(+) AND DL.DLOID=DL9_3.DLOID(+)
AND DL.DLOID=DL19_1.DLOID(+) AND DL.DLOID=DL19_2.DLOID(+) AND DL.DLOID=DL19_3.DLOID(+)
AND DL.DLOID=DL23_1.DLOID(+) AND DL.DLOID=DL23_2.DLOID(+) AND DL.DLOID=DL23_3.DLOID(+)
AND DL.DLOID=DL22_1.DLOID(+) AND DL.DLOID=DL22_2.DLOID(+) AND DL.DLOID=DL22_3.DLOID(+)
AND DL.DLOID=DL11_1.DLOID(+) AND DL.DLOID=DL11_2.DLOID(+) AND DL.DLOID=DL11_3.DLOID(+)
AND DL.DLOID=DL14_1.DLOID(+) AND DL.DLOID=DL14_2.DLOID(+) AND DL.DLOID=DL14_3.DLOID(+)
AND DL.DLOID=DL12_1.DLOID(+) AND DL.DLOID=DL12_2.DLOID(+) AND DL.DLOID=DL12_3.DLOID(+)
AND DL.DLOID=DL13_1.DLOID(+) AND DL.DLOID=DL13_2.DLOID(+) AND DL.DLOID=DL13_3.DLOID(+)
AND DL.DLOID=DL26_1.DLOID(+) AND DL.DLOID=DL26_2.DLOID(+) AND DL.DLOID=DL26_3.DLOID(+)
AND DL.DLOID=DL34_1.DLOID(+) AND DL.DLOID=DL34_2.DLOID(+) AND DL.DLOID=DL34_3.DLOID(+) AND DL.DLOID=DL34_4.DLOID(+) AND DL.DLOID=DL34_5.DLOID(+)
--AND DL.DLOID=DL16.DLOID(+) AND DL.DLOID=DL24.DLOID(+) AND DL.DLOID=DL9.DLOID(+)
--AND DL.CJNAME = \'合肥西供电车间\'
and DL.CJOID<>0 --DL.CJOID=123
GROUP BY DL.CJNAME--DL.CJNAME,DL.BZNAME
