落后发电单元 sql

SELECT
	a.s_id,
	a.busi_code,
	a.POORLOSS,
	a.display_time,
	a.d_name
FROM
	(
		SELECT
			*
		FROM
			(
				SELECT
					c.s_id,
					c.busi_code,
					(
						a.perpower_ratio_avg - c.perpower_ratio
					) * b.TOTALCAPACITY AS POORLOSS,
					c.perpower_ratio,
					a.perpower_ratio_avg,
					c.display_time,
					c.d_name
				FROM
					(
						SELECT
							a.loc_id,
							avg(a.perpower_ratio) AS perpower_ratio_avg
						FROM
							(
								SELECT
									b.loc_id,
									a.perpower_ratio
								FROM
									pms_kpi_inverter_day_t a,
									pms_device_t b
								WHERE
									a.busi_code = b.busi_code
								AND a.s_Id = b.s_Id
								AND b.d_state = 1
								AND b.dev_type_id = 1
								AND a.display_time = 20180725
								AND a.s_id = '2c28878c60ca56f50161509bc0606b26'
							) a
						GROUP BY
							a.loc_id
					) a,
					(
						SELECT
							dev.loc_id,
							dev.busi_code,
							dev.pv_total_capacity AS TOTALCAPACITY
						FROM
							pms_device_t dev
						WHERE
							dev.s_Id = '2c28878c60ca56f50161509bc0606b26'
						AND dev.d_state = 1
						AND dev.dev_type_id = 1
					) b,
					(
						SELECT
							s_id,
							busi_code,
							lose_effectiveness,
							display_time,
							d_name,
							perpower_ratio
						FROM
							pms_kpi_inverter_day_t
						WHERE
							perpower_ratio <> 0
						AND s_id = '2c28878c60ca56f50161509bc0606b26'
						AND display_time = 20180725
					) c
				WHERE
					a.loc_id = b.loc_id
				AND b.busi_code = c.busi_code
				AND a.perpower_ratio_avg > c.perpower_ratio
				AND (
					1 - (
						a.perpower_ratio_avg - c.perpower_ratio
					) / perpower_ratio_avg
				) * 100 < 0.99
			) a
		UNION ALL
			(
				SELECT
					kit.s_Id AS s_id,
					kit.busi_code,
					kit.lose_power AS POORLOSS,
					kit.perpower_ratio,
					pprAvg.perpower_ratio_avg,
					kit.display_time,
					kit.d_name
				FROM
					pms_kpi_inverter_day_t kit,
					pms_device_t dt,
					(
						SELECT
							AVG(avgQuery.perpower_ratio) AS perpower_ratio_avg
						FROM
							(
								SELECT
									perpower_ratio
								FROM
									pms_kpi_inverter_day_t t,
									pms_device_t d
								WHERE
									t.busi_code = d.busi_code
								AND t.s_id = '2c28878c60ca56f50161509bc0606b26'
								AND d.s_id = '2c28878c60ca56f50161509bc0606b26'
								AND t.display_time = 20180725
								AND d.dev_type_id = 14
							) avgQuery
					) pprAvg
				WHERE
					kit.busi_code = dt.busi_code
				AND kit.s_id = '2c28878c60ca56f50161509bc0606b26'
				AND dt.s_id = '2c28878c60ca56f50161509bc0606b26'
				AND dt.dev_type_id = 14
				AND kit.display_time = 20180725
				AND kit.lose_power > 0
				AND pprAvg.perpower_ratio_avg > kit.perpower_ratio
				AND (
					1 - (
						pprAvg.perpower_ratio_avg - kit.perpower_ratio
					) / pprAvg.perpower_ratio_avg
				) * 100 < 0.99
			)
		ORDER BY
			POORLOSS DESC
	) a