<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.cecep.talroad.mapper.effective.QEnvgasWqDayMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.cecep.talroad.domain.effective.QEnvgasWqDay">
        <id column="id" property="id"/>
        <result column="data_time" property="dataTime"/>
        <result column="region_code" property="regionCode"/>
        <result column="tsp" property="tsp"/>
        <result column="nox" property="nox"/>
        <result column="tvoc" property="tvoc"/>
        <result column="pmi" property="pmi"/>
        <result column="aqi" property="aqi"/>
        <result column="fk_pollutant_id" property="fkPollutantId"/>
        <result column="max_ipmi" property="maxIpmi"/>
        <result column="fk_aqilevel_id" property="fkAqilevelId"/>
        <result column="so2" property="so2"/>
        <result column="so2_is_over" property="so2IsOver"/>
        <result column="so2_max_value" property="so2MaxValue"/>
        <result column="so2_min_value" property="so2MinValue"/>
        <result column="iaqi_so2" property="iaqiSo2"/>
        <result column="ipmi_so2" property="ipmiSo2"/>
        <result column="no2" property="no2"/>
        <result column="no2_is_over" property="no2IsOver"/>
        <result column="no2_max_value" property="no2MaxValue"/>
        <result column="no2_min_value" property="no2MinValue"/>
        <result column="iaqi_no2" property="iaqiNo2"/>
        <result column="ipmi_no2" property="ipmiNo2"/>
        <result column="pm10" property="pm10"/>
        <result column="pm10_is_over" property="pm10IsOver"/>
        <result column="pm10_max_value" property="pm10MaxValue"/>
        <result column="pm10_min_value" property="pm10MinValue"/>
        <result column="iaqi_pm10" property="iaqiPm10"/>
        <result column="ipmi_pm10" property="ipmiPm10"/>
        <result column="pm25" property="pm25"/>
        <result column="pm25_is_over" property="pm25IsOver"/>
        <result column="pm25_max_value" property="pm25MaxValue"/>
        <result column="pm25_min_value" property="pm25MinValue"/>
        <result column="iaqi_pm25" property="iaqiPm25"/>
        <result column="ipmi_pm25" property="ipmiPm25"/>
        <result column="co" property="co"/>
        <result column="co_is_over" property="coIsOver"/>
        <result column="co_max_value" property="coMaxValue"/>
        <result column="co_min_value" property="coMinValue"/>
        <result column="iaqi_co" property="iaqiCo"/>
        <result column="ipmi_co" property="ipmiCo"/>
        <result column="o3" property="o3"/>
        <result column="o3_is_over" property="o3IsOver"/>
        <result column="o3_max_value" property="o3MaxValue"/>
        <result column="o3_min_value" property="o3MinValue"/>
        <result column="o3_8h" property="o38h"/>
        <result column="o3_8h_is_over" property="o38hIsOver"/>
        <result column="o3_8h_max_value" property="o38hMaxValue"/>
        <result column="o3_8h_min_value" property="o38hMinValue"/>
        <result column="iaqi_o3" property="iaqiO3"/>
        <result column="ipmi_o3" property="ipmiO3"/>
        <result column="iaqi_o3_8h" property="iaqiO38h"/>
        <result column="ipmi_o3_8h" property="ipmiO38h"/>
        <result column="temp" property="temp"/>
        <result column="temp_max_value" property="tempMaxValue"/>
        <result column="temp_min_value" property="tempMinValue"/>
        <result column="humi" property="humi"/>
        <result column="humi_max_value" property="humiMaxValue"/>
        <result column="humi_min_value" property="humiMinValue"/>
        <result column="winds" property="winds"/>
        <result column="winds_max_value" property="windsMaxValue"/>
        <result column="winds_min_value" property="windsMinValue"/>
        <result column="windd" property="windd"/>
        <result column="windd_max_value" property="winddMaxValue"/>
        <result column="windd_min_value" property="winddMinValue"/>
        <result column="pres" property="pres"/>
        <result column="pres_max_value" property="presMaxValue"/>
        <result column="pres_min_value" property="presMinValue"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, data_time, region_code, tsp, nox, tvoc, pmi, aqi, fk_pollutant_id, max_ipmi, fk_aqilevel_id, so2, so2_is_over, so2_max_value, so2_min_value, iaqi_so2, ipmi_so2, no2, no2_is_over, no2_max_value, no2_min_value, iaqi_no2, ipmi_no2, pm10, pm10_is_over, pm10_max_value, pm10_min_value, iaqi_pm10, ipmi_pm10, pm25, pm25_is_over, pm25_max_value, pm25_min_value, iaqi_pm25, ipmi_pm25, co, co_is_over, co_max_value, co_min_value, iaqi_co, ipmi_co, o3, o3_is_over, o3_max_value, o3_min_value, o3_8h, o3_8h_is_over, o3_8h_max_value, o3_8h_min_value, iaqi_o3, ipmi_o3, iaqi_o3_8h, ipmi_o3_8h, temp, temp_max_value, temp_min_value, humi, humi_max_value, humi_min_value, winds, winds_max_value, winds_min_value, windd, windd_max_value, windd_min_value, pres, pres_max_value, pres_min_value, create_time, update_time
    </sql>


    <select id="findYearGoodDays" resultType="cn.cecep.talroad.vo.effective.MonthDataVo">
        SELECT  COALESCE( SUM( fk_aqilevel_id ) ,'0' ) as goodDays,
         DATE_FORMAT( data_time, '%m' ) as monthNum
        FROM
            q_envgas_wq_day
        WHERE fk_aqilevel_id &lt; 2 and extract(year from data_time)= #{year} AND region_code = #{regionCode}
        GROUP BY to_char((SELECT data_time::timestamp),'yyyy-mm'),monthNum ORDER BY monthNum asc
    </select>
    <select id="findStandardMonitoringDay" resultType="java.util.Map">
        SELECT
            ROUND( aqi )  as aqi,
            ROUND( so2 ) as so2,
            ROUND( no2 ) as no2,
            ROUND( co, 1 ) as co,
            ROUND( o3_8h ) as o38h,
            ROUND( pm10 ) as pm10,
            ROUND( pm25 ) as pm25
        FROM
            q_envgas_wq_day
        WHERE
            TO_CHAR( data_time,'YYYY-MM-DD') = TO_CHAR( now(),'YYYY-MM-DD' )
            AND region_code = #{userRegionCode}
    </select>
    <select id="findStandardTargetDay" resultType="cn.cecep.talroad.vo.effective.QEnvgasAreaTargetVo">
        SELECT
        poll.NAME as pollutantName,
        poll.field as field,
        MAX(t.time_type),
        MAX(t.time),
        MAX(t.target_value),
        MAX(t.actual_value),
        MAX(t.fk_factor_id),
        MAX(t.region_code)
        FROM
        p_code_index_pollutant poll
        LEFT JOIN
        (
        select * from q_envgas_area_target t
        where t.delete_flag = '0'
        AND t.time = #{time}
        AND t.region_code = #{userRegionCode}
        <if test="type == 1 or type =='1'">
            and t.time_type ='2' AND t.fk_factor_id IN ( '1', '2', '3', '4', '5', '6' )
        </if>
        <if test="type == 2 or type =='2'">
            and t.time_type ='2' AND t.fk_factor_id IN ( '1', '2', '3', '4', '5', '6' ,'11','12')
        </if>
        <if test="type == 3 or type =='3'">
            and t.time_type ='3' AND t.fk_factor_id IN ( '1', '2', '3', '4', '5', '6' ,'11','12')
        </if>
        ) t ON poll.pk_id = t.fk_factor_id
        WHERE 1=1
        <if test="type == 1 or type =='1'">
            AND poll.pk_id IN ( '1', '2', '3', '4', '5', '6' )
        </if>
        <if test="type == 2 or type =='2'">
            AND poll.pk_id IN ( '1', '2', '3', '4', '5', '6' ,'11','12')
        </if>
        <if test="type == 3 or type =='3'">
            AND poll.pk_id IN ( '1', '2', '3', '4', '5', '6' ,'11','12')
        </if>
        GROUP BY poll.pk_id
    </select>
    <select id="findStandardMonitoringLastDay" resultType="java.util.Map">
         SELECT
            ROUND( aqi ) aqi,
            ROUND( so2 ) so2,
            ROUND( no2 ) no2,
            ROUND( co, 1 ) co,
            ROUND( o3_8h ) o38h,
            ROUND( pm10 ) pm10,
            ROUND( pm25 ) pm25
        FROM
            q_envgas_wq_day
        WHERE
        TO_CHAR( data_time, 'YYYY-MM-DD') =TO_CHAR(NOW() - INTERVAL '1 year', 'YYYY-MM-DD')
            AND region_code = #{userRegionCode}

    </select>

    <select id="findYearCoOverDays" resultType="cn.cecep.talroad.vo.effective.MonthDataVo">
      SELECT
          COALESCE( SUM( co), 0 ) as coOverDays,
          extract(month from data_time ) as monthNum
        FROM
            q_envgas_wq_day
        WHERE co  &gt;= #{stand}
             and TO_CHAR( data_time, 'YYYY' ) = #{year}
            AND region_code = #{regionCode}
        GROUP BY     TO_CHAR( data_time, 'YYYY-MM' ),monthNum
        ORDER BY
            monthNum ASC
    </select>
    <select id="findYearO38hOverDays" resultType="cn.cecep.talroad.vo.effective.MonthDataVo">
        SELECT
        COALESCE( SUM( o3_8h ), 0 ) o38hOverDays,
        extract(month from data_time ) as monthNum
        FROM
            q_envgas_wq_day
        WHERE o3_8h  &gt;= #{stand}
          and TO_CHAR( data_time, 'YYYY' ) = #{year}
          AND region_code = #{regionCode}
        GROUP BY  TO_CHAR( data_time, 'YYYY-MM' ),monthNum
        ORDER BY  monthNum ASC
    </select>


    <select id="selectByExceedDay" resultType="java.math.BigDecimal">
        SELECT
        COALESCE( SUM( CASE WHEN (${divisor} &gt;= ${targetValue}) THEN 1 ELSE 0 END ), 0 ) heavyDays
        FROM
            q_envgas_wq_day
        WHERE
        EXTRACT(YEAR FROM data_time) = EXTRACT(YEAR FROM NOW()) AND region_code =#{regionCode}
    </select>
    <select id="findYearHeavyDays" resultType="cn.cecep.talroad.vo.effective.MonthDataVo">
         SELECT
        COALESCE( SUM( fk_aqilevel_id   ), 0 ) heavyDays,
        extract(month from data_time ) as monthNum
        FROM
            q_envgas_wq_day
        WHERE fk_aqilevel_id &gt;= 5
          and TO_CHAR( data_time, 'YYYY' ) = #{year}
            AND region_code = #{regionCode}
         GROUP BY  TO_CHAR( data_time, 'YYYY-MM' ),monthNum
         ORDER BY  monthNum ASC
    </select>
    <select id="getPollutionDay" resultType="java.math.BigDecimal">
        SELECT  COUNT(*) FROM
        q_envgas_wq_day
        WHERE
        region_code = #{regionCode}
        AND EXTRACT(year FROM data_time) = EXTRACT(year FROM CURRENT_DATE)
        <if test="type == 1 or type =='1'">
            and fk_aqilevel_id &lt; 3
        </if>
        <if test="type == 2 or type =='2'">
            and fk_aqilevel_id > 4
        </if>
    </select>

