sql整理

来源：互联网发布：川南减震淘宝店编辑：程序博客网时间：2024/05/22 09:38

*插入数据后并查出插入数据的主键id

<insert id="abatorgenerated_insert" parameterClass="com.garea.cloud.entities.em.CustOrg">        insert into cust_org (org_id)        values (#orgId:BIGINT#)        <selectKey keyProperty="orgId" resultClass="java.lang.Long">        <![CDATA[          SELECT LAST_INSERT_ID() AS id        ]]>        </selectKey>    </insert>

*<![CDATA[]>

*case when else end

*DATE_FORMAT(,)

<![CDATA[select ca.id as id,ca.open_id as openId,    case    when cp.birthday is NULL then NULL    else    (year(now())-year(cp.birthday)-1) + ( DATE_FORMAT(cp.birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) end as age,    (SELECT GROUP_CONCAT(beConcerned.person_name) FROM cust_person_relation cor left join cust_person beConcerned on beConcerned.id = cor.be_concerned_id WHERE cor.concerned_id = #accountId:BIGINT#) as beConcerned    from cust_account ca    left join cust_person cp on cp.id = ca.id    left join cust_org co on co.org_code = cp.org_code]]>    where ca.id = #accountId:BIGINT#

<select id="selectAllByParams" parameterClass="com.garea.cloud.dto.em.CustPersonSearchDto" resultClass="com.garea.cloud.dto.em.CustPersonAccountOrgDto">   <![CDATA[select ca.id as id,from cust_person cp left join cust_account ca on ca.id = cp.idleft join cust_org co on co.org_code = cp.org_code ]]><dynamic prepend="where"><isNotEmpty prepend="and" property="accountLevel">ca.account_level = #accountLevel:SMALLINT#</isNotEmpty><isNotEmpty prepend="and" property="params">(cp.person_name like CONCAT('%',#params:VARCHAR#,'%') or ca.nick_name like CONCAT('%',#params:VARCHAR#,'%') or cp.mobile_phone like CONCAT('%',#params:VARCHAR#,'%'))</isNotEmpty></dynamic>order by ca.create_time desc  </select>

*LEFT (<character_expression>， <integer_expression>)

返回character_expression 左起 integer_expression 个字符。

*用EXISTS替代IN、用NOT EXISTS替代NOT IN：

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
例子：

（高效）SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT ‘X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB') (低效)SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')SELECT * FROM (SELECT * FROM  cust_area  WHERE  area_parent IN (SELECT area_code FROM  cust_area WHERE area_name = #areaName:VARCHAR#)) a WHERE EXISTS (SELECT 1 FROM cust_store t WHERE (LEFT(t.area_code,4) = LEFT(a.area_code, 4)) AND  (t.status = 2  OR  t.status = 5))

下面链接有这方面的介绍：
http://my.oschina.net/xianggao/blog/87216

0 0