oreacle中sql关键字的使用
1、union all 用户关联多个sql的使用
注意：此处要查询的字段和顺序必须一致
2、模糊查询关键字的使用：
%：表示任意个字符，包括零个；
_：表示一个任意字符；
3、case when then的使用 注意：Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略
SELECT s.s_id, s.s_name, s.s_sex, CASE WHEN s.s_sex = '1' THEN '男' WHEN s.s_sex = '2' THEN '女' ELSE '其他' END as sex, s.s_age, s.class_id FROM t_b_student s WHERE 1 = 1
<select id="selectAllProblems" resultMap="BaseResultMap"> select id,column_name,problem_id, car_mode, title from ( select t1.id, (case when #{lang} = 'zh' then t1.column_name when #{lang} = 'en' then t1.english_column_name when #{lang} = 'fr' then t1.french_column_name when #{lang} = 'es' then t1.spanish_column_name when #{lang} = 'ru' then t1.russian_column_name end) as column_name, t2.id as problem_id, t2.car_mode, (case when #{lang} = 'zh' then t2.problem_title when #{lang} = 'en' then t2.english_problem_title when #{lang} = 'fr' then t2.french_problem_title when #{lang} = 'es' then t2.spanish_problem_title when #{lang} = 'ru' then t2.russian_problem_title end) as title from oms_content_manage_column t1, oms_content_manage_problem t2 where t1.id = t2.problem_column_id <if test="title != null and title != ''"> and (case when #{lang} = 'zh' then t2.problem_title when #{lang} = 'en' then t2.english_problem_title when #{lang} = 'fr' then t2.french_problem_title when #{lang} = 'es' then t2.spanish_problem_title when #{lang} = 'ru' then t2.russian_problem_title end) like '%' || #{title} || '%' if> and t1.column_state = '1' and t2.problem_subsystem in ('01', '03') and t2.problem_state = '1' and t2.display_flag = '1' and t2.car_mode = #{autoType} and t2.del_flag = 0 and t1.del_flag = 0 order by t1.column_sort desc,t2.problem_sort desc ) union all select id,column_name,problem_id, car_mode, title from ( select t1.id, (case when #{lang} = 'zh' then t1.column_name when #{lang} = 'en' then t1.english_column_name when #{lang} = 'fr' then t1.french_column_name when #{lang} = 'es' then t1.spanish_column_name when #{lang} = 'ru' then t1.russian_column_name end) as column_name, t2.id as problem_id, t2.car_mode, (case when #{lang} = 'zh' then t2.problem_title when #{lang} = 'en' then t2.english_problem_title when #{lang} = 'fr' then t2.french_problem_title when #{lang} = 'es' then t2.spanish_problem_title when #{lang} = 'ru' then t2.russian_problem_title end) as title from oms_content_manage_column t1, oms_content_manage_problem t2 where t1.id = t2.problem_column_id <if test="title != null and title != ''"> and (case when #{lang} = 'zh' then t2.problem_title when #{lang} = 'en' then t2.english_problem_title when #{lang} = 'fr' then t2.french_problem_title when #{lang} = 'es' then t2.spanish_problem_title when #{lang} = 'ru' then t2.russian_problem_title end) like '%' || #{title} || '%' if> and t1.column_state = '1' and t2.problem_subsystem in ('01', '03') and t2.problem_state = '1' and t2.display_flag = '1' and t2.car_mode is null and t2.del_flag = 0 and t1.del_flag = 0 order by t1.column_sort desc,problem_sort desc ) select>
select id, case when column_name is not null then '中' else '' end as columnName, case when english_column_name is not null then '英' else '' end as englishColumnName, case when french_column_name is not null then '法' else '' end as frenchColumnName, case when spanish_column_name is not null then '西' else '' end as spanishColumnName, case when russian_column_name is not null then '俄' else '' end as russianColumnName from oms_content_manage_column
and case when t.AUDIT_ROLE = 'national_service_manager' then t2.COUNTRY_CODE else #{countryList[0]} end in <foreach collection="countryList" item="country" open="(" close=")" separator=","> #{country,jdbcType=VARCHAR} foreach>
4、in/not in 的作用：??
（1）in 常用于条件表达式（where）中，它的作用是对某个范围内的数据进行操作，查询或删除。字段值只要满足这个范围之内的任意一个即可
（2）not in 作用是查询不在某个范围内的数据，字段值同时不满这个范围的所有值。
2、语法：
（1）select * from TableName(表名） where 字段 in (value1，value2，…);
（2）select * from TableName(表名） where 字段 not in (value1，value2，…);
（3）select 字段1，字段2，… from 表名 where 字段 in/not in (value1，value2，…);
5、EXIST 和not exist 关键字的使用 in和not in的区别
SELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
---> SELECT * FROM B WHERE B.AID=１有值，返回真，所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
---> SELECT * FROM B WHERE B.AID=２有值，返回真，所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
---> SELECT * FROM B WHERE B.AID=３无值，返回假，所以没有数据
NOT EXISTS 就是反过来
SELECT ID,NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
3 A3
===========================================================================
EXISTS = IN,意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因
SELECT ID,NAME FROM A WHERE ID IN (SELECT AID FROM B)
NOT EXISTS = NOT IN ,意思相同不过语法上有点点区别
SELECT ID,NAME FROM A WHERE ID NOT IN (SELECT AID FROM B)
链接如下：
6、oreacle中list的使用
判断list <if test="countryList == null or countryList.size == 0"> 是否在集合中： <if test="roleSet != null and roleSet.size > 0"> and t.AUDIT_ROLE in <foreach collection="roleSet" item="role" open="(" close=")" separator=","> #{role,jdbcType=VARCHAR} foreach> if>
7、oreacle中时间比较 获取当前时间 时间比较范围
如果都是date类型可以直接比较：
<if test="repairStartDate!=null and endRepairDate!=null " > AND o.REPAIR_TIME between #{repairStartDate} and #{endRepairDate} if>
更改日期类型格式比较：
and to_char(t.item_Date,'yyyy-MM-dd') = to_char(#{omsOldMaterialHandleVO.itemDate},'yyyy-MM-dd')
当前时间在某个时间范围内：
AND TO_CHAR ( TRUNC (SYSDATE, 'dd'), 'YYYY-MM-DD hh24:mi:ss' ) BETWEEN TO_CHAR ( ra.RECTIFICATION_ENDTIME, 'YYYY-MM-DD hh24:mi:ss' ) AND TO_CHAR ( TRUNC (ra.RECTIFICATION_ENDTIME) + 30, 'YYYY-MM-DD hh24:mi:ss' )
8、NVL关键字的使用
1、NVL（表达式A，表达式B）
如果表达式A为空值，NVL返回值为表达式B的值，否则返回表达式A的值。该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式A和表达式B的数据类型必须为同一个类型。
例：
nvl(clue_num,0)：如果clue_num为空，则返回0；否则返回clue_num的值2、NVL2(表达式A，表达式B，表达式C）
如果表达式A为空，则返回表达式C的值；如果表达式A不为空，则返回表达式B的值。 例： nvl(sex,0,1):如果sex为空，则返回1；否则返回09、listagg关键字使用： 使用 listagg() WITHIN GROUP () 将多行合并成一行(比较常用)
SELECT OCP.*, su.realName AS currentHandlerName, ( SELECT listagg ( CAST ( su1.realname AS VARCHAR2 (500) ), ',' ) WITHIN GROUP ( ORDER BY OCA.SERVICE_INCENTIVE_ID ) FROM oms_ser_incentive_result OCA
其中cast函数的使用：
一、转换列或值
语法：cast( 列名/值 as 数据类型 )
用例：
1）、转换列
--将empno的类型（number）转换为varchar2类型。
select cast(empno as varchar2(10)) as empno from emp;
10、oreacle中group by 设计到分组会取最大值 最小值 和的值计算 max min sum
例如： 设计的案例可以查询如下：
https://baijiahao.baidu.com/s?id=1664040284917893293&wfr=spider&for=pc