with as 和with recursive 用法

玛卡，巴卡，阿卡，哇卡，米卡，玛卡，呣！
玛卡，巴卡，阿巴，雅卡，伊卡，阿卡，噢！
哈姆，达姆，阿卡嗙，咿呀呦~ 玛卡，巴卡，阿卡，哇卡，米卡，玛卡，呣！

with as 用法

在mysql中，“with as”也叫子查询，用于定义一个sql片段，且该片段会被整个sql语句反复使用很多次，这个sql片段就相当于是一个公用临时表，语法为“with tmp as (查询语句)”。

WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。

常用语法

• 针对一个别名

WITH tem AS ( SELECT * FROM dept)

• 针对多个别名

WITH tem AS ( SELECT * FROM dept),
	tem1 AS ( SELECT * FROM dept1),
	tem2 AS ( SELECT * FROM dept2),
	...

• 构建tem临时表

WITH tem AS ( SELECT * FROM dept) SELECT * FROM tem

• 构建tem_code，tem_name临时表，并指定所需字段

WITH tem_code ( dept_code ) AS ( SELECT dept_code FROM dept ),
	tem_money ( dept_code, money ) AS ( SELECT dept_code, money FROM dept_expense ) 
SELECT
	* 
FROM
	tem_code
	INNER JOIN tem_money ON tem_code.dept_code = tem_money.dept_code

• 构建tem临时表，通过自己编写方式构建

WITH tem ( dept_code, dept_name, date ) AS (
	VALUES
		ROW ( "1614470462714445825", "营销部", "2022-01" ),
		ROW ( "1614470959278095767", "销售部", "2022-01" ),
		ROW ( "1614471266527641602", "设计部", "2022-01" ),
		ROW ( "1614471057957486593", "宣传部", "2022-01" ) 
	) SELECT
	*
FROM
	tem

其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它

with recursive递归的使用

语法

WITH recursive 表名 AS ( 
	初始语句（非递归部分） 
	UNION ALL 
	递归部分语句
)
[ SELECT| INSERT | UPDATE | DELETE]

with as 业务需求场景

查询统计各部门2022年01月总支出金额，如果该部门下为有支出金额显示0。

设有四个部门，在2022年01月只有两个部门有支出金额记录。

数据库表

以下表做演示使用

部门表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `dept_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门编码',
  `dept_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '1614470462714445825', '营销部');
INSERT INTO `dept` VALUES (3, '1614470959278095767', '销售部');
INSERT INTO `dept` VALUES (4, '1614471266527641602', '设计部');
INSERT INTO `dept` VALUES (5, '1614471057957486593', '宣传部');

部门消费表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept_expense
-- ----------------------------
DROP TABLE IF EXISTS `dept_expense`;
CREATE TABLE `dept_expense`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `dept_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `money` decimal(20, 8) UNSIGNED ZEROFILL NOT NULL,
  `create_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept_expense
-- ----------------------------
INSERT INTO `dept_expense` VALUES (1, '1614470959278095767', 3000.00000000, '2022-01-01 11:04:28');
INSERT INTO `dept_expense` VALUES (2, '1614470462714445825',9000.00000000, '2022-01-02 11:05:24');

数据查询

查询各部门总支出金额

查询各部门总支出金额，sql如下：

SELECT
	dt.dept_code,
	dt.dept_name,
	SUM( dte.money ) AS money 
FROM
	dept AS dt
	LEFT JOIN dept_expense AS dte ON dt.dept_code = dte.dept_code 
	WHERE DATE_FORMAT(dte.create_time,"%Y-%m") = DATE_FORMAT("2022-01-01","%Y-%m")
GROUP BY
	dt.dept_code,
	dt.dept_name

这样统计只统计了有支出金额数据的部门

结合临时表查询各部门总支出金额

查询各部门总支出金额，sql如下：

WITH tem ( dept_code, dept_name, date ) AS ( SELECT dept_code, dept_name, "2022-01" AS date FROM dept ) SELECT
	tem.dept_code,
	tem.dept_name,
	tem.date,
IF
	( ISNULL( mm.money ), 0, mm.money ) AS money 
FROM
	tem
	LEFT JOIN (
	SELECT
		dt.dept_code,
		dt.dept_name,
		SUM( dte.money ) AS money 
	FROM
		dept AS dt
		LEFT JOIN dept_expense AS dte ON dt.dept_code = dte.dept_code 
	WHERE
		DATE_FORMAT( dte.create_time, "%Y-%m" ) = DATE_FORMAT( "2022-01-01", "%Y-%m" ) 
	GROUP BY
		dt.dept_code,
		dt.dept_name 
	) AS mm ON mm.dept_code = tem.dept_code

构建临时表tem，在与mm表进行连表查询即可。结合mybatis动态sql使用更佳

with recursive 业务需求场景

仓库分布在各个省市区内，仓库表为一个自关联表
需求查询出京海市下所有的仓库

数据库表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for store
-- ----------------------------
DROP TABLE IF EXISTS `store`;
CREATE TABLE `store`  (
  `ID` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `PARENT_ID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上级仓库编码',
  `STORE_NAME` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '仓库名称',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '仓库管理' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of store
-- ----------------------------
INSERT INTO `store` VALUES (1, NULL, '京海市仓库');
INSERT INTO `store` VALUES (2, '1', '京海市-A仓库');
INSERT INTO `store` VALUES (3, '1', '京海市-B仓库');
INSERT INTO `store` VALUES (4, '3', '京海市-B-xxx仓库');
INSERT INTO `store` VALUES (5, NULL, '汉东市仓库');

数据查询

查询出京海市下所有的仓库

WITH recursive temp AS(
    SELECT * FROM store WHERE STORE_NAME = '京海市仓库'
    UNION ALL
    SELECT u.* FROM store u,temp t WHERE u.PARENT_ID=t.ID
)
SELECT * FROM temp;

如有问题请指出