<sql id="getUserInfoList_body">
     SELECT *　from userinfo
     <dynamic prepend="WHERE">
     　　<isNotNull prepend="AND" property="id"> a.id=#id#</isNotNull>
     　　<isNotNull prepend="AND" property="userName"> a.userName like concat(‘%‘, #userName#, ‘%‘) </isNotNull>
     　　<isNotNull prepend="AND" property="inschoolTimeEnd"> a.inschoolTime &lt; #inschoolTimeEnd# </isNotNull>
     　　<isNotNull property="roleID">
           <isEqual property="roleID" compareValue="3" prepend="AND">
            addrProvinceID in (SELECT areaID FROM co_user_area WHERE adminID = #adminID#)</isEqual>
           <isEqual property="roleID" compareValue="4" prepend="AND">
            addrCityID in (SELECT areaID FROM co_user_area WHERE adminID = #adminID#)</isEqual>
     　　</isNotNull>
     </dynamic>
     ORDER BY a.id DESC  
</sql>

<insert id="">      //注意无需resultClass
…
　　<selectKey resultClass="int" keyProperty="id">
   　　SELECT @@IDENTITY AS id
　　</selectKey>
</insert>     

public Integer insert(Invoice invoice) throws DAOException {
   return (Integer)getSqlMapClientTemplate().insert("insert",invoice);
}

//一
< isNotNull prepend="AND" property="createStartDateStr"> 
　　CONVERT(varchar(100), kb.createDate , 23) &gt;= #createStartDateStr# 
</isNotNull>
<isNotNull prepend="AND" property="createEndDateStr">
　　CONVERT(varchar(100),kb.createDate,23) &lt;=#createEndDateStr# 
</isNotNull>

//二
<isNotNull prepend="AND" property="startTime">
　　kc.createDate >= #startTime# 
</isNotNull>
<isNotNull prepend="AND" property="endTime">
　　<![CDATA[kc.createDate <= #endTime#]]>
</isNotNull>  

//三
<isNotNull prepend="AND" property="startTime">
　　CONVERT(varchar(100), kb.createDate , 23) &gt;= #startTime# 
</isNotNull>
<isNotNull prepend="AND" property="endTime">
　　CONVERT(varchar(100), kb.createDate , 23) &lt;= #endTime# 
</isNotNull>

SELECT
    COUNT(DISTINCT kes.userID) AS examCount,
    CONVERT(nvarchar(7), kesd.createTime, 120) AS month
FROM kp_out_system kos WITH (NOLOCK)
LEFT JOIN kp_exam_score kes WITH (NOLOCK)
    ON kes.outSystemSign = kos.outSystemSign
LEFT JOIN kp_exam_score_detail kesd WITH (NOLOCK)
    ON kesd.examScoreID = kes.id AND kesd.valid = 1
    < isNotNull prepend="AND" property="startTime">  kesd.createTime  &gt;= #startTime# </isNotNull>
    <isNotNull prepend="AND" property="endTime">  kesd.createTime  &lt;= #endTime# </isNotNull> 
    WHERE kos.status = 1
    <isNotNull prepend="AND" property="outSystemSign"> kos.outSystemSign=#outSystemSign# </isNotNull>
    AND kos.outSystemSign IS NOT NULL AND kos.sysAreaID IS NOT NULL AND kesd.createTime IS NOT NULL
    GROUP BY CONVERT(nvarchar(7), kesd.createTime, 120)
    ORDER BY CONVERT(nvarchar(7), kesd.createTime, 120)

SELECT ISNULL(SUM(myTable.thesisCount),0) AS thesisCount FROM (
　　SELECT
       COUNT(kuu.id) AS thesisCount,
       ISNULL(kos.outSystemName, ‘无‘) AS outSystemName
   FROM kp_out_system kos WITH (NOLOCK)
   LEFT JOIN kp_user_upload_thesis kuu WITH (NOLOCK) ON kuu.outSystemSign = kos.outSystemSign AND kuu.valid = 1
   WHERE kos.status = 1
   　　   <isNotNull prepend="AND" property="outSystemSign"> kuu.outSystemSign = #outSystemSign# </isNotNull>
         AND kos.outSystemSign IS NOT NULL AND kos.sysAreaID IS NOT NULL
   GROUP BY ISNULL(kos.outSystemName, ‘无‘)
) AS myTable

//Action代码
if (null != studyID && 2 == studyID.intValue()) {
    Integer[] studyIDs = new Integer[] { 2,3, 4 };
    userInfo.setStudyIDs(studyIDs);
} else {
    userInfo.setStudyID(studyID);
}

//Xml文件
<isNotNull prepend="AND" property="studyID">
    a.studyID=#studyID#
</isNotNull>
<isNotNull prepend="AND" property="studyIDs">
    a.studyID in
    <iterate property="studyIDs" open="(" close=")" conjunction=",">
        #studyIDs[]#
    </iterate>
</isNotNull>

SELECT (SELECT DISTINCT
           CAST(studyID AS varchar) + ‘,‘
       FROM kp_study_type
       WHERE outSystemSign = ‘drivingcoach‘ AND valid = 1
       FOR xml PATH (‘‘))
       AS studyIDs,
       ISNULL(
       SUM(CASE
           WHEN b.isPass = 1 THEN 1 ELSE 0
       END), 0) AS isPassCount,
       ISNULL(
       SUM(CASE
           WHEN b.isOpen = 1 OR b.isOpen = 3 THEN 1 ELSE 0
       END), 0) AS isOpenCount,
       ISNULL(
       SUM(CASE
           WHEN b.isOpen = 1 OR b.isOpen = 3 THEN 0 ELSE 1
       END), 0) AS isNotOpenCount
FROM kp_study_type a WITH (NOLOCK)
INNER JOIN kp_select_studyType b WITH (NOLOCK)
    ON a.studyID = b.studyID AND b.valid = 1
INNER JOIN kp_user_info kui WITH (NOLOCK)
    ON kui.ID = b.userID AND kui.valid = 1
LEFT JOIN (SELECT
    k.studyID,
    k.userID,
    SUM(k.studyTimes) studyTimes,
    SUM(CASE
       WHEN k.studyTimes >= k.studyTime * 60 THEN 1 ELSE 0
    END) coursefinishCount,
    COUNT(k.courseID) courseCount
FROM (SELECT
    r.studyID,
    t.userID,
    SUM(t.studyTimes) AS studyTimes,
    r.courseID,
    r.studyTime
FROM kp_studyCourse_relation r WITH (NOLOCK)
LEFT JOIN kp_course c WITH (NOLOCK)
    ON r.courseID = c.id
LEFT JOIN kp_study_course_time t WITH (NOLOCK)
    ON c.courseNumber = t.courseNumber AND t.studyID = r.studyID
WHERE r.valid = 1 AND c.valid = 1 AND t.id IS NOT NULL
GROUP BY   r.studyID,
           t.userID,
           r.courseID,
           r.studyTime) k
GROUP BY   k.studyID,
           k.userID) e
    ON a.studyID = e.studyID AND b.userID = e.userID
WHERE a.valid = 1 AND a.studyID IN (SELECT DISTINCT
    studyID
FROM kp_select_studyType
WHERE outerSystemSign = ‘drivingcoach‘ AND valid = 1 AND isOpen = 1)

SELECT DISTINCT
    ISNULL(SUBSTRING(areaID, 1, 6), 0) AS areaID
FROM kp_user_info
或
SELECT
TOP 10
    kui.username 
FROM kp_user_info kui WITH (NOLOCK)
LEFT JOIN dic_area dc WITH (NOLOCK)
    ON RIGHT(kui.areaID, 6) = dc.areaID

if(null != provinceID && !"000000".equals(provinceID)){
    productOrder.setAreaID(provinceID);                    
}
if(null!=cityID && cityID != ""){
    //数据库存储为110000:110101，依据存储方式设置
    productOrder.setAreaID(provinceID+":"+cityID);
}

<dynamic prepend="">
    <isNotNull prepend="AND" property="areaID">
 　 　　kui.areaID like #areaID# + ‘%‘
　　 </isNotNull>
</dynamic>

*
FROM computer
WHERE sex = ‘男‘ AND LEFT(name, 1) IN (‘李‘, ‘陈‘)
或
SELECT
    *
FROM computer
WHERE sex = ‘男‘
(and name like ‘李__‘or name like ‘陈__‘)

//-1-
SET IDENTITY_INSERT cdel_jxjy_flat20171106..op_ip_login_control ON;
//-2-
INSERT INTO cdel_jxjy_flat20171106..op_ip_login_control (id, ip, notes, valid, creator, createTime)
SELECT * FROM cdel_jxjy_flat0725..op_ip_login_control