![](https://img-blog.csdnimg.cn/20201014180756928.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
MySQL
qq_43739780
这个作者很懒,什么都没留下…
展开
-
MySQL实现全连接功能(FULL OUTER JOIN)
MySQL不支持全连接可通过以下方式实现with name_1 as ( select 1 as id, '张三' as name, 20 as `number` union all select 2 as id, '李四' as name, 30 as `number`), name_2 as ( select 1 as id,原创 2022-02-11 17:52:24 · 5741 阅读 · 0 评论 -
根据符号将一行拆分为多行
目标原数据代码with cte as ( select 'A+B' as A, 1 as b UNION ALL SELECT 'C+D' as A, 2 as b) SELECT A, substring_index(substring_index(f.A, '+', (h.help_topic_id + 1)), '+', -(1)) as AA, bFROM cte f原创 2022-01-24 18:26:05 · 213 阅读 · 0 评论 -
lag和lead函数
功能:将A列错位n行语法:LAG(字段名 [,下移行数[, default_value]]) OVER (PARTITION BY 分区字段名,…ORDER BY 排序字段名[ASC|DESC],…)LEAD(字段名 [,上移行数[, default_value]]) OVER (PARTITION BY 分区字段名,…ORDER BY 排序字段名[ASC|DESC],…)default_value:补充错位后得到的新列中的null,见下例中的”999“with cte as (原创 2022-01-24 10:09:08 · 888 阅读 · 0 评论 -
MySQL 游标模板 + 动态SQL
存储过程中使用游标CREATE PROCEDURE PROCEDURE_NAME()BEGIN -- 定义变量,用于接受游标中的值 DECLARE A VARCHAR(10); DECLARE B VARCHAR(20); DECLARE C INT DEFAULT 0; DECLARE D INT DEFAULT 0; -- 定义退出游标标签 DECLARE DONE BOOLEAN DEFAULT 0; -- 定义游标 DECLA原创 2022-01-17 21:35:46 · 482 阅读 · 0 评论