添加无人机实时查询最大值和最小值的sql语句

ZhuDongming

2019-08-15 3f02455956dad0e19fc1bef171950319f95528a8

添加无人机实时查询最大值和最小值的sql语句

 src/main/resources/mapper/HistoryMapper.xml

@@ -191,4 +191,46 @@
        ORDER BY

        time

    </select>



    <select id="getSensorDataTodayMin" resultType="java.util.LinkedHashMap">

        SELECT

        DATE_FORMAT(time,'%Y-%m-%d %H:%i') time,

        <foreach collection="sensorKeys" separator="," item="sensorKey">

            MIN(h.value ->'$.${sensorKey}') AS '${sensorKey}'

        </foreach>

        FROM

        history h

        <where>

            <if test="mac!=null">

                and h.mac=#{mac}

            </if>

            and h.time >= #{startTime}

            and h.time <![CDATA[<=]]> #{endTime}

        </where>

        GROUP BY

        DATE_FORMAT(time,'%Y-%m-%d %H:%i')

        ORDER BY

        time

    </select>



    <select id="getSensorDataTodayMax" resultType="java.util.LinkedHashMap">

        SELECT

        DATE_FORMAT(time,'%Y-%m-%d %H:%i') time,

        <foreach collection="sensorKeys" separator="," item="sensorKey">

            MAX(h.value ->'$.${sensorKey}') AS '${sensorKey}'

        </foreach>

        FROM

        history h

        <where>

            <if test="mac!=null">

                and h.mac=#{mac}

            </if>

            and h.time >= #{startTime}

            and h.time <![CDATA[<=]]> #{endTime}

        </where>

        GROUP BY

        DATE_FORMAT(time,'%Y-%m-%d %H:%i')

        ORDER BY

        time

    </select>

</mapper>