<!--    <sql id="aqiCount">-->
<!--        COUNT(( windd = 0.0 AND fk_aqilevel_id = #{factorMinStand} ) OR NULL)   N,-->
<!--        COUNT(( windd = 22.5 AND fk_aqilevel_id = #{factorMinStand} )OR NULL)  NNE,-->
<!--        COUNT(( windd = 45.0 AND fk_aqilevel_id = #{factorMinStand}) OR NULL)  NE,-->
<!--        COUNT(( windd = 67.5 AND fk_aqilevel_id = #{factorMinStand}) OR NULL)  ENE,-->
<!--        COUNT(( windd = 90.0 AND fk_aqilevel_id = #{factorMinStand}) OR NULL)  E,-->
<!--        COUNT(( windd = 112.5 AND fk_aqilevel_id = #{factorMinStand})OR NULL) ESE,-->
<!--        COUNT(( windd = 135.0 AND fk_aqilevel_id = #{factorMinStand})OR NULL) SE,-->
<!--        COUNT(( windd = 157.5 AND fk_aqilevel_id = #{factorMinStand})OR NULL) SSE,-->
<!--        COUNT(( windd = 180.0 AND fk_aqilevel_id = #{factorMinStand})OR NULL) S,-->
<!--        COUNT(( windd = 202.5 AND fk_aqilevel_id = #{factorMinStand} )OR NULL)SSW,-->
<!--        COUNT(( windd = 225.0 AND fk_aqilevel_id = #{factorMinStand})OR NULL) SW,-->
<!--        COUNT(( windd = 247.5 AND fk_aqilevel_id = #{factorMinStand})OR NULL) WSW,-->
<!--        COUNT(( windd = 270.0 AND fk_aqilevel_id = #{factorMinStand})OR NULL) W,-->
<!--        COUNT(( windd = 292.5 AND fk_aqilevel_id = #{factorMinStand})OR NULL) WNW,-->
<!--        COUNT(( windd = 315.0 AND fk_aqilevel_id = #{factorMinStand})OR NULL) NW,-->
<!--        COUNT(( windd = 337.5 AND fk_aqilevel_id = #{factorMinStand})OR NULL) NNW-->
<!--    </sql>-->

<!--    <sql id="pm25WindCount">-->
<!--        COUNT(( windd = 0.0 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand} ) OR NULL)N,-->
<!--        COUNT(( windd = 22.5 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand}) OR NULL)NNE,-->
<!--        COUNT(( windd = 45.0 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand}) OR NULL)NE,-->
<!--        COUNT(( windd = 67.5 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand}) OR NULL)ENE,-->
<!--        COUNT(( windd = 90.0 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand}) OR NULL)E,-->
<!--        COUNT(( windd = 112.5 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) ESE,-->
<!--        COUNT(( windd = 135.0 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) SE,-->
<!--        COUNT(( windd = 157.5 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) SSE,-->
<!--        COUNT(( windd = 180.0 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) S,-->
<!--        COUNT(( windd = 202.5 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) SSW,-->
<!--        COUNT(( windd = 225.0 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) SW,-->
<!--        COUNT(( windd = 247.5 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) WSW,-->
<!--        COUNT(( windd = 270.0 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) W,-->
<!--        COUNT(( windd = 292.5 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) WNW,-->
<!--        COUNT(( windd = 315.0 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) NW,-->
<!--        COUNT(( windd = 337.5 AND pm25 &gt; #{factorMinStand} AND pm25 &lt;= #{factorMaxStand})OR NULL) NNW-->
<!--    </sql>-->

<!--    <sql id="pm10WindCount">-->
<!--        COUNT(( windd = 0.0 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand} )  OR NULL)N,-->
<!--        COUNT(( windd = 22.5 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand})  OR NULL)NNE,-->
<!--        COUNT(( windd = 45.0 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand})  OR NULL)NE,-->
<!--        COUNT(( windd = 67.5 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand})  OR NULL)ENE,-->
<!--        COUNT(( windd = 90.0 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand})  OR NULL)E,-->
<!--        COUNT(( windd = 112.5 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) ESE,-->
<!--        COUNT(( windd = 135.0 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) SE,-->
<!--        COUNT(( windd = 157.5 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) SSE,-->
<!--        COUNT(( windd = 180.0 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) S,-->
<!--        COUNT(( windd = 202.5 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) SSW,-->
<!--        COUNT(( windd = 225.0 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) SW,-->
<!--        COUNT(( windd = 247.5 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) WSW,-->
<!--        COUNT(( windd = 270.0 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) W,-->
<!--        COUNT(( windd = 292.5 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) WNW,-->
<!--        COUNT(( windd = 315.0 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) NW,-->
<!--        COUNT(( windd = 337.5 AND pm10 &gt; #{factorMinStand} AND pm10 &lt;= #{factorMaxStand}) OR NULL) NNW-->
<!--    </sql>-->

<!--    <sql id="so2WindCount">-->
<!--        COUNT(( windd = 0.0 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand} )  OR NULL)N,-->
<!--        COUNT(( windd = 22.5 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand})  OR NULL)NNE,-->
<!--        COUNT(( windd = 45.0 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand})  OR NULL)NE,-->
<!--        COUNT(( windd = 67.5 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand})  OR NULL)ENE,-->
<!--        COUNT(( windd = 90.0 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand})  OR NULL)E,-->
<!--        COUNT(( windd = 112.5 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) ESE,-->
<!--        COUNT(( windd = 135.0 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) SE,-->
<!--        COUNT(( windd = 157.5 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) SSE,-->
<!--        COUNT(( windd = 180.0 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) S,-->
<!--        COUNT(( windd = 202.5 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) SSW,-->
<!--        COUNT(( windd = 225.0 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) SW,-->
<!--        COUNT(( windd = 247.5 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) WSW,-->
<!--        COUNT(( windd = 270.0 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) W,-->
<!--        COUNT(( windd = 292.5 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) WNW,-->
<!--        COUNT(( windd = 315.0 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) NW,-->
<!--        COUNT(( windd = 337.5 AND so2 &gt; #{factorMinStand} AND so2 &lt;= #{factorMaxStand}) OR NULL) NNW-->
<!--    </sql>-->

<!--    <sql id="no2WindCount">-->
<!--        COUNT(( windd = 0.0 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand} )  OR NULL)N,-->
<!--        COUNT(( windd = 22.5 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand})  OR NULL)NNE,-->
<!--        COUNT(( windd = 45.0 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand})  OR NULL)NE,-->
<!--        COUNT(( windd = 67.5 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand})  OR NULL)ENE,-->
<!--        COUNT(( windd = 90.0 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand})  OR NULL)E,-->
<!--        COUNT(( windd = 112.5 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) ESE,-->
<!--        COUNT(( windd = 135.0 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) SE,-->
<!--        COUNT(( windd = 157.5 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) SSE,-->
<!--        COUNT(( windd = 180.0 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) S,-->
<!--        COUNT(( windd = 202.5 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) SSW,-->
<!--        COUNT(( windd = 225.0 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) SW,-->
<!--        COUNT(( windd = 247.5 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) WSW,-->
<!--        COUNT(( windd = 270.0 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) W,-->
<!--        COUNT(( windd = 292.5 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) WNW,-->
<!--        COUNT(( windd = 315.0 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) NW,-->
<!--        COUNT(( windd = 337.5 AND no2 &gt; #{factorMinStand} AND no2 &lt;= #{factorMaxStand}) OR NULL) NNW-->
<!--    </sql>-->

<!--    <sql id="coWindCount">-->
<!--        COUNT(( windd = 0.0 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand} )  OR NULL)N,-->
<!--        COUNT(( windd = 22.5 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand})  OR NULL)NNE,-->
<!--        COUNT(( windd = 45.0 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand})  OR NULL)NE,-->
<!--        COUNT(( windd = 67.5 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand})  OR NULL)ENE,-->
<!--        COUNT(( windd = 90.0 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand})  OR NULL)E,-->
<!--        COUNT(( windd = 112.5 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) ESE,-->
<!--        COUNT(( windd = 135.0 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) SE,-->
<!--        COUNT(( windd = 157.5 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) SSE,-->
<!--        COUNT(( windd = 180.0 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) S,-->
<!--        COUNT(( windd = 202.5 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) SSW,-->
<!--        COUNT(( windd = 225.0 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) SW,-->
<!--        COUNT(( windd = 247.5 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) WSW,-->
<!--        COUNT(( windd = 270.0 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) W,-->
<!--        COUNT(( windd = 292.5 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) WNW,-->
<!--        COUNT(( windd = 315.0 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) NW,-->
<!--        COUNT(( windd = 337.5 AND co &gt; #{factorMinStand} AND co &lt;= #{factorMaxStand}) OR NULL) NNW-->
<!--    </sql>-->

<!--    <sql id="o38hWindCount">-->
<!--        COUNT(( windd = 0.0 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand} )  OR NULL)N,-->
<!--        COUNT(( windd = 22.5 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand})  OR NULL)NNE,-->
<!--        COUNT(( windd = 45.0 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand})  OR NULL)NE,-->
<!--        COUNT(( windd = 67.5 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand})  OR NULL)ENE,-->
<!--        COUNT(( windd = 90.0 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand})  OR NULL)E,-->
<!--        COUNT(( windd = 112.5 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) ESE,-->
<!--        COUNT(( windd = 135.0 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) SE,-->
<!--        COUNT(( windd = 157.5 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) SSE,-->
<!--        COUNT(( windd = 180.0 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) S,-->
<!--        COUNT(( windd = 202.5 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) SSW,-->
<!--        COUNT(( windd = 225.0 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) SW,-->
<!--        COUNT(( windd = 247.5 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) WSW,-->
<!--        COUNT(( windd = 270.0 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) W,-->
<!--        COUNT(( windd = 292.5 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) WNW,-->
<!--        COUNT(( windd = 315.0 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) NW,-->
<!--        COUNT(( windd = 337.5 AND o3_8h &gt; #{factorMinStand} AND o3_8h &lt;= #{factorMaxStand}) OR NULL) NNW-->
<!--    </sql>-->

    <select id="queryAreaDayData" resultType="cn.cecep.talroad.vo.effective.AirQualityData">
        SELECT
        ROUND( a.aqi, 0 ) as aqi,
        a.data_time::date as monitorTime,
        ROUND( a.so2, 0 ) as so2,
        ROUND( a.no2, 0 ) as no2,
        ROUND( a.co, 1 ) as co,
        ROUND( a.o3, 0 ) as o3,
        ROUND( a.o3_8h, 0 ) as o38h,
        ROUND( a.pm25, 0 ) as pm25,
        ROUND( a.pm10, 0 ) as pm10,
        ROUND( a.winds, 0 ) as winds,
        ROUND( a.pres, 0 ) as pres,
        ROUND( a.humi, 0 ) as humi,
        ROUND( a.temp, 0 ) as temp,
        ROUND( a.windd, 0 ) as windd,
        ROUND( a.tvoc, 0 ) as tvoc,
        ROUND( a.tsp, 0 ) as tsp,
        ROUND( a.pmi, 2 ) as pmi,
        ROUND( a.iaqi_pm25, 0 ) as iaqiPm25,
        ROUND( a.iaqi_pm10, 0 ) as iaqiPm10,
        ROUND( a.iaqi_so2, 0 ) as iaqiSo2,
        ROUND( a.iaqi_no2, 0 ) as iaqiNo2,
        ROUND( a.iaqi_co, 0 ) as iaqiCO,
        ROUND( a.iaqi_o3, 0 ) as iaqiO3,
        ROUND( a.iaqi_o3_8h, 0 ) as iaqiO38h,
        ROUND( a.ipmi_pm25, 0 ) as ipmiPm25,
        ROUND( a.ipmi_pm10, 0 ) as ipmiPm10,
        ROUND( a.ipmi_so2, 0 ) as ipmiSo2,
        ROUND( a.ipmi_no2, 0 ) as ipmiNo2,
        ROUND( a.ipmi_co, 0 ) as ipmiCo,
        ROUND( a.ipmi_o3, 0 ) as ipmiO3,
        ROUND( a.ipmi_o3_8h, 0 ) as ipmiO38h,
        ROUND( a.o3_8h_max_value,0) as dayMaxO3,
        a.fk_aqilevel_id as fkAqiLevelId,
        a.fk_pollutant_id as fkPollutantId,
        a.aqi_type as fkAqiLevelName,
        a.aqi_color as fkAqiLevelColor,
        a.pollutant_name as fkPollutantName,
        d.region_name AS regionName,
        d.longitude as longitude,
        d.latitude as latitude,
        a.region_code AS regionCode,
        d.region_lever AS regionLevel
        FROM
        p_code_region as d
        LEFT JOIN (
        SELECT
        l.*,
        b.aqi_type,
        b.aqi_color,
        c.pollutant_name
        FROM
        q_envgas_wq_day as l
        LEFT JOIN s_envgas_aqilevel as b ON l.fk_aqilevel_id = b.pk_id
        LEFT JOIN s_envgas_pollutant as c ON l.fk_pollutant_id =  cast(c.pk_id as VARCHAR)
        <where>
            <if test="datetime!=null and datetime !=''">
                AND DATE_FORMAT(l.data_time,'%Y-%m-%d') = #{datetime}
            </if>
            <if test="startTime != null and startTime != ''">
                AND DATE_FORMAT(l.data_time,'%Y-%m-%d') &gt;= #{startTime}
            </if>
            <if test="endTime != null and endTime !=''">
                AND DATE_FORMAT(l.data_time,'%Y-%m-%d') &lt;= #{endTime}
            </if>
        </where>
        ) a ON d.region_code = a.region_code
        <where>
            <if test="regionCode != null and regionCode != ''">
                AND d.region_code = #{regionCode}
            </if>
            <if test="regionCodeList != null and regionCodeList.size > 0">
                AND d.region_code IN
                (
                <foreach collection="regionCodeList" item="item" separator=",">
                    #{item}
                </foreach>
                )
            </if>
        </where>
        ORDER BY a.data_time,d.region_code DESC
    </select>
</mapper>
