Mapper.xml

学习一下别人的sql写法，扩大自己的视野。QuoteProductMapper.xml
XML头，我就不写了，这个只是简单的随便写了下架子，不具有实际意义。自己写sql,可以作为参考。
具体的有：1：搜索，一个字段按多层意思进行搜索，
2：集合遍历
3：case when then else 用法
4：sql片段
5：子查询
6：日期格式转换(+1/3,或者8小时)
需要注意的是，最好把sql中的注释用/**/ 来代替，这样的话再对sql进行格式化的时候，plsql可以直接格式化，但是如果用- - 注释的话，plsql是无法解析所有的–为注释的，所以字段多的时候，在 plsql中检查执行的sql会很麻烦。数据用的Oracle

<mapper namespace="com.xxxxx.domain.quote,QuoteProductMapper" >
		<resultMap id="resultMapper" type = "com.xxxxx.model.quote.QuoteProductModel">
			<id        column="ROWID"          property="rowId">
			<result  column="TOTAL_NUM" property="totalRecords">
			<result  column="CUR_PAGE"   property="curPage">
			
			<result column="PRODUCT_NAME" property="proName"><!--产品名称-->
			<result column="X_SWP"           property="swp"><!--标准质保期-->
			<result column="valStatus" 		property="valStatus"><!--供货状态-->
			。。。。字段很多，我就不写了
		</resultMap>

		<sql id= "Column_list"><!--sql片段-->
			T.ROW_ID,/*rowId*/
			TO_CHAR(T.CREATED+1/3,'YYYY-MMM-DD HH24:MI:SS') AS CREATED,/*创建时间*/
			--另一种写法：TO_CHAR(T.CREATED+numtodsinterval(8,'hour'),'YYYY-MMM-DD HH24:MI:SS') AS CREATED,/*创建时间*/
			T.X_SWP,/*标准质保期*/
			T15.VAL as valStatus,/*供货状态*/
			T.PRODUCT_NAME,/*产品名称*/
			/*以下为查询字段*/
			CASE WHEN T.X_EXIST_IN_PRICE_LIST = 'N' THEN T.X-ADJ_UNIT_TMP
				ELSE(
					CASE WHEN T.PAR_SQ_ITEM_ID  IS NOT NULL AND (CASE WHEN T6.PRO_TYPE_CD = 'Bundle' THEN 'Y' ELSE 'N' END) = 'Y'  THEN 0 ELSE 
					T.ADJ_UNIT_PRI  END
				) END AS StandardPrice,/*标准单价*/
			CASE WHEN SUBSTR(T1.PART_NUM,0,3) = '1.4' THEN '0天' 
			ELSE(
				case when (t2.attrib_36 is null or t2.attrib_36 = 'No') then '30天以上'
						when t2.attrib_36 = 'New' then '10-30天'
						when t2.attrib_36 = ‘General’ then '5-10天'
						else '2-5天' end		
			) END AS BILLCYCLE，  /*单据*/	
			NVL2(F.DOCSTATUS,F.DOCSTATUS,0) AS DOCSTATUS,
			C.STD_PRI_UNIT CATALOGPRICE /*AS 可以省略*/
		</sql>


		<select id="queryProduct" resultMap ="resultMap" parameterType = "com.xxxxx.model.quote.QuoteProductModel">
			<if test =" sum== false ">
				select * from （
						select #{curpage} as CUR_PAGE,ROWNUM AS rowno,t.* from(
			</if>
			<if test = "sum">
						select count(1) as TOTAL_NUM [,sum(Quote_money) as summoney from (--这个是根据需要加的。]
			</if>
				<!--主sql-->
				select distinct
				<include refid="Column_list" /> ,
				T21.ROW_ID AS OLD_DEMAND_ID
				<!--有其他字段，可以在这里面添加-->
				FROM  
							SIEBEL.S_QUOTE_ITEM T
							LEFT JOIN SIEBEL.S_PROD_INT T1 ON T.PROD_ID = T1.ROW_ID
							LEFT JOIN SIEBEL.S_PROD_INT_X T2 ON T1.ROW_ID = T2.PAR_ROW_ID
							INNER JOIN SIEBEL.S_DOC_QUOTE T7 ON T.SD_ID = T7.ROW_ID
							LEFT JOIN (
								select x.par_row_id,sum(a.query) quanty_sum from (select t.quanty,t.prod_id from siebel.cx_forcast_item t) a
							) T22 ON T2.PAR_ROW_ID = T22.PAR_ROW_ID
				<WHERE>
							T.SD_ID = #{quoteId}
							and ( t.prod_id is null or t17.released_flg = 'Y'
									 and t17.start_dt &lt; = sysdate
									 and (t17.end_dt is null or t17.end-dt > sysdate)
									and t18.first_vars &lt;= t17.ver_num and t18.last_vers &gt;= t17.ver_num)
							<if test =" name != null and name != '' ">	
									<!--按xxx,xxx,xxx进行搜索，一个字段代表多层意思-->
									and ( t1.x_name like '%'||#{name}||'%'  or  t1.part_num like '%'||#{name}||'%'  or t2.attrib_01 like '%'||#{name}||'%')
							</if>
							<if test= "product != null and product != ‘’ "> AND  T1.X_NAME LIKE '%'||#{product}||'%' </if><!--产品描述-->	
							<if test="partNum != null and partNum != '' ">AND T1.PART_NUM LIKE '%'||#{partNumber}||‘%’ </if>	<!--物料号-->	
							<!--集合型-->
							<if test=" partNumberList != null and partNumberList.size() != 0 ">
									AND
									<foreach collection ="partNumberList" index="index" item="item" open="(" separator=" or " close=")" >
										B.ATTRIB_02 = #{item}
									</foreach>
							</if>														
				</WHERE>
						ORDER BY T.LN_NUM
						) t
						<if test = "sum == false">
							) tbs  where  tbs.rowno &gt;= #{startRowNum,jdbcType = numeric}
							and tbs.rowno &lt;= #{endRowNum,jdbcType = numeric}
						</if>
		</select>
</mapper>