Mapper查询技巧

Sql字段动态比较判断

<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>

sql语句返回主键id

<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);

}

sql时间范围查询条件

//一

< 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>

sql每月数据统计

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)

sql对sum()结果判空

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

验证某个属性是否已存在（修改信息时用）

select COUNT(*) from ... where id != #id# and ... = #...#

sql数组传参（in）

//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>

Int类型拼接需要使用cast()

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)

sql截取字符串

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

巴特西

Mapper查询技巧

最新文章

热门文章