Oracle数据库-常用函数及查询条件用法（二）

一. 常使用的函数

1.sign()函数：符号函数

是根据某个值是0、正数还是负数，分别返回0、1、-1

2.decode() 函数

 decode(value,if1,then1,if2,then2,if3,then3,...,else)：
 含义为：值value, if value=值1  then return (value1)
                else value=值2 then return (value2)
                else value=值3 then return (value3)
　　　　            ......
                else  return (default)
　              end if 

例如：有学生成绩表student，现在要用decode函数实现以下几个功能：成绩 > 85,显示优秀；>70显示为良好；>60及格；否则是不及格。

select id,decode(sign(score-85),1,'优秀',0,'优秀',-1,
		decode(sign(score-70),1,'良好',0,'良好',-1,
		  decode(sign(score-60),1,'及格',0,'及格',-1,'不及格')
		)
	)
	from student;

3.case when … then … else …

语法：case  when <A>  then <someThingA>  when <B> then <someThingB>    else   <someThingE>  end

例如：数据库存储中为了方便，使用1 代表男，使用2代表女，查询时性别翻译为：

select id, name, (case sex when 1 then '男' when 2 '女' else '空' end)性别 from table;

例如：更新时，如果id为某些值时，设置pid为1，其余为0

update T_RELATION set pid = (case when id in ('00000','00001','00002','00004','00005') then '1' else '0' end ) where rid='1001';

4.wmsys.wm_concat() 合并行记录/列转字符串行函数

wmsys.wm_concat() 函数，是在oracle 10g数据库中推出，返回为字符串类型，在11g版本中返回clob类型，
括号里面的参数是列，而且可以是多个列的集合，也就是说在括号里面可以自由的使用'||' 合并字符串

例如：查询时，获取一个人的多个标签

select t.usernum as userNum,
 wmsys.wm_concat( (select s.LABELKEY from T_LABELTYPE s where s.id = t.labelCode) 
 || ':' || t.labelvalue ) as labelValues
 from T_RES_NODELABEL t 
 WHERE t.TASKID = '1001' group by t.usernum

5.listagg()函数 列转行函数

语法：listagg(xx,xx) within group(order by XX)  

例如：

select listagg(labelValue,',') within group(order by labelValue) as labelCodes from t_res_nodelabel where taskId='neta11' and userNum='13111223344';
结果：labelValues
	13111223344,27,暂驻,男,碑林行政区,政区,铜川市

二. 数据库join用法

1.join 分类

join 分为：内连接（inner join）
		  外连接（outer join）---right outer join、left outer join、full outer join
		  自然连接（natural join）

2.join用法
1）内连接（inner join）:表示返回两个表或记录集连接字段的匹配记录，即：返回两张表的交集
2）外连接（outer join）

(1)全连接：full join 包含左右两个表的全部行,不管另一边的表中是否存在与它们匹配的行,不符合条件的,以空值代替。
(2)左外连接：left join 左外连接又叫左连接，意思是：包含左边表所有记录，右边表的所有记录，如果没有则用空补齐，换句话说就是，列出左边表全部的，以及右边表符合条件的，不符合条件的以空值代替。
(3)右外连接： 右外连接又叫右连接：意思是包括右边表所有记录，匹配左边表的记录，如果没有则以空补齐，换句话说，与左连接一样，列出右边表全部的，以及左边表符合条件的，不符合条件的用空值替代。

3）自然连接：natural join 自然连接的两个表有多个字段都满足有相同名称，那么他们会被作为自然连接的条件。