mybatis mapper把传递来的参数当作字段写入结果集中

我会带着你远行 2022-06-12 10:44 257阅读 0赞

![Center][] ![Center 1][]

\-------------mapper把传递来的参数当作字段写入结果集中----------

<select id="queryStationStats" parameterType="java.util.Map" resultMap="StatsResultMap">  
     
    select  
     \# \{startTime,jdbcType=VARCHAR\} as StartTime,  
     \# \{endTime,jdbcType=VARCHAR\} as EndTime,  
     Station\_Stats\_Id stationStatsId ,  
     Station\_Id  
    from station\_stats  
    where 1=1  
     <if test="stationId != null and stationId !='' " >  
       and Station\_Id = \#\{stationId,jdbcType=VARCHAR\}  
     </if>  
    <if test="startTime != null and startTime !='' ">  
    and UNIX\_TIMESTAMP(Start\_Time) >= UNIX\_TIMESTAMP(\#\{startTime,jdbcType=VARCHAR\})  
   </if>  
   <if test="endTime != null and endTime !='' ">  
     and UNIX\_TIMESTAMP(End\_Time) <= UNIX\_TIMESTAMP(\#\{endTime,jdbcType=CARCHAR\})   
   </if>  
      
  </select>

ps：求站的总电量，站下各个设备的总量以及设备下各个接口的总量；一个站在每天会insert一条记录：时间段内query如下

SELECT  
 s.Station\_Stats\_Id,  
  c.Connector\_Stats\_Id,e.Equipment\_Stats\_Id,  
  s.Station\_Id,s.Station\_Electricity,  
  e.Equipment\_Id,e.Equipment\_Electricity,  
  c.Connector\_Id,c.Connector\_Electricity  
FROM  
  station\_stats s  
inner JOIN equipment\_stats e ON s.Station\_Stats\_Id = e.Station\_Stats\_Id  
inner JOIN connector\_stats c ON e.Equipment\_Stats\_Id = c.Equipment\_Stats\_Id  
where s.Station\_Id=720 \#and s.Start\_Time>'2017-07-15 00:00:00' and s.End\_Time<'2017-07-18 00:00:00'

AND UNIX\_TIMESTAMP(s.Start\_Time) >= UNIX\_TIMESTAMP('2017-07-15')  
    AND UNIX\_TIMESTAMP(s.End\_Time) <= UNIX\_TIMESTAMP('2017-07-18')  
GROUP BY s.Station\_Stats\_Id,s.Station\_Id,e.Equipment\_Id,c.Connector\_Id

最终优化之后数据结构：

SELECT  
  s.Station\_Id,  
  \#s.Station\_Electricity,  
  e.Equipment\_Id,  
  \#e.Equipment\_Electricity,  
  c.Connector\_Id,  
  SUM(s.Station\_Electricity) Station\_Electricity,  
  SUM(e.Equipment\_Electricity) Equipment\_Electricity,  
  SUM(c.Connector\_Electricity) Connector\_Electricity  
FROM  
  station\_stats s  
inner JOIN equipment\_stats e ON s.Station\_Stats\_Id = e.Station\_Stats\_Id  
inner JOIN connector\_stats c ON e.Equipment\_Stats\_Id = c.Equipment\_Stats\_Id  
where s.Station\_Id=720 \#and s.Start\_Time>'2017-07-15 00:00:00' and s.End\_Time<'2017-07-18 00:00:00'

AND UNIX\_TIMESTAMP(s.Start\_Time) > UNIX\_TIMESTAMP('2017-07-15')  
    AND UNIX\_TIMESTAMP(s.End\_Time) < UNIX\_TIMESTAMP('2017-07-18')  
GROUP BY s.Station\_Id,e.Equipment\_Id,c.Connector\_Id

[Center]: https://img-blog.csdn.net/20170721165143001?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcXFfMzI0NDczMjE=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: /images/20220612/dd897dfbe9f5413fb9dcda6a3edeef46.png