一条sql语句两次foreach迭代问题

来源:转载

SELECT date_format(a.DataTime,'%H:%i') AS dateTime,SUM(a.nowpower) AS sum FROM
        (select drt.DataTime,drt.Pac AS nowpower FROM (SELECT pd.DeviceSN FROM plant p,plantdevice pd WHERE p.PlantUID=pd.PlantUid AND p.PlantUID=#{plantUid}
        <if test="sn != null and sn != ''">
          and pd.DeviceSN in
             <foreach item="sn" index="index" collection="sn" open="(" separator="," close=")">  
              #{sn}  
             </foreach>
             </if>
         ) AS d
        INNER JOIN dataforreadtime drt  ON d.DeviceSN=drt.DeviceSN WHERE DATE_FORMAT(drt.DataTime,"%Y-%m-%d")=#{dateTime}
        UNION ALL
        SELECT sdt.DataTime,getStoreDevicePower(sdt.PV1Power,sdt.PV2Power,sdt.PV3Power,sdt.PV4Power,sdt.PV5Power,sdt.PV6Power) AS nowpower
        FROM (SELECT pd.DeviceSN FROM plant p,plantdevice pd WHERE p.PlantUID=pd.PlantUid AND p.PlantUID=#{plantUid}
        <if test="sn != null and sn != ''">
          and pd.DeviceSN in
             <foreach item="sn" index="index" collection="sn" open="(" separator="," close=")">  
              #{sn}  
             </foreach>
             </if>
        ) AS d
        INNER JOIN store_dataforrealtime sdt  ON d.DeviceSN=sdt.DeviceSN WHERE DATE_FORMAT(sdt.DataTime,"%Y-%m-%d")=#{dateTime}

        )AS a GROUP BY a.DataTime


传过来同一个sn数组查询报错,解决方案:把两个foreach名不一样,两个相同的数组

SELECT date_format(a.DataTime,'%H:%i') AS dateTime,SUM(a.nowpower) AS sum FROM
        (select drt.DataTime,drt.Pac AS nowpower FROM (SELECT pd.DeviceSN FROM plant p,plantdevice pd WHERE p.PlantUID=pd.PlantUid AND p.PlantUID=#{plantUid}
        <if test="sn != null and sn != ''">
          and pd.DeviceSN in
             <foreach item="sn" index="index" collection="sn" open="(" separator="," close=")">  
              #{sn}  
             </foreach>
             </if>
         ) AS d
        INNER JOIN dataforreadtime drt  ON d.DeviceSN=drt.DeviceSN WHERE DATE_FORMAT(drt.DataTime,"%Y-%m-%d")=#{dateTime}
        UNION ALL
        SELECT sdt.DataTime,getStoreDevicePower(sdt.PV1Power,sdt.PV2Power,sdt.PV3Power,sdt.PV4Power,sdt.PV5Power,sdt.PV6Power) AS nowpower
        FROM (SELECT pd.DeviceSN FROM plant p,plantdevice pd WHERE p.PlantUID=pd.PlantUid AND p.PlantUID=#{plantUid}
        <if test="sn != null and sn != ''">
          and pd.DeviceSN in
             <foreach item="sns" index="index" collection="sns" open="(" separator="," close=")">  
              #{sns}  
             </foreach>
             </if>
        ) AS d
        INNER JOIN store_dataforrealtime sdt  ON d.DeviceSN=sdt.DeviceSN WHERE DATE_FORMAT(sdt.DataTime,"%Y-%m-%d")=#{dateTime}
        )AS a GROUP BY a.DataTime

分享给朋友:
您可能感兴趣的文章:
随机阅读: