sql中常用的一些函数（后续用到新的持续补充）

MYSQL

1、字符串拼接函数

CONCAT、CONCAT_WS、GROUP_CONCAT

语法
concat(str1,str2,...)
例子说明：
SELECT CONCAT('are ', 'you ', ' ok?') AS 'result' FROM DUAL;

语法
CONCAT_WS(separator,str1,str2,...)
语法说明
CONCAT_WS(分隔符,字符1,字符2,...)
例子说明
SELECT CONCAT_WS(' ','but','we',"haven't",'fate') AS 'result' FROM DUAL;
//对分组后的数据，指定某个字段进行拼接，并且可以指定拼接的分隔符和拼接的顺序
语法
GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])
语法说明：
GROUP_CONCAT([DISTINCT] 字符1,字符2... [ORDER BY 需要排序的字段 [ASC | DESC] ] [SEPARATOR 分隔符])

2、字符串截取函数

left()、right()、substring()、substring_index()、mid()、substr() 等价于 substring() 函数

select left('cs.csdn', 3);
# 从左边开始截取三个字符，结果：cs.
select right('cs.csdn', 3);
# 从左边开始截取三个字符，结果：sdn
//substring(str, pos)、substring(str, pos, len) 索引为负，从右向左查位置

3、聚合函数

聚合函数不能嵌套使用
求平均值 AVG() ,求和SUM() ,求最大值MAX(),求最小值MIN(), 统计一组数据的个数COUNT()
聚合函数一般搭配分组函数group by使用
如果对聚合后的数据还要进行条件筛选，就需要用到having

ORACLE

1、nvl(对应mysql中的ifnull)，to_date(对应mysql中的str_to_date),to_char(对应mysql中的date_formate),decode(对应mysql中的if),rownum(对应MySQL中的limit)

nvl (字段1，默认值)
nvl意思是 如果字段1为空，就取默认值，对应mysql中的ifnull函数

2、聚合函数

常用聚合函数

listagg、xmlagg、wm_concat、zh_concat、case when函数

wm_concat
来自wmsys下的wm_concat函数，属于Oracle内部函数，返回值类型varchar2，最大字符数4000。随着版本的变更返回值类型可能会有改动，项目中使用时候最好在新的用户下创建一个函数。
适用于简单聚合数据
优点：效率高.
缺点：（1）返回最大字符数4000
（2）行数据默认以逗号分隔，可以修改函数更改，但是函数一旦创建不能随意自定义分隔符；
（3）排序实现复杂且效率低；
（4）内部聚合混乱

//使用方法
select id,wm_concat(name) from stu group by id;
//排序方法（其中一种）
select * from (select wm_concat(name) over(partition by class_id order by id) val,

row_number() over(partition by class_id order by id desc) rn,

a.* from stu a) where rn = 1;

zh_concat
该函数是在wm_concat基础上修改返回值类型得到，可以返回clob类型数据，内部实现同wm_concat。优缺点同wm_concat。

listagg
11g新增函数，返回值varchar2，同样受4000字符数限制。但是可以排序，可以指定分隔符。
优点：可排序、可自定义分隔符
缺点：返回最大字符数4k

//使用方法
select class_id,listagg(name,',') within group(order by id) from stu group by class_id

xmlagg
xmlagg函数需要将输入的值转换为xml，处理返回结果也是xml，最后再用getclobval()获取colb类型的结果。

1.xmlagg(xmlparse(content 合并字段 ||’,’ wellformed) order by 排序字段).getclobval()

2. xmlagg(xmlelement(e, 合并字段, ',').extract('//text()')).getclobval()

XMLAGG([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])

col-list可以是单个字段，也可以是用逗号分割的字段列表
string-expr 计算结果为字符串的sql表达式（通常是要从其中检索数据的列的名称）
%FOREACH(col-list) 可选-列名或者以逗号分割的列名列表。
%AFTERHAVING-可选用在HAVING子句中的条件

当类型为varchar2时，不排序用wm_concat;当排序时候，11g以上用listagg，10g用xmlagg；当类型为clob时，排序用xmlagg，不排序用zh_concat

写法一
case 属性名
when '1' then '是'
else '否' 
end 
写法二
case
when 1=1 then '是'
else '否' 
end