sql小知识点学习记录

in与exists

select * from A where id in (select id from B)

1. in()适合B表比A表数据小的情况
2. exists()适合B表比A表数据大的情况
   当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用。

A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，
还不如使用in()遍历10000*100次，
因为in()是在内存里遍历比较，而exists()需要查询数据库，
我们都知道查询数据库所消耗的性能更高，而内存比较快。

yyyy-mm-dd hh24:mi:ss 用法（Oracle）

SELECT TO_CHAR(CURRENT_DATE,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

2018-11-30 17:16:07

因为SQL中不区分大小写，MM和mm被认为是相同的格式代码，因此Oracle的SQL采用了mi代替分钟。

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//**mi是分钟**

2018-11-30 17:16:07

select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//**mm会显示月份**

2018-11-30 17:11:07

start with 搭配 connect by prior（Oracle）

select * from TEST_SD t start with t.ID='10002' connect by prior t.PARENTID=t.ID;

select * from TEST_SD t start with t.ID='10002' connect by prior t.ID=t.PARENTID;

select * from TEST_SD t start with t.PARENTID='10002' connect by prior t.ID=t.PARENTID;

运行结果
（无锡市上级集合）

（无锡市下级集合）

（无锡市下级集合、不含自身）

leading ，trailing ，both

select trim(leading 'a' from 'a123456a');
select trim(trailing 'a' from 'a123456a');
select trim(both 'a' from 'a123456aa');

运行结果

select trim(leading 'a' from 'a123456a') from dual;

123456a

select trim(trailing 'a' from 'a123456a') from dual;

a123456

select trim(both 'a' from 'a123456a') from dual;

123456

docode

它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。
其具体的语法格式如下：

DECODE(input_value,value,result[,value,result…][,default_result]);

使用实例

SELECT SEX,DECODE(SEX,'0','女','1','男','未知') FROM GK_USER

运行结果

1	男
	未知
-1	未知
0	女

listagg()函数将多个列的查询结果集合并到一行中

listagg()函数合并结果时，可以设置分隔符，通过order by 设置它合并的先后顺序，通过 group by 来设置它合并的条件。有点类似于sum()或者count()的使用方法。

SELECT SEX,LISTAGG(GKCODE,',') WITHIN GROUP (ORDER BY GKCODE) FROM GK_USER GROUP BY SEX;

-1	00000626,00000627,00000641,00000663,00000681
0	00000482,00000521,00000522,00000541,00000561,00000562
1	00000481,00000523,00000524,00000564,00000566,10001712,10001763
	00000621,00000625,00000662,00000723,00000724

纯SQL将多个列的查询结果集合并到一行中

另一种方法，纯SQL一次性搞定，而且扩充性很好，没有列的限制。
对于你自己的应用，把SQL中“n_classify_code ”换为你的用来汇总的列，“n_summary_code”替换为需合并文本的列，“m_table”替换为你的表名。

SELECT    n_classify_code, TRANSLATE (LTRIM (text, '/'), '*/', '*,') researcherList
     FROM (SELECT ROW_NUMBER () OVER (PARTITION BY n_classify_code ORDER BY n_classify_code,
                   lvl DESC) rn,
                  n_classify_code, text
             FROM (SELECT      n_classify_code, LEVEL lvl,
                              SYS_CONNECT_BY_PATH (n_summary_code,'/') text
                         FROM (SELECT    n_classify_code, n_summary_code as n_summary_code,
                                        ROW_NUMBER () OVER (PARTITION BY n_classify_code ORDER BY n_classify_code,n_summary_code) x
                                   FROM m_table
                               ORDER BY n_classify_code, n_summary_code) a
                   CONNECT BY n_classify_code = PRIOR n_classify_code AND x - 1 = PRIOR x))
    WHERE rn = 1
ORDER BY n_classify_code;

使用实例

SELECT    SEX, TRANSLATE (LTRIM (text, '/'), '*/', '*,') researcherList
     FROM (SELECT ROW_NUMBER () OVER (PARTITION BY SEX ORDER BY SEX,
                   lvl DESC) rn,
                  SEX, text
             FROM (SELECT      SEX, LEVEL lvl,
                              SYS_CONNECT_BY_PATH (GKCODE,'/') text
                         FROM (SELECT    SEX, GKCODE as GKCODE,
                                        ROW_NUMBER () OVER (PARTITION BY SEX ORDER BY SEX,GKCODE) x
                                   FROM GK_USER
                               ORDER BY SEX, GKCODE) a
                   CONNECT BY SEX = PRIOR SEX AND x - 1 = PRIOR x))
    WHERE rn = 1
ORDER BY SEX;

结果

-1	00000626,00000627,00000641,00000663,00000681
0	00000482,00000521,00000522,00000541,00000561,00000562
1	00000481,00000523,00000524,00000564,00000566,10001712,10001763
	00000621

但是两种方法结果不同

第二种方法对于null的结果汇总是有缺失的。
两种方式在数据量大（拼接多）的情况下会返回

[Err] ORA-01489: result of string concatenation is too long

ORACLE批量插入数据的SQL语句

mybatis + oracle插入list数据

<insert id="insertList" parameterType="java.util.List">
		INSERT INTO
		SYS_ROLE_PERMISSION
		(ID, PERMISSIONID, ROLEID)
		<foreach collection="list" item="item" index="index" open="(" close=")" separator="union">
			select
			#{item.id},
			#{item.permissionid},
			#{item.roleid}
			from dual
		</foreach>
	</insert>

代码执行

INSERT INTO SYS_ROLE_PERMISSION (ID, PERMISSIONID, ROLEID) ( select ?, ?, ? from dual union select ?, ?, ? from dual union select ?, ?, ? from dual union 
select ?, ?, ? from dual union select ?, ?, ? from dual union select ?, ?, ? from dual union select ?, ?, ? from dual union select ?, ?, ? from dual ) 

Parameters: 4fa4a26789934dd191158132c8cb4364(String), 11102(String), ee3e641179584ef095c5461a59663a72(String), 
735cb3774e1d45b09bbc85794b42711b(String), 11104(String), ee3e641179584ef095c5461a59663a72(String), 
a209e48ca703421c9c740789ef96a67b(String), 11105(String), ee3e641179584ef095c5461a59663a72(String), 
fa83685628774030bb65d6c7b8d2b109(String), 11106(String), ee3e641179584ef095c5461a59663a72(String), 
c9eecf855ce142fcae70cb5b0e6df487(String), 11108(String), ee3e641179584ef095c5461a59663a72(String), 
fc49b8e1bff9458090efb14d63a7f72f(String), 11109(String), ee3e641179584ef095c5461a59663a72(String), 
7ab3634f764740d29177bd70198bbd8a(String), 111010(String), ee3e641179584ef095c5461a59663a72(String), 
4d04b231ab6046af9169b495624fe056(String), 11131(String), ee3e641179584ef095c5461a59663a72(String)

13:17:37.772 [http-nio-8088-exec-2] DEBUG com.lanswon.smcmanage.dao.SysRolePermissionMapper.insertList - <==    Updates: 8