关于Oracle与MySQL，查询语句中一些函数的不同与转化(Group_concat、List_agg、ifnull、NVL、分页等)

Group_concat（MySQL分组函数）

1. 语法：
   group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
2. 分组后，把合并字段的值，合并于一个字段中，默认,分隔
3. 通过设置SEPARATOR修改分隔符

• 例：SELECT rname, GROUP_CONCAT(perms) FROM USER_PERMS GROUP BY rname;
• 例：SELECT rname, GROUP_CONCAT(perms SEPARATOR'|') FROM USER_PERMS GROUP BY rname;
  
  

List_agg(Oracle分组函数）

1. 语法：
   官方文档的解释如下：
   For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.
   即在每个分组内，LISTAGG根据order by子句对列植进行排序，将排序后的结果拼接起来。

• measure_expr：可以是任何基于列的表达式。
• delimiter：分隔符，默认为NUL
• order_by_clause：order by子句决定了列值被拼接的顺序。

2. 例：SELECT ROLEID,LISTAGG(menuid, ',')WITHIN GROUP(ORDER BY ROLEID) PERMS from CMS_ROLEMENU GROUP BY ROLEID;
   

IFNULL(MySQL空值处理函数)

1. 作用：IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
2. IFNULL() 函数语法格式为：
   IFNULL(expression, alt_value)
3. 如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值。

例：SELECT uid, uname, IFNULL(utext,"OJBK")utext FROM USER;

NVL(Oracle空值处理函数)

1. 作用：NVL()函数是Oracle中的一个函数，NVL()函数的功能是实现空值的转换。
2. 用法：NVL(string1,replace_with)中：

• 当第一个参数（string1）为空时，返回第二个参数（replace_with）；
• 当第一个参数（string1)不为空时，则返回第一个参数（string1）。
• NVL()函数的第一个参数和第二个参数类型必须相同，或者可以由隐式转换得到。

例：SELECT roleid, NVL(rolename， 'rolename') FROM CMS_ROLEMENU;

3. NVL2函数(类似三元运算符)：

• NVL2()函数：Oracle/PLSQL中的一个函数,Oracle在NVL函数的功能上扩展，提供了NVL2函数。
• 如NVL2(E1,E2,E3)中：
  当E1为NULL时，返回E3；当E1不为NULL时，返回E2。

MySQL(分页函数)

1. 直接使用limit即可
2. LIMIT PageStart, PageSize
3. 例：SELECT * FROM USER LIMIT 0,5;
   

Oracle(分页函数)

1.直接使用rownum

直接使用rownum时，不能对 rownum 使用 >（大于或等于 1 的数值）、>=（大于 1 的数值）、=（不等于 1 的数值），否则无结果。
因此，直接用rownum的时候只能从1开始。(rownum >= 1)
原因：如果第一条不满足，去掉的话，第二条的rownum还会是1，你条件不包括1，就永远没有符合条件的记录了。

单独使用rownum，Demo：
正确用法：

select * from EMP where ROWNUM >= 1;
select * from EMP where ROWNUM <= 1;
select * from EMP where ROWNUM <= 10;

以下用法查询无结果：

select * from EMP where ROWNUM > 1;
select * from EMP where ROWNUM >= 2;
select * from EMP where ROWNUM < 1;
select * from EMP where ROWNUM = 0;

2.使用rownum分页查询

2.1 rownum嵌套使用

select *
from (
	select ROWNUM AS rnum, e.*
	from EMP e
)
WHERE rnum >= 10;

2.2 rownum嵌套查询1

内层放结束游标，外层放起始游标。反之查询为空，后面同理。

select * from 
	(
		select 
			ROWNUM rnum,
			e.*
		FROM
			EMP e
		WHERE
			ROWNUM <= 4
	)
WHERE
	rnum >= 2;

2.3 rownum嵌套查询2(排序后分页)

若需要排序，先在内层排序好后，再进行分页

SELECT * FROM
	(
		SELECT * FROM
			(
				SELECT
					ROWNUM rnum,
					E .*
				FROM
					EMP E ORDER BY SAL
			)
		WHERE
			rnum <= 5
	)
WHERE
	rnum >= 2;

2.4 rownum嵌套查询2(BETWEEN)

使用此函数分页，查询效率略低一些

SELECT * FROM(
		SELECT ROWNUM rnum, e.*
		FROM EMP e
)WHERE rnum BETWEEN 3 AND 6;