高级Ibatis查询（动态传入多个值、在不同时间断查询）

<!-- 动态数据查询  id="自定义名称" resultClass="返回结果类型" resultSetType="设置结果类型"-->
<select id="BREAKSLISTSQL" resultClass="HashMap" resultSetType="SCROLL_INSENSITIVE">
        SELECT  D.*,G.NAME AS ANALYSEGROUPID ,V.RegistrationNO AS VehicleID
	FROM ANA_DrivingBreak D
	LEFT JOIN ANA_AnalyseGroups G ON D.AnalyseGroupID=G.ID 
	LEFT JOIN dbo.MSC_Vehicle V ON D.VehicleID=V.ID
       <dynamic prepend="WHERE">      <!--动态添加条件-->
	<isNotNull property="VEHICLEID">       <!--判断属性是否为空-->
	<isNotEqual property="VEHICLEIDSIZE" compareValue="0" prepend="AND">
		 V.RegistrationNO IN <!--property="listData"  循环执行传进来的数据  in(值)-->
	<iterate property="VEHICLEID" open="(" close=")" conjunction=",">   
	   	'$VEHICLEID[]$'         <!--使用list保存数据，这里使用'$VEHICLEID[]$'  把值取出来-->
	</iterate>
	 </isNotEqual>
	<isEqual property="VEHICLEIDSIZE" compareValue="0" prepend="AND">
		 1 = 0            <!--property="listSize"   条件成立继续执行-->
	</isEqual>
	</isNotNull>
				
	<!-- 查询本月数据 -->
	<isEqual property="TIMETYPE" compareValue="1" prepend="AND">
		 MONTH(GenerateTime) = MONTH(GETDATE()) AND YEAR(GenerateTime) = YEAR(GETDATE())
	</isEqual> 
	<!-- 查询上月数据 -->
	<isEqual property="TIMETYPE" compareValue="2" prepend="AND">
		MONTH(GenerateTime) = MONTH(GETDATE()) -1 AND YEAR(GenerateTime) = YEAR(GETDATE())
	</isEqual> 
	<!-- 查询今天数据 -->
	<isEqual property="TIMETYPE" compareValue="3" prepend="AND">
		DAY(GenerateTime) = DAY(GETDATE()) AND MONTH(GenerateTime) = MONTH(GETDATE()) AND YEAR(GenerateTime) = YEAR(GETDATE())
	</isEqual> 
	<!-- 查询昨天数据 -->
	<isEqual property="TIMETYPE" compareValue="4" prepend="AND">
		DAY(GenerateTime) = DAY(GETDATE())-1 AND MONTH(GenerateTime) = MONTH(GETDATE()) AND YEAR(GenerateTime) = YEAR(GETDATE())
	</isEqual> 
	<!-- 查询自定义数据-->
	<isGreaterEqual property="TIMETYPE" compareValue="5" prepend="AND">
		 GenerateTime BETWEEN '$BEGINTIME$' AND '$ENDTIME$'
	</isGreaterEqual>
	</dynamic>
	ORDER BY D.ID DESC
</select>

 

评论

2 楼 di1984HIT 2015-09-08  

学习了，真的学习了～

1 楼 小嘴看世界 2010-08-24  

哪高级了？？？？？？？？