1:注意参数daySub也可以传到select 和 from 之间的sql中。
2:sql执行都是从内到外的顺序执行
SELECT
station_uuid,
stake_code,
sum(fault_long_time)/#{daySub}/24/3600 AS fault_ra1,
station_level_type,
manufacture_name,
stake_type,
<!-- 运营分析表和故障分析表-->
<mapper namespace="cn.datamining.stake.station.dao.AiOperationFaultDao">
<!--故障率析表-->
<select id="getFaultResultString" resultType="cn.datamining.stake.station.vo.FaultAnalysisResult">
SELECT
n1.stake_code AS stakeCode,
n1.station_level_type AS stationLevelType,
n1.station_status AS stationStatus,
n1.manufacture_name AS manufactureName,
n1.stake_type AS stakeType,
n1.stake_power AS stakePower,
n1.tcu_version AS tcuVersion,
n1.station_runtime AS stationRuntime,
n1.fault_ra1 AS stakeFaultRate,
(
n1.fault_ra1 / n2.fault_ra2 - 1
)AS stakeFaultRateSequential,
n1.fault_type AS faultType,
n1.fault_grade AS faultGrade,
n1.fault_times AS faultTimes,
n3.station_name AS stationName,
n1.station_uuid AS stationUuid,
n3.station_address AS stationAddress,
n3.stake_num_in_station AS stakeNumInStation,
n3.dc_number AS dcNumber,
n3.stationfaulttime1 AS stationFaultRate,
n4.stationfaulttime2 AS stationFaultRateSequential,
n3.station_fault_all_time AS stationFaultAllTime,
n3.fault_stakes_number AS faultStakesNumber,
n3.fault_stakes_rate AS faultStakesRate,
n3.fault_stakes_once_num AS faultStakesOnceNum,
n3.fault_stakes_once_rate AS faultStakesOnceRate,
n3.fault_stakes_twice_num AS faultStakesTwiceNum,
n3.fault_stakes_twice_rate AS faultStakesTwiceRate,
n3.fault_stakes_twice_more_num AS faultStakesTwiceMoreNum,
n3.fault_stakes_twice_more_rate AS faultStakesTwiceMoreRate,
n3.fault_stakes_three_years_num AS faultStakesThreeYearsNum,
n3.fault_stakes_three_years_rate AS faultStakesThreeYearsRate,
n1.avg_electricity_three AS avgElectricityThree,
n1.three_days_count AS threeDaysCount,
n1.avg_electricity_seven AS avgElectricitySeven,
n1.seven_days_count AS sevenDaysCount,
n1.avg_electricity_fifteen AS avgElectricityFifteen,
n1.fifteen_days_count AS fifteenDaysCount,
n1.avg_electricity_thirty AS avgElectricityThirty,
n1.thirty_days_count AS thirtyDaysCount,
n3.stationfaulttime1,
(
n3.stationfaulttime1 / n4.stationfaulttime2 - 1
)AS stationfault_seq
FROM
(
SELECT
station_uuid,
stake_code,
sum(fault_long_time)/#{daySub}/24/3600 AS fault_ra1,
station_level_type,
manufacture_name,
stake_type,
stake_power,
tcu_version,
station_runtime,
station_status,
fault_type,
fault_grade,
fault_times,
avg_electricity_three,
three_days_count,
avg_electricity_seven,
seven_days_count,
avg_electricity_fifteen,
fifteen_days_count,
avg_electricity_thirty,
thirty_days_count
FROM
stake_fault_day
WHERE 1=1
<if test="provinceLevelOrgCode !=null and provinceLevelOrgCode !=''">
AND province_level_org_code =#{provinceLevelOrgCode}
</if>
<if test="dateStartString !=null and dateStartString !=''">
AND stake_date >=#{dateStartString}
</if>
<if test="dateEndString !=null and dateEndString !=''">
AND stake_date <=#{dateEndString}
</if>
<if test="stakeDateStart !=null and stakeDateStart !=''">
AND stake_date >=#{stakeDateStart}
</if>
<if test="stakeDateEnd !=null and stakeDateEnd !=''">
AND stake_date <=#{stakeDateEnd}
</if>
<if test="stationLevelType !=null and stationLevelType !=''">
AND station_level_type=#{stationLevelType}
</if>
<if test="manufactureName !=null and manufactureName !=''">
AND manufacture_name=#{manufactureName}
</if>
<if test="provincePrCode !=null and provincePrCode !=''">
AND province_pr_code=#{provincePrCode}
</if>
<if test="provinceOmOrgCode !=null and provinceOmOrgCode !=''">
AND province_om_org_code=#{provinceOmOrgCode}
</if>
<if test="faultType !=null and faultType !=''">
AND fault_type=#{faultType}
</if>
GROUP BY
stake_code
)n1
LEFT JOIN(
SELECT
stake_code,
sum(fault_long_time)/ #{daySub} AS fault_ra2
FROM
stake_fault_day
WHERE 1=1
<if test="provinceLevelOrgCode !=null and provinceLevelOrgCode !=''">
AND province_level_org_code =#{provinceLevelOrgCode}
</if>
<if test="dateStartString !=null">
AND stake_date >=#{dateStartString}
</if>
<if test="dateEndString !=null">
AND stake_date <=#{dateEndString}
</if>
GROUP BY
stake_code
)n2 ON n1.stake_code = n2.stake_code
LEFT JOIN(
SELECT
station_uuid,
sum(station_fault_all_time)/ #{daySub}/24 AS stationfaulttime1,
station_name,
station_address,
stake_num_in_station,
dc_number,
station_fault_all_time,
fault_stakes_number,
fault_stakes_rate,
fault_stakes_once_num,
fault_stakes_once_rate,
fault_stakes_twice_num,
fault_stakes_twice_rate,
fault_stakes_twice_more_num,
fault_stakes_twice_more_rate,
fault_stakes_three_years_num,
fault_stakes_three_years_rate
FROM
station_fault_day
WHERE 1=1
<if test="provinceLevelOrgCode !=null and provinceLevelOrgCode !=''">
AND province_level_org_code =#{provinceLevelOrgCode}
</if>
<if test="stakeDateStart !=null">
AND station_date >=#{stakeDateStart}
</if>
<if test="stakeDateEnd !=null">
AND station_date <=#{stakeDateEnd}
</if>
GROUP BY
station_uuid
)n3 ON n1.station_uuid = n3.station_uuid
LEFT JOIN(
SELECT
station_uuid,
sum(station_fault_all_time)/ #{daySub} AS stationfaulttime2
FROM
station_fault_day
WHERE 1=1
<if test="provinceLevelOrgCode !=null and provinceLevelOrgCode !=''">
AND province_level_org_code =#{provinceLevelOrgCode}
</if>
<if test="dateStartString !=null">
AND station_date >=#{dateStartString}
</if>
<if test="dateEndString !=null">
AND station_date <=#{dateEndString}
</if>
GROUP BY
station_uuid
)n4 ON n1.station_uuid = n4.station_uuid
</select>
</mapper>