sql COALESCE函数潜在问题

目录

文章目录

• 目录
• lead()函数
• substirng_index()函数
• case函数
• if()函数
• find_in_set()函数
• replace()函数
• trim()函数
• row_number() over 分组排序功能
• timestampdiff ()函数

lead()函数

lead()函数，允许您向前看多行并从当前访问行的数据
与lag()函数类似。

语法：
LEAD(<expression>[,offset[, default_value]]) OVER (
    PARTITION BY (expr)
    ORDER BY (expr)
)

expression
LEAD()函数返回的值expression从offset-th有序分区排。

offset
offset是从当前行向前行的行数，以获取值。

offset必须是一个非负整数。如果offset为零，则LEAD()函数计算expression当前行的值。

如果省略  offset，则LEAD()函数默认使用一个。
default_value
如果没有后续行，则LEAD()函数返回default_value。例如，如果offset是1，则最后一行的返回值为default_value。

如果您未指定default_value，则函数返回  NULL 。

PARTITION BY子句
PARTITION BY子句将结果集中的行划分LEAD()为应用函数的分区。

如果PARTITION BY未指定子句，则结果集中的所有行都将被视为单个分区。

ORDER BY子句
ORDER BY子句确定LEAD()应用函数之前分区中行的顺序。

示例：
我们将使用示例数据库中的orders和customers表进行演示：

以下语句查找每个客户的订单日期和下一个订单日期：

SELECT 
    customerName,
    orderDate,
    LEAD(orderDate,1) OVER (
        PARTITION BY customerNumber
        ORDER BY orderDate ) nextOrderDate
FROM 
    orders
INNER JOIN customers USING (customerNumber);

substirng_index()函数

substring_index(str,delim,count)
 str:要处理的字符串
 delim:分隔符
 count:计数
 示例一：
 SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number
 FROM user_submit

case函数

case when类似于编程语言中的if else判断、switch case语句。
该语句执行时先对条件进行判断，然后根据判断结果做出相应的操作。

Case具有两种格式：简单Case函数和Case搜索函数。
简单Case函数：
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘0’ THEN ‘女’
ELSE ‘其他’ END


Case搜索函数：
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘0’ THEN ‘女’
ELSE ‘其他’ END

if()函数

IF( expr1 , expr2 , expr3 )
expr1 的值为 TRUE，则返回值为 expr2 
expr1 的值为FALSE，则返回值为 expr3

select if("1=1",1,0);
-> 1

IFNULL( expr1 , expr2 )
如果expr1不为空，直接返回expr1；
如果expr1为空，返回第二个参数 expr2   

SELECT IFNULL(NULL,"11");
->11

find_in_set()函数

FIND_IN_SET(str,strlist)

str 要查询的字符串

strlist 字段名 参数以”,”分隔 如 (1,2,6,8)

查询字段(strlist)中包含(str)的结果，返回结果为null或记录

SELECT FIND_IN_SET('b', 'a,b,c,d'); 
-> 2 因为b 在strlist集合中放在2的位置 从1开始

1、find_in_set() 和 in 的区别：

1.如果待查询的条件是常量那就使用IN，是变量则使用FIND_IN_SET，可以使用索引的。
2.如果使用IN和FIND_IN_SET都能满足条件，则最好使用IN，理由同上，特别是查询字段为主键时或有索引时。
3.如果使用IN不能满足功能需求，那只能使用FIND_IN_SET，有时候说不定IN中条件加个%号也可以解决问题，加个%号IN就不只是比较是否相等了！

示例1：
测试代码：  
CREATE TABLE `test` ( 
 `id` int(8) NOT NULL auto_increment, 
 `name` varchar(255) NOT NULL, 
 `list` varchar(255) NOT NULL, 
 PRIMARY KEY (`id`) 
) 
INSERT INTO `test` VALUES (1, 'name', 'zs,zs,zs'); 
INSERT INTO `test` VALUES (2, 'name2', 'zs,daodao,xiaoqin'); 
INSERT INTO `test` VALUES (3, 'name3', 'zs,daodao,xiaohu'); 
test1:sql = select * from `test` where name IN ("zs"); 
得到结果空值. 
test2:sql = select * from `test` where FIND_IN_SET('zs',`list`); 
得到三条数据。 



2、find_in_set() 和 like的区别：
like是广泛的模糊匹配，字符串中没有分隔符，Find_IN_SET 是精确匹配，字段值以英文”,”分隔

示例1：
CREATE TABLE users(
id int(6) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
limits VARCHAR(50) NOT NULL, – 权限
PRIMARY KEY (id)
);

INSERT INTO users(name, limits) VALUES(‘小张’,‘1,2,12’);
INSERT INTO users(name, limits) VALUES(‘小王’,‘11,22,32’);

test1:sql = select * from `test` where limits like '%2%' ; 
得到两条数据值. 
test2:sql = select * from `test` where FIND_IN_SET('2',limits); 
得到一条数据。

replace()函数

replace函数语法:
replace(str,str1,str2);

str:源字符串
str1:待替换字符串
str2:替换后的字符串

示例1：
select replace("http:/url/xxx-xxx/weiwei",'http:/url/','') 
->  xxx-xxx/weiwei

trim()函数

完整格式：TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
简化格式：TRIM([remstr FROM] str)

示例
select trim('  barnd   ')
-> barnd

select trim('http:/url/' from blog_url)
from user_submit

row_number() over 分组排序功能

row_number() over()分组排序功能
   在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于
   where  group by  order by 的执行。
   partition by 用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，
   它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。

	使用ROW_NUMBER删除重复数据 
---假设表TAB中有a,b,c三列，可以使用下列语句删除a,b,c都相同的重复行。

示例：

SELECT 
 empno,
 WORKDEPT,
 SALARY,
 Row_Number() OVER (partition by  workdept ORDER BY salary desc) rank 
 FROM employee

rank() over()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

select 
workdept,
salary,
rank() over(partition by workdept order by salary) as dense_rank_order
from emp 
order by workdept;

dense_rank() over()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的

select 
workdept,
salary,
dense_rank() over(partition by workdept order by salary) as dense_rank_order from emp 
order by workdept;

timestampdiff ()函数

语法：  timestampdiff（unit，begin，end）
begin和end可以为DATE或DATETIME类型，并且可允许参数为混合类型。

可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
SECOND 秒
MINUTE 分钟（返回秒数差除以60的整数部分）
HOUR 小时（返回秒数差除以3600的整数部分）
DAY 天数（返回秒数差除以3600*24的整数部分）
MONTH 月数
YEAR 年数


示例
select timestampdiff(minute,'2021-09-05 19:01:01','2021-09-05 19:06:01')
-> 5