1.count()_other
select * from (
select
COUNT(*)as ti ,ACCOUNT from virtual_human_4419
group by ACCOUNT
)s
where ti>1
2.查出的是一条数据select *
from virtual_human_4419 as a,virtual_human_4419 as b,card_4419
where card_id=a.account
and a.human_id2=b.HUMAN_ID2
and card_id='0015027192'
group by a.human_id2
3.like语句
select spec_id, spec_name, model, spec_desc from tape_spec
where MODEL like
'%$value$%'order by spec_id
4.嵌套，注意括号
select *
from virtual_human_4419_200605
WHERE (HUMAN_ID1, HUMAN_ID2) in
(select HUMAN_ID1,HUMAN_ID2
from virtual_human_4419_200605_0
where hash='GZogxxdyGYOkT6UY' and ID_TYPE='3' )
5.并操作
<dynamic prepend="">
<iterate prepend="" property="tablename"
conjunction="union" >
(select * from
$tablename[]$
where ACCOUNT=#ACCOUNT#
<![CDATA[and START_TIME>#START_TIME# and END_TIME<#END_TIME#
]]> )
</iterate>
</dynamic>
6.isNotNull是用来判断字符串的，整型用isNotEmpty<dynamic prepend="">
<iterate prepend="" property="tablename" conjunction="union" >
(select * from $tablename[]$
<dynamic prepend="where">
<isNotNull prepend="AND" property="ACCOUNT">
ACCOUNT=#ACCOUNT#
</isNotNull>
<isNotNull prepend="AND" property="START_TIME">
<![CDATA[START_TIME>#START_TIME# ]]>
</isNotNull>
<isNotNull prepend="AND" property="END_TIME">
<![CDATA[END_TIME<#END_TIME#]]>
</isNotNull>
</dynamic>
)
</iterate>
</dynamic>
7
<select id="HumanId22" parameterClass="edu.hit.ice.ibatis.domain.HumanInput" resultClass="edu.hit.ice.ibatis.domain.HumanOutput">
select CARD_ID,CARD_TYPE,ID_NUM,USER_NAME,$table2$.HUMAN_ID2,v3.COUNT
from $table1$,$table2$,
(select v2.HUMAN_ID2,COUNT(*) AS COUNT
FROM $table2$ as v2
group by v2.HUMAN_ID2
order by v2.HUMAN_ID2) AS v3
where $table1$.ID_TYPE=$table2$.ID_TYPE and $table1$.HASH=$table2$.HASH
and $table2$.HUMAN_ID2=v3.HUMAN_ID2
and <isNotEmpty property="query">
$query$ and
</isNotEmpty>
DAY$cmp$!=0
limit $BEGIN_ROW$, $PERPAGE$
</select>