2132

<select id="getByQuestion" parameterClass="questionBean"
		resultMap="question">
		select questionId,QUESTIONAUTHOR,ADDRESS,ISANSWERED,
		X,Y,TITLE,CONTENT,CLASSIFY,SENDANSWER,CITYCODE,TIME from( select
		questionId,QUESTIONAUTHOR,ADDRESS,ISANSWERED,
		X,Y,TITLE,CONTENT,CLASSIFY,SENDANSWER,CITYCODE,TIME,ROWNUM myrow
		from( SELECT questionId,QUESTIONAUTHOR,ADDRESS,ISANSWERED,
		X,Y,TITLE,CONTENT,CLASSIFY,SENDANSWER,CITYCODE,TIME FROM
		t_ugc_question WHERE ISHIDDEN='2'
		<isNotEmpty property="questionId" prepend="AND">
			questionId=#questionId#
		</isNotEmpty>
		<isNotEmpty property="questionAuthor" prepend="AND">
			questionAuthor=#questionAuthor#
		</isNotEmpty>
		<isNotEmpty property="title" prepend="AND">
			title like '%'||#title#||'%'
		</isNotEmpty>
		<isNotEmpty property="classify" prepend="AND">
			classify like '%'||#classify#||'%'
		</isNotEmpty>
		<isNotEmpty property="cityCode" prepend="AND">
			cityCode = #cityCode#
		</isNotEmpty>

		<isNotEmpty property="range" prepend="AND">
			<![CDATA[
			x >= #minX# and  x <= #maxX#
			and y >= #minY# and y <= #maxY#
			]]>
		</isNotEmpty>

		<isNotEmpty property="isAnswered" prepend="AND">
			1=1
			<isNotEqual property="isAnswered" prepend="and"
				compareValue="0">
				isAnswered = #isAnswered#
			</isNotEqual>
		</isNotEmpty>

		<isEqual property="order" prepend="and" compareValue="1">
			1=1
			<isEqual property="sort" prepend="and"
				compareValue="Distance">
				1=1 order by GetDistance(x,y,#x#,#y#) asc
			</isEqual>
			<isEqual property="sort" prepend="and"
				compareValue="IsAnswered">
				1=1 order by isAnswered
			</isEqual>
			<isEqual property="sort" prepend="and"
				compareValue="Time">
				1=1 order by time
			</isEqual>
		</isEqual>
		<isEqual property="order" prepend="and" compareValue="2">
			1=1
			<isEqual property="sort" prepend="and"
				compareValue="Distance">
				1=1 order by GetDistance(x,y,#x#,#y#) desc
			</isEqual>
			<isEqual property="sort" prepend="and"
				compareValue="IsAnswered">
				1=1 order by isAnswered desc
			</isEqual>
			<isEqual property="sort" prepend="and"
				compareValue="Time">
				1=1 order by time desc
			</isEqual>
		</isEqual>
		) a ) b where 1=1
		<isNotEmpty property="fromNum" prepend="AND">
			<![CDATA[
			myrow >= #fromNum# 
			]]>
		</isNotEmpty>
		<isNotEmpty property="toNum" prepend="AND">
			<![CDATA[
			myrow <= #toNum# 
			]]>
		</isNotEmpty>

	</select>