Mysql8-新特性-公用表表达式

mysql8引入了公用表表达式

什么是公用表表达式？

答：公用表表达式是一个命名的临时结果集。它存在于单个查询语句中，主要作用就是可以代替子查询，并且可以被后面的查询多次引用。

普通公用表表达式的语法结构：

WITH
CTE名称 AS （子查询）
SELECT|DELETE|UPDATE 语句;

普通公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用。

假设我们有一个商品信息表（demo.goodsmaster），它保存的是商品信息，还有一个每日销售统计表（demo.goodsmaster_detail），保存的是每日的销售统计信息。现在超市经营者想要查出都卖了什么商品，我们就可以先通过子查询查出所有销售过的商品的商品编号，然后查出这些商品的商品信息.

我们先创建商品信息表：

/*Table structure for table `goodsmaster` */

DROP TABLE IF EXISTS `goodsmaster`;

CREATE TABLE `goodsmaster` (
  `barcode` text,
  `goodsname` text,
  `price` double DEFAULT NULL,
  `itemnumber` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`itemnumber`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

/*Data for the table `goodsmaster` */

insert  into `goodsmaster`(`barcode`,`goodsname`,`price`,`itemnumber`) values ('0001','书',0.47,1),('0002','笔',0.44,2),('0003','胶水',0.19,3);

 先后创建每日销售统计表：

/*Table structure for table `goodsmaster_detail` */

DROP TABLE IF EXISTS `goodsmaster_detail`;

CREATE TABLE `goodsmaster_detail` (
  `barcode` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '商品条码',
  `goodsname` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '商品名称',
  `price` double DEFAULT NULL COMMENT '进货价格',
  `itemnumber` int NOT NULL AUTO_INCREMENT COMMENT '编号',
  `sale_price` double DEFAULT NULL COMMENT '出售价格',
  `unit` int DEFAULT NULL COMMENT '商品卖出份数',
  `specification` text COMMENT '特别说明',
  PRIMARY KEY (`itemnumber`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3;

/*Data for the table `goodsmaster_detail` */

insert  into `goodsmaster_detail`(`barcode`,`goodsname`,`price`,`itemnumber`,`sale_price`,`unit`,`specification`) values ('0001','书',0.47,1,10.47,2,'无'),('0002','笔',0.44,2,2.44,5,'无'),('0003','胶水',0.19,3,1.19,3,'无'),('0002','笔',0.44,4,2.45,4,'无'),('0002','笔',0.44,5,2,20,'大客户优惠');

 最后采用公用表表达式的写法如下：

WITH
cte AS (SELECT DISTINCT barcode FROM demo.`goodsmaster_detail`)
SELECT * FROM demo.goodsmaster a JOIN cte ON (a.barcode = cte.barcode);

 等价于子查询：

SELECT * FROM demo.`goodsmaster` a WHERE a.`barcode` IN (SELECT DISTINCT barcode FROM demo.`goodsmaster_detail`)