<?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="com.lightinit.hsdatashow.mapper.StWaterReservoirMapper">
  <resultMap id="BaseResultMap" type="com.lightinit.hsdatashow.entity.StWaterReservoir">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="region_id" jdbcType="BIGINT" property="regionId" />
    <result column="region_code" jdbcType="VARCHAR" property="regionCode" />
    <result column="reservoir_type" jdbcType="VARCHAR" property="reservoirType" />
    <result column="reservoir_name" jdbcType="VARCHAR" property="reservoirName" />
    <result column="reservoir_code" jdbcType="VARCHAR" property="reservoirCode" />
    <result column="reservoir_description" jdbcType="VARCHAR" property="reservoirDescription" />
    <result column="reservoir_area" jdbcType="DECIMAL" property="reservoirArea" />
    <result column="reservoir_depth" jdbcType="DECIMAL" property="reservoirDepth" />
    <result column="reservoir_impoundage" jdbcType="DECIMAL" property="reservoirImpoundage" />
    <result column="reservoir_chief" jdbcType="VARCHAR" property="reservoirChief" />
    <result column="impoundage_normal" jdbcType="DECIMAL" property="impoundageNormal" />
    <result column="impoundagel_warning" jdbcType="DECIMAL" property="impoundagelWarning" />
    <result column="impoundage_dead" jdbcType="DECIMAL" property="impoundageDead" />
    <result column="water_level_normal" jdbcType="DECIMAL" property="waterLevelNormal" />
    <result column="water_level_warning" jdbcType="DECIMAL" property="waterLevelWarning" />
    <result column="water_level_dead" jdbcType="DECIMAL" property="waterLevelDead" />
    <result column="water_quality" jdbcType="VARCHAR" property="waterQuality" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="created_at" jdbcType="TIMESTAMP" property="createdAt" />
    <result column="created_by" jdbcType="VARCHAR" property="createdBy" />
    <result column="deleted_at" jdbcType="TIMESTAMP" property="deletedAt" />
    <result column="deleted_by" jdbcType="VARCHAR" property="deletedBy" />
  </resultMap>
  <sql id="Example_Where_Clause">
    <where>
      <foreach collection="oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause">
    <where>
      <foreach collection="example.oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List">
    id, region_id, region_code, reservoir_type, reservoir_name, reservoir_code, reservoir_description, 
    reservoir_area, reservoir_depth, reservoir_impoundage, reservoir_chief, impoundage_normal, 
    impoundagel_warning, impoundage_dead, water_level_normal, water_level_warning, water_level_dead, 
    water_quality, remark, created_at, created_by, deleted_at, deleted_by
  </sql>
  <select id="selectByExample" parameterType="com.lightinit.hsdatashow.entity.StWaterReservoirExample" resultMap="BaseResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="Base_Column_List" />
    from st_water_reservoir
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from st_water_reservoir
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from st_water_reservoir
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <delete id="deleteByExample" parameterType="com.lightinit.hsdatashow.entity.StWaterReservoirExample">
    delete from st_water_reservoir
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </delete>
  <insert id="insert" parameterType="com.lightinit.hsdatashow.entity.StWaterReservoir">
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into st_water_reservoir (region_id, region_code, reservoir_type, 
      reservoir_name, reservoir_code, reservoir_description, 
      reservoir_area, reservoir_depth, reservoir_impoundage, 
      reservoir_chief, impoundage_normal, impoundagel_warning, 
      impoundage_dead, water_level_normal, water_level_warning, 
      water_level_dead, water_quality, remark, 
      created_at, created_by, deleted_at, 
      deleted_by)
    values (#{regionId,jdbcType=BIGINT}, #{regionCode,jdbcType=VARCHAR}, #{reservoirType,jdbcType=VARCHAR}, 
      #{reservoirName,jdbcType=VARCHAR}, #{reservoirCode,jdbcType=VARCHAR}, #{reservoirDescription,jdbcType=VARCHAR}, 
      #{reservoirArea,jdbcType=DECIMAL}, #{reservoirDepth,jdbcType=DECIMAL}, #{reservoirImpoundage,jdbcType=DECIMAL}, 
      #{reservoirChief,jdbcType=VARCHAR}, #{impoundageNormal,jdbcType=DECIMAL}, #{impoundagelWarning,jdbcType=DECIMAL}, 
      #{impoundageDead,jdbcType=DECIMAL}, #{waterLevelNormal,jdbcType=DECIMAL}, #{waterLevelWarning,jdbcType=DECIMAL}, 
      #{waterLevelDead,jdbcType=DECIMAL}, #{waterQuality,jdbcType=VARCHAR}, #{remark,jdbcType=VARCHAR}, 
      #{createdAt,jdbcType=TIMESTAMP}, #{createdBy,jdbcType=VARCHAR}, #{deletedAt,jdbcType=TIMESTAMP}, 
      #{deletedBy,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.lightinit.hsdatashow.entity.StWaterReservoir">
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into st_water_reservoir
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="regionId != null">
        region_id,
      </if>
      <if test="regionCode != null">
        region_code,
      </if>
      <if test="reservoirType != null">
        reservoir_type,
      </if>
      <if test="reservoirName != null">
        reservoir_name,
      </if>
      <if test="reservoirCode != null">
        reservoir_code,
      </if>
      <if test="reservoirDescription != null">
        reservoir_description,
      </if>
      <if test="reservoirArea != null">
        reservoir_area,
      </if>
      <if test="reservoirDepth != null">
        reservoir_depth,
      </if>
      <if test="reservoirImpoundage != null">
        reservoir_impoundage,
      </if>
      <if test="reservoirChief != null">
        reservoir_chief,
      </if>
      <if test="impoundageNormal != null">
        impoundage_normal,
      </if>
      <if test="impoundagelWarning != null">
        impoundagel_warning,
      </if>
      <if test="impoundageDead != null">
        impoundage_dead,
      </if>
      <if test="waterLevelNormal != null">
        water_level_normal,
      </if>
      <if test="waterLevelWarning != null">
        water_level_warning,
      </if>
      <if test="waterLevelDead != null">
        water_level_dead,
      </if>
      <if test="waterQuality != null">
        water_quality,
      </if>
      <if test="remark != null">
        remark,
      </if>
      <if test="createdAt != null">
        created_at,
      </if>
      <if test="createdBy != null">
        created_by,
      </if>
      <if test="deletedAt != null">
        deleted_at,
      </if>
      <if test="deletedBy != null">
        deleted_by,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="regionId != null">
        #{regionId,jdbcType=BIGINT},
      </if>
      <if test="regionCode != null">
        #{regionCode,jdbcType=VARCHAR},
      </if>
      <if test="reservoirType != null">
        #{reservoirType,jdbcType=VARCHAR},
      </if>
      <if test="reservoirName != null">
        #{reservoirName,jdbcType=VARCHAR},
      </if>
      <if test="reservoirCode != null">
        #{reservoirCode,jdbcType=VARCHAR},
      </if>
      <if test="reservoirDescription != null">
        #{reservoirDescription,jdbcType=VARCHAR},
      </if>
      <if test="reservoirArea != null">
        #{reservoirArea,jdbcType=DECIMAL},
      </if>
      <if test="reservoirDepth != null">
        #{reservoirDepth,jdbcType=DECIMAL},
      </if>
      <if test="reservoirImpoundage != null">
        #{reservoirImpoundage,jdbcType=DECIMAL},
      </if>
      <if test="reservoirChief != null">
        #{reservoirChief,jdbcType=VARCHAR},
      </if>
      <if test="impoundageNormal != null">
        #{impoundageNormal,jdbcType=DECIMAL},
      </if>
      <if test="impoundagelWarning != null">
        #{impoundagelWarning,jdbcType=DECIMAL},
      </if>
      <if test="impoundageDead != null">
        #{impoundageDead,jdbcType=DECIMAL},
      </if>
      <if test="waterLevelNormal != null">
        #{waterLevelNormal,jdbcType=DECIMAL},
      </if>
      <if test="waterLevelWarning != null">
        #{waterLevelWarning,jdbcType=DECIMAL},
      </if>
      <if test="waterLevelDead != null">
        #{waterLevelDead,jdbcType=DECIMAL},
      </if>
      <if test="waterQuality != null">
        #{waterQuality,jdbcType=VARCHAR},
      </if>
      <if test="remark != null">
        #{remark,jdbcType=VARCHAR},
      </if>
      <if test="createdAt != null">
        #{createdAt,jdbcType=TIMESTAMP},
      </if>
      <if test="createdBy != null">
        #{createdBy,jdbcType=VARCHAR},
      </if>
      <if test="deletedAt != null">
        #{deletedAt,jdbcType=TIMESTAMP},
      </if>
      <if test="deletedBy != null">
        #{deletedBy,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <select id="countByExample" parameterType="com.lightinit.hsdatashow.entity.StWaterReservoirExample" resultType="java.lang.Long">
    select count(*) from st_water_reservoir
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </select>
  <update id="updateByExampleSelective" parameterType="map">
    update st_water_reservoir
    <set>
      <if test="record.id != null">
        id = #{record.id,jdbcType=BIGINT},
      </if>
      <if test="record.regionId != null">
        region_id = #{record.regionId,jdbcType=BIGINT},
      </if>
      <if test="record.regionCode != null">
        region_code = #{record.regionCode,jdbcType=VARCHAR},
      </if>
      <if test="record.reservoirType != null">
        reservoir_type = #{record.reservoirType,jdbcType=VARCHAR},
      </if>
      <if test="record.reservoirName != null">
        reservoir_name = #{record.reservoirName,jdbcType=VARCHAR},
      </if>
      <if test="record.reservoirCode != null">
        reservoir_code = #{record.reservoirCode,jdbcType=VARCHAR},
      </if>
      <if test="record.reservoirDescription != null">
        reservoir_description = #{record.reservoirDescription,jdbcType=VARCHAR},
      </if>
      <if test="record.reservoirArea != null">
        reservoir_area = #{record.reservoirArea,jdbcType=DECIMAL},
      </if>
      <if test="record.reservoirDepth != null">
        reservoir_depth = #{record.reservoirDepth,jdbcType=DECIMAL},
      </if>
      <if test="record.reservoirImpoundage != null">
        reservoir_impoundage = #{record.reservoirImpoundage,jdbcType=DECIMAL},
      </if>
      <if test="record.reservoirChief != null">
        reservoir_chief = #{record.reservoirChief,jdbcType=VARCHAR},
      </if>
      <if test="record.impoundageNormal != null">
        impoundage_normal = #{record.impoundageNormal,jdbcType=DECIMAL},
      </if>
      <if test="record.impoundagelWarning != null">
        impoundagel_warning = #{record.impoundagelWarning,jdbcType=DECIMAL},
      </if>
      <if test="record.impoundageDead != null">
        impoundage_dead = #{record.impoundageDead,jdbcType=DECIMAL},
      </if>
      <if test="record.waterLevelNormal != null">
        water_level_normal = #{record.waterLevelNormal,jdbcType=DECIMAL},
      </if>
      <if test="record.waterLevelWarning != null">
        water_level_warning = #{record.waterLevelWarning,jdbcType=DECIMAL},
      </if>
      <if test="record.waterLevelDead != null">
        water_level_dead = #{record.waterLevelDead,jdbcType=DECIMAL},
      </if>
      <if test="record.waterQuality != null">
        water_quality = #{record.waterQuality,jdbcType=VARCHAR},
      </if>
      <if test="record.remark != null">
        remark = #{record.remark,jdbcType=VARCHAR},
      </if>
      <if test="record.createdAt != null">
        created_at = #{record.createdAt,jdbcType=TIMESTAMP},
      </if>
      <if test="record.createdBy != null">
        created_by = #{record.createdBy,jdbcType=VARCHAR},
      </if>
      <if test="record.deletedAt != null">
        deleted_at = #{record.deletedAt,jdbcType=TIMESTAMP},
      </if>
      <if test="record.deletedBy != null">
        deleted_by = #{record.deletedBy,jdbcType=VARCHAR},
      </if>
    </set>
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map">
    update st_water_reservoir
    set id = #{record.id,jdbcType=BIGINT},
      region_id = #{record.regionId,jdbcType=BIGINT},
      region_code = #{record.regionCode,jdbcType=VARCHAR},
      reservoir_type = #{record.reservoirType,jdbcType=VARCHAR},
      reservoir_name = #{record.reservoirName,jdbcType=VARCHAR},
      reservoir_code = #{record.reservoirCode,jdbcType=VARCHAR},
      reservoir_description = #{record.reservoirDescription,jdbcType=VARCHAR},
      reservoir_area = #{record.reservoirArea,jdbcType=DECIMAL},
      reservoir_depth = #{record.reservoirDepth,jdbcType=DECIMAL},
      reservoir_impoundage = #{record.reservoirImpoundage,jdbcType=DECIMAL},
      reservoir_chief = #{record.reservoirChief,jdbcType=VARCHAR},
      impoundage_normal = #{record.impoundageNormal,jdbcType=DECIMAL},
      impoundagel_warning = #{record.impoundagelWarning,jdbcType=DECIMAL},
      impoundage_dead = #{record.impoundageDead,jdbcType=DECIMAL},
      water_level_normal = #{record.waterLevelNormal,jdbcType=DECIMAL},
      water_level_warning = #{record.waterLevelWarning,jdbcType=DECIMAL},
      water_level_dead = #{record.waterLevelDead,jdbcType=DECIMAL},
      water_quality = #{record.waterQuality,jdbcType=VARCHAR},
      remark = #{record.remark,jdbcType=VARCHAR},
      created_at = #{record.createdAt,jdbcType=TIMESTAMP},
      created_by = #{record.createdBy,jdbcType=VARCHAR},
      deleted_at = #{record.deletedAt,jdbcType=TIMESTAMP},
      deleted_by = #{record.deletedBy,jdbcType=VARCHAR}
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="com.lightinit.hsdatashow.entity.StWaterReservoir">
    update st_water_reservoir
    <set>
      <if test="regionId != null">
        region_id = #{regionId,jdbcType=BIGINT},
      </if>
      <if test="regionCode != null">
        region_code = #{regionCode,jdbcType=VARCHAR},
      </if>
      <if test="reservoirType != null">
        reservoir_type = #{reservoirType,jdbcType=VARCHAR},
      </if>
      <if test="reservoirName != null">
        reservoir_name = #{reservoirName,jdbcType=VARCHAR},
      </if>
      <if test="reservoirCode != null">
        reservoir_code = #{reservoirCode,jdbcType=VARCHAR},
      </if>
      <if test="reservoirDescription != null">
        reservoir_description = #{reservoirDescription,jdbcType=VARCHAR},
      </if>
      <if test="reservoirArea != null">
        reservoir_area = #{reservoirArea,jdbcType=DECIMAL},
      </if>
      <if test="reservoirDepth != null">
        reservoir_depth = #{reservoirDepth,jdbcType=DECIMAL},
      </if>
      <if test="reservoirImpoundage != null">
        reservoir_impoundage = #{reservoirImpoundage,jdbcType=DECIMAL},
      </if>
      <if test="reservoirChief != null">
        reservoir_chief = #{reservoirChief,jdbcType=VARCHAR},
      </if>
      <if test="impoundageNormal != null">
        impoundage_normal = #{impoundageNormal,jdbcType=DECIMAL},
      </if>
      <if test="impoundagelWarning != null">
        impoundagel_warning = #{impoundagelWarning,jdbcType=DECIMAL},
      </if>
      <if test="impoundageDead != null">
        impoundage_dead = #{impoundageDead,jdbcType=DECIMAL},
      </if>
      <if test="waterLevelNormal != null">
        water_level_normal = #{waterLevelNormal,jdbcType=DECIMAL},
      </if>
      <if test="waterLevelWarning != null">
        water_level_warning = #{waterLevelWarning,jdbcType=DECIMAL},
      </if>
      <if test="waterLevelDead != null">
        water_level_dead = #{waterLevelDead,jdbcType=DECIMAL},
      </if>
      <if test="waterQuality != null">
        water_quality = #{waterQuality,jdbcType=VARCHAR},
      </if>
      <if test="remark != null">
        remark = #{remark,jdbcType=VARCHAR},
      </if>
      <if test="createdAt != null">
        created_at = #{createdAt,jdbcType=TIMESTAMP},
      </if>
      <if test="createdBy != null">
        created_by = #{createdBy,jdbcType=VARCHAR},
      </if>
      <if test="deletedAt != null">
        deleted_at = #{deletedAt,jdbcType=TIMESTAMP},
      </if>
      <if test="deletedBy != null">
        deleted_by = #{deletedBy,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.lightinit.hsdatashow.entity.StWaterReservoir">
    update st_water_reservoir
    set region_id = #{regionId,jdbcType=BIGINT},
      region_code = #{regionCode,jdbcType=VARCHAR},
      reservoir_type = #{reservoirType,jdbcType=VARCHAR},
      reservoir_name = #{reservoirName,jdbcType=VARCHAR},
      reservoir_code = #{reservoirCode,jdbcType=VARCHAR},
      reservoir_description = #{reservoirDescription,jdbcType=VARCHAR},
      reservoir_area = #{reservoirArea,jdbcType=DECIMAL},
      reservoir_depth = #{reservoirDepth,jdbcType=DECIMAL},
      reservoir_impoundage = #{reservoirImpoundage,jdbcType=DECIMAL},
      reservoir_chief = #{reservoirChief,jdbcType=VARCHAR},
      impoundage_normal = #{impoundageNormal,jdbcType=DECIMAL},
      impoundagel_warning = #{impoundagelWarning,jdbcType=DECIMAL},
      impoundage_dead = #{impoundageDead,jdbcType=DECIMAL},
      water_level_normal = #{waterLevelNormal,jdbcType=DECIMAL},
      water_level_warning = #{waterLevelWarning,jdbcType=DECIMAL},
      water_level_dead = #{waterLevelDead,jdbcType=DECIMAL},
      water_quality = #{waterQuality,jdbcType=VARCHAR},
      remark = #{remark,jdbcType=VARCHAR},
      created_at = #{createdAt,jdbcType=TIMESTAMP},
      created_by = #{createdBy,jdbcType=VARCHAR},
      deleted_at = #{deletedAt,jdbcType=TIMESTAMP},
      deleted_by = #{deletedBy,jdbcType=VARCHAR}
    where id = #{id,jdbcType=BIGINT}
  </update>

        <!--防汛抗旱概况首页-->
  <!--汛期预警-->
  <select id="getxunqiyujing" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
   SELECT B.id,B.reservoir_name AS `name`,B.water_level_warning AS `temp1`,B.water_level AS `temp2`,(B.water_level-B.water_level_warning) fag
  FROM
  (SELECT DISTINCT(swr.id),swr.reservoir_name ,swr.water_level_warning ,swri.water_level ,swri.capture_time
  FROM st_water_reservoir swr
  INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
  INNER JOIN
  (
  SELECT reservoir_id,MAX(capture_time) AS maxtime
  FROM st_water_reservoir_impoundage
  GROUP BY reservoir_id
  ) AS A ON swri.reservoir_id=A.reservoir_id AND swri.capture_time=A.maxtime
  LEFT JOIN region r ON swr.region_id=r.id
               WHERE r.parent_id  =2
  ) B
  ORDER BY fag DESC
  LIMIT 0,6
  </select>

 <!--今日暴雨-->
  <select id="getjinribaoyu" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
  SELECT DISTINCT(swr.station_id)AS id,swrms.station_name AS `name`,swr.rainfall AS `value`
  FROM st_water_rainfall swr
  INNER JOIN st_water_rainfall_monitoring_station swrms ON swr.station_id=swrms.id
  WHERE DATE(swr.capture_time)=DATE(NOW())
  ORDER BY swr.rainfall DESC
  LIMIT 6
  </select>

  <!--今日降雨变化-->
  <select id="getjinrijiangyubianhua" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
  SELECT DISTINCT(swr.capture_time),swr.rainfall AS temp1,CONCAT(HOUR(swr.capture_time),':00') AS temp2
  FROM st_water_rainfall swr
  INNER JOIN region r ON r.id=swr.region_id AND r.parent_id=2
  WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨' AND
   DATE(swr.capture_time)=DATE(NOW()) AND DAY(swr.capture_time)=DAY(NOW())
  </select>

  <!--全市可用水总量-->
  <select id="getquanshikeyongzongshuiliang" resultType="object">
    SELECT water_capacity
    FROM st_water_available
    ORDER BY  capture_time DESC
    LIMIT 1
  </select>

    <!--全市蓄水总量-->
  <select id="getquanshixushuizongliang" resultType="object">
    SELECT SUM(impoundage) FROM(
    SELECT DISTINCT(swr.id),swri.impoundage,swri.capture_time AS `date`
    FROM st_water_reservoir swr
    INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
    INNER JOIN
    (
    SELECT reservoir_id,MAX(capture_time) AS maxtime
    FROM st_water_reservoir_impoundage
    GROUP BY reservoir_id
    ) AS A ON swri.reservoir_id=A.reservoir_id AND swri.capture_time=A.maxtime) AS B
  </select>


  <!--全市监测站总数-->
  <select id="getquanshijianchezhanzongshu" resultType="object">
    SELECT COUNT(*)
      FROM st_water_flood_control_monitoring_station
</select>

<!--监测站构成图-->
  <select id="getjiancezhangouchengtu" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
    SELECT DISTINCT(station_type) AS `name`,COUNT(*) AS `value`
    FROM st_water_flood_control_monitoring_station swfcms
    INNER JOIN region r ON r.id=swfcms.region_id
    WHERE   r.parent_id=2
    GROUP BY station_type
</select>

<!--各区县构成图·监测站-->
  <select id="getgeqvxianguochengtu" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
    SELECT r.id,r.region_name AS `name`,swfcms.station_type AS 'temp1',COUNT(*) AS `value`
    FROM st_water_flood_control_monitoring_station swfcms
    INNER JOIN region r ON r.id=swfcms.region_id
    WHERE   r.parent_id=2 AND
    swfcms.station_type=#{stationType}
    GROUP BY r.id
</select>

  <!-- <select id="getgeqvxianguochengtu" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
     SELECT id,region_name as`name`,GROUP_CONCAT(station_type,'-',co,'个') AS `temp1`,COUNT(DISTINCT(id)) AS `value`
    FROM(
    SELECT r.id,r.region_name,COUNT(*) AS co,station_type
    FROM st_water_flood_control_monitoring_station swfcms
    INNER JOIN region r ON r.id=swfcms.region_id
    GROUP BY swfcms.region_id,swfcms.station_type
    ) AS A
    GROUP BY region_name
</select>-->





    <!--各水库可用水量-->
    <select id="getgeshuikukeyongshuiliang" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
      SELECT DISTINCT(swr.id),swr.reservoir_name AS `name`,swri.impoundage_usable AS `value`,swri.capture_time AS `date`
    FROM st_water_reservoir swr
    INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
    INNER JOIN
    (
    SELECT reservoir_id,MAX(capture_time) AS maxtime
    FROM st_water_reservoir_impoundage
    GROUP BY reservoir_id
    ) AS A ON swri.reservoir_id=A.reservoir_id AND swri.capture_time=A.maxtime
    INNER JOIN region r ON swr.region_id=r.id
               WHERE r.parent_id  =2
    </select>

  <!--各水库各月可用水量变化-->
    <select id="getshuikugeyueshuiliang" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
      SELECT DISTINCT(swr.id),swr.reservoir_name AS `name`,swri.impoundage_usable,swri.capture_year,swri.capture_month,GROUP_CONCAT(capture_month,'-',impoundage_usable) AS `value`,MAX(capture_month) AS temp1
        FROM st_water_reservoir swr
        INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
        INNER JOIN region r ON swr.region_id=r.id
               AND r.parent_id  =2
        WHERE swri.capture_year=YEAR(NOW())
        GROUP BY swr.id
        ORDER BY swri.capture_month
    </select>



  <!--各水库蓄水量-->
  <select id="getgeshuikuxushuiliang" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
    SELECT DISTINCT(swr.id),swr.reservoir_name AS `name`,swri.impoundage AS `value`,swri.capture_time AS `date`
    FROM st_water_reservoir swr
    INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
    INNER JOIN
    (
    SELECT reservoir_id,MAX(capture_time) AS maxtime
    FROM st_water_reservoir_impoundage
    GROUP BY reservoir_id
    ) AS A ON swri.reservoir_id=A.reservoir_id AND swri.capture_time=A.maxtime
    INNER JOIN region r ON r.id=swr.region_id
    WHERE r.parent_id=2
  </select>

  <!--各水库蓄水量变化|本市各水库本年蓄水变化-->
  <select id="getgeshuikuxushuiliangbianhua" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
     SELECT DISTINCT(swr.id),swr.reservoir_name AS `name`,swri.impoundage,swri.capture_year,swri.capture_month,GROUP_CONCAT(capture_month,'-',impoundage) AS `value`,MAX(capture_month) AS temp1
    FROM st_water_reservoir swr
    INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
	LEFT JOIN region r ON r.id=swr.region_id
    WHERE swri.capture_year=YEAR(NOW()) AND r.parent_id=2
    GROUP BY swr.id
    ORDER BY swri.capture_month
  </select>

  <!--全市工程总数-->
  <select id="getquanshigongchengzongshu" resultType="object">
   SELECT COUNT(*)
  FROM st_water_project
  WHERE YEAR(created_at)=YEAR(NOW())
  </select>

  <!--工程构成图-->
  <select id="getgongchengguozaotu" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
    SELECT DISTINCT(project_type) AS `name`,COUNT(*) AS `value`
    FROM st_water_project
    GROUP BY project_type
  </select>

  <!--各区县构成图·工程-->
  <select id="getqvxiangongchenggouzaotu" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
     SELECT r.id,r.region_name as`name`,swp.project_type AS `temp1`,COUNT(*) AS `value`
    FROM
    st_water_project swp
    INNER JOIN region r ON r.id =swp.region_id
    WHERE r.parent_id=2 AND
	swp.project_type=#{projectType}
	GROUP BY r.id
  </select>

<!--
 <select id="getqvxiangongchenggouzaotu" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
     SELECT id,region_name as`name`,GROUP_CONCAT(project_type,'-',co,'个') AS `temp1`,COUNT(DISTINCT(id)) AS `value`
    FROM(
    SELECT r.id,r.region_name,COUNT(*) AS co,project_type
    FROM st_water_project swp
    INNER JOIN region r ON r.id=swp.region_id
    GROUP BY swp.region_id,swp.project_type
    ) AS A
    GROUP BY region_name
  </select>

-->


                    <!--水情-->

    <!--本市各水库蓄水量与多年均值比较-->
    <select id="getbenshigeshuikuxushuiliangyuduonianjunzhibijiao" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
      SELECT B.id,B.name,B.value AS temp1,B.date t1,C.date t2,C.value AS temp2
      FROM (
      SELECT DISTINCT(swr.id),swr.reservoir_name AS `name`,swri.impoundage AS `value`,swri.capture_time AS `date`
      FROM st_water_reservoir swr
      INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
      INNER JOIN
      (
      SELECT reservoir_id,MAX(capture_time) AS maxtime
      FROM st_water_reservoir_impoundage
      GROUP BY reservoir_id
      ) AS A ON swri.reservoir_id=A.reservoir_id AND swri.capture_time=A.maxtime
      INNER JOIN region r ON r.id=swr.region_id
      WHERE r.parent_id=2
      ) AS B
      LEFT JOIN
      (
      SELECT z.id,z.name,AVG(Z.impoundage)AS `value`,Z.date
      FROM(
      SELECT DISTINCT(swr.id),swr.reservoir_name AS `name`,swri.impoundage,swri.capture_time AS `date`
      FROM st_water_reservoir swr
      INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
      INNER JOIN region r ON r.id=swr.region_id
      WHERE r.parent_id=2 AND YEAR(swri.capture_time)&lt;YEAR(NOW())
      GROUP BY YEAR(swri.capture_time)
      ) AS Z
      ) AS C ON B.id=C.id
    </select>


   <!--本市各水库蓄水能力-->
      <select id="getgeshigeshuikuxushuinengli" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
          SELECT DISTINCT(swr.id),swr.reservoir_name AS `name`,swr.reservoir_impoundage `temp1`,swr.impoundage_dead AS `temp2`,swri.impoundage_usable AS `temp3`,swr.water_level_warning ,swri.water_level ,swri.capture_time,r.region_name
            FROM st_water_reservoir swr
            INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
            INNER JOIN
            (
            SELECT reservoir_id,MAX(capture_time) AS maxtime
            FROM st_water_reservoir_impoundage
            GROUP BY reservoir_id
            ) AS A ON swri.reservoir_id=A.reservoir_id AND swri.capture_time=A.maxtime
            LEFT JOIN region r ON swr.region_id=r.id
               WHERE r.parent_id  =2
      </select>


    <!--全省各水库蓄水量变化（同···》各水库蓄水量变化|本市各水库本年蓄水变化）-->
    <select id="getquanshenggeshuikuxushuiliangbianhua" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
        SELECT DISTINCT(swr.id),swr.reservoir_name AS `name`,swri.impoundage,swri.capture_year,swri.capture_month,GROUP_CONCAT(capture_month,'-',impoundage) AS `value`,MAX(capture_month) AS temp1
        FROM st_water_reservoir swr
        INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
        LEFT JOIN region r ON r.id=swr.region_id
        WHERE swri.capture_year=YEAR(NOW())
        GROUP BY swr.id
        ORDER BY swri.capture_month
    </select>


    <!--全省各水库蓄水能力（同···》本市各水库蓄水能力）-->
    <select id="getquanshenggeshuikuxushuinengli" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
        SELECT DISTINCT(swr.id),swr.reservoir_name AS `name`,swr.reservoir_impoundage `temp1`,swr.impoundage_dead AS `temp3`,swri.impoundage_usable AS `temp2`,swr.water_level_warning ,swri.water_level ,swri.capture_time,r.region_name
        FROM st_water_reservoir swr
        INNER JOIN st_water_reservoir_impoundage swri ON swr.id=swri.reservoir_id
        INNER JOIN
        (
        SELECT reservoir_id,MAX(capture_time) AS maxtime
        FROM st_water_reservoir_impoundage
        GROUP BY reservoir_id
        ) AS A ON swri.reservoir_id=A.reservoir_id AND swri.capture_time=A.maxtime
        LEFT JOIN region r ON swr.region_id=r.id
    </select>


<!--降雨量分析比较-->
    <select id="getjiangyuliangfenxibijiao" resultType="com.lightinit.hsdatashow.model.topic.water.STRainModel">
      SELECT r.id,
      r.region_name,
      a.capture_year,
      a.capture_month,
      CONCAT(a.capture_year,'年',a.capture_month,'月') AS timeText,
      a.rainfall,
      (SELECT AVG(b.rainfall) FROM st_water_rainfall b WHERE b.capture_year = a.capture_year - 1 AND b.capture_month = a.capture_month) AS last_year_month_rainfall,
      (SELECT AVG(c.rainfall) FROM st_water_rainfall c WHERE c.capture_year &lt; a.capture_year AND c.capture_month = a.capture_month) AS long_ago_year_month_average_rainfall
      FROM st_water_rainfall a
      INNER JOIN region r ON r.id=a.region_id AND r.parent_id=2 OR r.id=2
      WHERE a.capture_year IS NOT NULL
      <if test="id !=null">
        AND r.id=#{id}
      </if>
      <if test="fromTime !=null">
        AND DATE(capture_time)>=#{fromTime}
      </if>
      <if test="toTime !=null">
        AND DATE(capture_time) &lt;=#{toTime}
      </if>
      ORDER BY a.capture_year, a.capture_month
      limit #{pageNo},#{pageSize}
    </select>

  <!--降雨量分析比较总数-->
    <select id="getjiangyuliangfenxibijiaoTotal" resultType="int">
      SELECT count(*)
      FROM (
        SELECT r.id,
        r.region_name,
        a.capture_year,
        a.capture_month,
        CONCAT(a.capture_year,'年',a.capture_month,'月') AS timeText,
        a.rainfall,
        (SELECT AVG(b.rainfall) FROM st_water_rainfall b WHERE b.capture_year = a.capture_year - 1 AND b.capture_month = a.capture_month) AS last_year_month_rainfall,
        (SELECT AVG(c.rainfall) FROM st_water_rainfall c WHERE c.capture_year &lt; a.capture_year AND c.capture_month = a.capture_month) AS long_ago_year_month_average_rainfall
        FROM st_water_rainfall a
        INNER JOIN region r ON r.id=a.region_id
        WHERE a.capture_year IS NOT NULL AND r.parent_id=2 OR r.id=2
      <if test="id !=null">
        AND r.id=#{id}
      </if>
      <if test="fromTime !=null">
        AND DATE(capture_time)>=#{fromTime}
      </if>
      <if test="toTime !=null">
        AND DATE(capture_time) &lt;=#{toTime}
      </if>
        ) AS  A
    </select>




                                        <!--旱情-->
  <!--全市上游本月来水总量-->
    <select id="getbenshishangyoubenyuelaishuizongliang" resultType="object">
       SELECT water_quantity
       FROM st_water_inflow swi
       WHERE  MONTH(swi.capture_time)=MONTH(NOW()) AND YEAR(swi.capture_time)=YEAR(NOW())
    </select>

    <!--全市下月预计来水总量-->
    <select id="getquanshixiayueyujilaishuizongliang" resultType="object">
      SELECT water_quantity
       FROM st_water_inflow_estimate swi
       WHERE  MONTH(DATE_ADD(NOW(),INTERVAL 1 MONTH))=estimate_month
        AND
      YEAR(DATE_ADD(NOW(),INTERVAL 1 MONTH))=estimate_year
    </select>

    <!--全市下月预测可用总水量-->
    <select id="getquanshixiayueyucekeyongzongshuiliang" resultType="object">
      SELECT water_capacity
       FROM st_water_available_estimate
       WHERE  MONTH(DATE_ADD(NOW(),INTERVAL 1 MONTH))=estimate_month
       AND
      YEAR(DATE_ADD(NOW(),INTERVAL 1 MONTH))=estimate_year
    </select>

    <!--连续干旱地区排行榜-->
    <select id="getlianxuganhantu" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
      SELECT DISTINCT(r.id),r.region_name AS `name`,ROUND(SUM(swr.rainfall),2) AS `value`,swr.rainfall_type
        FROM st_water_rainfall swr
         INNER JOIN region r ON r.id=swr.region_id AND r.parent_id=2
        WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨' AND
         YEAR(swr.capture_time)=YEAR(NOW())
         GROUP BY r.id
         ORDER BY `value`
    </select>




                    <!--雨情-->
    <!--今日降雨量-->
    <select id="getjiangyuliang" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
        SELECT  ROUND(IFNULL(AVG(rainfall),0),2) as temp1
         FROM st_water_rainfall swr
         INNER JOIN region r ON r.id=swr.region_id AND r.id=2
        WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨' AND
              DATE(NOW()) &lt;=capture_time AND capture_time  &lt;NOW()
        UNION ALL
        SELECT  ROUND(IFNULL(AVG(rainfall),0),2) as temp1
         FROM st_water_rainfall swr
         INNER JOIN region r ON r.id=swr.region_id AND r.id=2
        WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨' AND
              DATE_ADD(NOW(),INTERVAL -1 HOUR)  &lt;=capture_time AND capture_time  &lt;NOW()
        UNION ALL
        SELECT  ROUND(IFNULL(AVG(rainfall),0),2) as temp1
         FROM st_water_rainfall swr
         INNER JOIN region r ON r.id=swr.region_id AND r.id=2
        WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨' AND
              DATE_ADD(NOW(),INTERVAL -3 HOUR)  &lt;=capture_time AND capture_time  &lt;NOW()
        UNION ALL
        SELECT  ROUND(IFNULL(AVG(rainfall),0),2) as temp1
        FROM st_water_rainfall swr
         INNER JOIN region r ON r.id=swr.region_id AND r.id=2
        WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨' AND
              DATE_ADD(NOW(),INTERVAL -6 HOUR)  &lt;=capture_time AND capture_time  &lt;NOW()
    </select>

    <!--本市本月降雨变化-->
    <select id="getbenshibenyuejiangyubianhua" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
       SELECT DISTINCT(DAY(swr.capture_time)) AS temp2,ROUND(SUM(swr.rainfall),2) AS temp1, swr.capture_time,swr.rainfall_type
          FROM st_water_rainfall swr
          INNER JOIN region r ON r.id=swr.region_id AND r.id=2
          WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨' AND
         YEAR(swr.capture_time)=YEAR(NOW()) AND MONTH(swr.capture_time)=MONTH(NOW())
         GROUP BY temp2
    </select>

   <!--今年降雨变化-->
      <select id="getjinnianjiangyubianhua" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
         SELECT DISTINCT(MONTH(swr.capture_time)) AS temp2,ROUND(SUM(swr.rainfall),2) AS temp1,swr.rainfall_type
        FROM st_water_rainfall swr
         INNER JOIN region r ON r.id=swr.region_id AND r.id=2
        WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨' AND
         YEAR(swr.capture_time)=YEAR(NOW())
         GROUP BY temp2
      </select>

  <!--今日降雨与周边地市比较-->
      <select id="getjinrijiangyuyuzhoubiandishibijiao" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
         SELECT r.id,ROUND(SUM(swr.rainfall),2) AS temp1,r.region_name AS `name`
        FROM st_water_rainfall swr
        INNER JOIN region r ON r.id=swr.region_id AND r.parent_id=2
        WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨'
        AND DATE(swr.capture_time)=DATE(NOW()) AND DAY(swr.capture_time)=DAY(NOW())
         GROUP BY r.id
      </select>

      <!--本月降雨与周边地市比较-->
      <select id="getbenyuejiangyuyuzhoubiandishibijiao" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
         SELECT ROUND(SUM(swr.rainfall),2) AS temp1,swr.rainfall_type, r.id,r.region_name AS `name`
          FROM st_water_rainfall swr
          INNER JOIN region r ON r.id=swr.region_id AND r.parent_id=2
          WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨' AND
         YEAR(swr.capture_time)=YEAR(NOW()) AND MONTH(swr.capture_time)=MONTH(NOW())
         GROUP BY r.id
      </select>


  <!--今年降雨与周边地市比较-->
  <select id="getjinnianjiangyuyuzhoubiandishibijiao" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
     SELECT ROUND(SUM(swr.rainfall),2) AS temp1,swr.rainfall_type,r.region_name AS `name`,r.id,r.parent_id
    FROM st_water_rainfall swr
    INNER JOIN region r ON r.id=swr.region_id AND r.parent_id=2
    WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨' AND
    YEAR(swr.capture_time)=YEAR(NOW())
    GROUP BY r.id
  </select>

  <!--历年降雨与周边地市比较-->
  <select id="getlinianjiangyuyuzhoubianbijiao" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
     SELECT id,`name`,GROUP_CONCAT(t2,'-',t1) AS temp1,MAX(t2) AS temp2,MIN(t2) AS temp3
     FROM(
     SELECT DISTINCT(YEAR(swr.capture_time)) AS `t2`,r.id,r.region_name AS `name`, ROUND(SUM(swr.rainfall),2) AS t1,swr.rainfall_type
              FROM st_water_rainfall swr
              INNER JOIN region r ON r.id=swr.region_id AND r.parent_id=2
              WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨'
             GROUP BY r.id,YEAR(swr.capture_time)
    ) AS A
  GROUP BY id
  </select>

  <!--本市历年降雨变化-->
  <select id="getbenshilinianjiangyubianhua" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
    SELECT DISTINCT(YEAR(swr.capture_time)) AS `temp1`,r.region_name AS `name`, ROUND(SUM(swr.rainfall),2) AS temp2,swr.rainfall_type
          FROM st_water_rainfall swr
          INNER JOIN region r ON r.id=swr.region_id AND r.id=2
          WHERE swr.station_id IS NULL AND swr.rainfall_type='降雨'
         GROUP BY YEAR(swr.capture_time)
         ORDER BY YEAR(swr.capture_time)
  </select>



  <!--降雨量明细查询-->
  <select id="getjiangyumingxichaxun" resultType="com.lightinit.hsdatashow.model.topic.water.STRainModel">
    SELECT r.id,r.region_name,swrms.station_name,swrms.station_type,swr.capture_time,Round(SUM(swr.rainfall)) AS rainfall
    FROM st_water_rainfall swr
    INNER JOIN st_water_rainfall_monitoring_station swrms ON swrms.id=swr.station_id
    LEFT JOIN region r ON r.id=swrms.region_id AND r.parent_id=2 OR r.id=2
    <where>
      <if test="id !=null">
        r.id=#{id}
      </if>
      <if test="fromTime !=null">
        AND DATE(capture_time)>=#{fromTime}
      </if>
      <if test="toTime !=null">
        AND DATE(capture_time) &lt;=#{toTime}
      </if>
    </where>
    GROUP BY r.id,swr.capture_time
    ORDER BY swr.capture_time
    limit #{pageNo},#{pageSize}
  </select>

<!--降雨量明细查询总数-->
  <select id="getjiangyumingxichaxunTotal" resultType="int">
    SELECT COUNT(*)
 FROM(
    SELECT r.id,r.region_name,swrms.station_name,swrms.station_type,swr.capture_time,SUM(swr.rainfall) AS rainfall
    FROM st_water_rainfall swr
    INNER JOIN st_water_rainfall_monitoring_station swrms ON swrms.id=swr.station_id
    LEFT JOIN region r ON r.id=swrms.region_id AND r.parent_id=2 OR r.id=2
    <where>
      <if test="id !=null">
        r.id=#{id}
      </if>
      <if test="fromTime !=null">
        AND DATE(capture_time)>=#{fromTime}
      </if>
      <if test="toTime !=null">
        AND DATE(capture_time) &lt;=#{toTime}
      </if>
    </where>
    GROUP BY r.id,swr.capture_time
    ORDER BY swr.capture_time) AS A
  </select>

  <!--得到衡水区县-->
  <select id="getRegion" resultType="com.lightinit.hsdatashow.entity.Region">
    SELECT *
    FROM region
    WHERE parent_id=2
  </select>


        <!--工请-->
  <!--工程分布图-->
  <select id="getgongchengfengbutu" resultType="com.lightinit.hsdatashow.entity.StWaterProject">
    SELECT project_type,project_name,longitude,latitude
    FROM st_water_project
  </select>

  <!--各区县工程量-->
  <select id="getgeqvxiangongchengshu" resultType="com.lightinit.hsdatashow.model.topic.water.STChartModel">
    SELECT id,`name`,SUM(temp1) AS temp1,SUM(temp2)AS temp2,SUM(temp3)AS temp3
  FROM (
SELECT id, region_name AS `name`,
(CASE project_type WHEN '水库' THEN SUM(tot) ELSE 0 END) 'temp1' ,
(CASE project_type WHEN '河道' THEN SUM(tot) ELSE 0 END) 'temp2' ,
(CASE project_type WHEN '闸坝' THEN SUM(tot) ELSE 0 END) 'temp3'
FROM(
SELECT r.id,r.region_name,project_type,COUNT(project_type) AS tot FROM st_water_project swp
INNER JOIN region r ON r.id=swp.region_id GROUP BY r.id,swp.project_type
 ) AS A
 GROUP BY A.region_name,A.project_type
 ) AS B
 GROUP BY B.id
 ORDER BY B.id
  </select>



</mapper>