sql总结1

<!-- 预测模型    日期ENDDATE   品种NAME   市场名称MARKETPLACE--><!-- 市场均价 -->
<select id="QueryForecastMarketPrice" parameterType="pd" resultType="pd">
SELECT DATE_ADD(DATE,INTERVAL 1 DAY) AS FirstDay,ROUND(SUM(AVGPRICE)/3,4)AS FirstDayValue,
DATE_ADD(DATE,INTERVAL 2 DAY) AS SecondDay,ROUND((SUM(AVGPRICE)/3+(
SELECT SUM(AVGPRICE)
from  t_info_wholesaleprices 
where 1=1
AND MARKETPLACE=#{MARKETPLACE}
AND name=#{NAME}
<choose>
<when test="ENDDATE != null and ENDDATE != '' "> 
AND DATE_FORMAT(DATE,"%Y-%m-%d") <![CDATA[>=]]>DATE_FORMAT(#{ENDDATE}-1,"%Y-%m-%d")
</when>
<otherwise>
AND DATE >= (select DISTINCT date_sub((SELECT DISTINCT MAX(DATE) 
FROM t_info_wholesaleprices
WHERE NAME = #{NAME}),interval 1 day) 
from t_info_wholesaleprices) 
</otherwise>
</choose> 
<choose>
<when test="ENDDATE != null and ENDDATE != '' ">   
AND DATE_FORMAT(DATE,"%Y-%m-%d")<![CDATA[<=]]>DATE_FORMAT(#{ENDDATE},"%Y-%m-%d")
</when>
<otherwise>
AND DATE <![CDATA[<=]]>(select MAX(DATE) from t_info_wholesaleprices  WHERE NAME = #{NAME})
</otherwise>
</choose> 
))/3,4) AS SecondDayValue,
DATE_ADD(DATE,INTERVAL 3 DAY) AS ThirdDay,
ROUND((SUM(AVGPRICE)/3+(
(SUM(AVGPRICE)/3+(
SELECT SUM(AVGPRICE)
from  t_info_wholesaleprices 
where MARKETPLACE=#{MARKETPLACE}  
AND name=#{NAME}
<choose>
<when test="ENDDATE != null and ENDDATE != '' "> 
AND DATE_FORMAT(DATE,"%Y-%m-%d") <![CDATA[>=]]>DATE_FORMAT(#{ENDDATE}-1,"%Y-%m-%d")
</when>
<otherwise>
AND DATE >= (select DISTINCT date_sub((SELECT DISTINCT MAX(DATE) 
FROM t_info_wholesaleprices
WHERE NAME =#{NAME}),interval 1 day) 
from t_info_wholesaleprices) 
</otherwise>
</choose>
<choose>
<when test="ENDDATE != null and ENDDATE != '' "> 
AND DATE_FORMAT(DATE,"%Y-%m-%d")<![CDATA[<=]]>DATE_FORMAT(#{ENDDATE},"%Y-%m-%d") 
</when>
<otherwise>
AND DATE<![CDATA[<=]]>(select MAX(DATE) from t_info_wholesaleprices  WHERE NAME = #{NAME})
</otherwise>
</choose>    
))/3
)+(
SELECT SUM(AVGPRICE)
from  t_info_wholesaleprices 
where MARKETPLACE=#{MARKETPLACE}  
AND name=#{NAME} 
<choose>
<when test="ENDDATE != null and ENDDATE != '' "> 
AND DATE_FORMAT(DATE,"%Y-%m-%d") <![CDATA[>=]]> DATE_FORMAT(#{ENDDATE},"%Y-%m-%d") 
</when>
<otherwise>
AND DATE=(select MAX(DATE) from t_info_wholesaleprices  WHERE NAME = #{NAME})
</otherwise>
</choose>
))/3,4) AS ThirdDayValue
from  t_info_wholesaleprices 
where MARKETPLACE=#{MARKETPLACE} 
AND name=#{NAME}
<choose>
<when test="ENDDATE != null and ENDDATE != '' "> 
AND DATE_FORMAT(DATE,"%Y-%m-%d") <![CDATA[>=]]> DATE_FORMAT(#{ENDDATE}-2,"%Y-%m-%d") 
</when>
<otherwise>
AND DATE <![CDATA[>=]]> (select DISTINCT date_sub((SELECT DISTINCT MAX(DATE) 
FROM t_info_wholesaleprices
WHERE NAME = #{NAME}),interval 2 day) 
from t_info_wholesaleprices) 
</otherwise>
</choose> 
<choose>
<when test="ENDDATE != null and ENDDATE != '' "> 
AND DATE_FORMAT(DATE,"%Y-%m-%d") <![CDATA[<=]]> DATE_FORMAT(#{ENDDATE},"%Y-%m-%d")  
</when>
<otherwise>
AND DATE<![CDATA[<=]]>(select MAX(DATE) from t_info_wholesaleprices  WHERE NAME = #{NAME})
</otherwise>
</choose>
</select>