SQL 难点对比分析：WITH 和 CREATE VIEW 的区别

1. 简述

WITH 和 CREATE VIEW 有点类似Python给变量赋值，它们的区别是：

• WITH：创建临时表以供复用，执行完毕自动清理，无法执行增删改操作（只能被SELECT）

  创建临时表并使用：

  WITH a AS (SELECT * FROM table_a) # 在这里没有分号！
  
  SELECT col
  FROM a
  

• CREATE VIEW：创建虚拟表以供复用，不删除还存在，可以对视图进行DML操作

  创建 视图：

  CREATE VIEW a AS  # 这里没有括号！
  SELECT * FROM table_a;
  

  删除视图：

  DROP VIEW a;
  

2. 小结

	CREATE VIEW	WITH
语法	CREATE VIEW view_name AS SELECT 语句（视图）; SELECT 语句（你的查询）;	WITH name AS (SELECT 语句（临时表）) SELECT 语句（你的查询）;
用法	便于复用的视图，需要自行删除	便于复用的临时表 ，自动删除
注意1	上述语法分号的位置	上述语法分号的位置，即后面必须直接紧跟SQL语句
注意2	AS 后的语句不需要使用括号	AS 后的语句需要使用括号，再次强调with as后面必须紧跟使用CTE(Common table Express)
注意3	AS 不可省略	AS 不可省略

附：CTE(Common table Express) 待深入了解

3. 实例

表格user_behavior_cnt_grouping：

临时表

从上表中构造零时表：

WITH behavior_cnt AS
(SELECT COUNT(*) 'cnt_all',
			 SUM(IF(pv_grouping > 0, 1, 0)) 'pv_total',
			 SUM(IF(pv_grouping > 0 AND fav_grouping > 0 AND cart_grouping > 0 AND buy_grouping > 0, 1, 0)) 'pv-fav-cart-buy',
			 SUM(IF(pv_grouping > 0 AND fav_grouping > 0 AND cart_grouping = 0 AND buy_grouping > 0, 1, 0)) 'pv-fav-buy',
			 SUM(IF(pv_grouping > 0 AND fav_grouping = 0 AND cart_grouping > 0 AND buy_grouping > 0, 1, 0)) 'pv-cart-buy',
			 SUM(IF(pv_grouping > 0 AND fav_grouping = 0 AND cart_grouping = 0 AND buy_grouping > 0, 1, 0)) 'pv-buy',
			 SUM(IF(pv_grouping > 0 AND fav_grouping = 0 AND cart_grouping = 0 AND buy_grouping = 0, 1, 0)) 'not-buy'
FROM user_behavior_cnt_grouping)
-- 
SELECT * FROM behavior_cnt;

返回结果：

视图

相应 视图写法：

DROP VIEW  IF EXISTS behavior_cnt;  # 若已经存在先删除，便于代码多次运行

CREATE VIEW behavior_cnt AS
SELECT COUNT(*) 'cnt_all',
			 SUM(IF(pv_grouping > 0, 1, 0)) 'pv_total',
			 SUM(IF(pv_grouping > 0 AND fav_grouping > 0 AND cart_grouping > 0 AND buy_grouping > 0, 1, 0)) 'pv-fav-cart-buy',
			 SUM(IF(pv_grouping > 0 AND fav_grouping > 0 AND cart_grouping = 0 AND buy_grouping > 0, 1, 0)) 'pv-fav-buy',
			 SUM(IF(pv_grouping > 0 AND fav_grouping = 0 AND cart_grouping > 0 AND buy_grouping > 0, 1, 0)) 'pv-cart-buy',
			 SUM(IF(pv_grouping > 0 AND fav_grouping = 0 AND cart_grouping = 0 AND buy_grouping > 0, 1, 0)) 'pv-buy',
			 SUM(IF(pv_grouping > 0 AND fav_grouping = 0 AND cart_grouping = 0 AND buy_grouping = 0, 1, 0)) 'not-buy'
FROM user_behavior_cnt_grouping;

SELECT * FROM behavior_cnt;

返回结果一样：

注：上面代码为了反复运行 我在在最开始使用 DROP VIEW IF EXISTS先删除已经存在的视图。

补充

如何使用多个临时表

使用两个 WITH AS 的格式为：

WITH name_1 AS
(
  ....
),  
name_2 AS
(
  ....
)
SELECT ...;

实例：

WITH pv_favcart AS 
(SELECT SUM(fav_grouping) + SUM(cart_grouping)
FROM user_behavior_cnt_grouping
WHERE pv_grouping > 0 AND fav_grouping > 0 AND cart_grouping > 0),
pv_favcart_buy AS 
(SELECT SUM(fav_grouping) + SUM(cart_grouping)
FROM user_behavior_cnt_grouping
WHERE pv_grouping > 0 AND fav_grouping > 0 AND cart_grouping > 0 AND buy_grouping > 0)
SELECT (SELECT * FROM pv_favcart_buy) / (SELECT * FROM pv_favcart);

结果为0.4452，因为这计算的是两个数值的比值。

以及三个临时表的：

### pv-fav-buy
WITH pv AS
(
SELECT SUM(pv_grouping)
FROM user_behavior_cnt_grouping
WHERE pv_grouping > 0
),
pv_fav AS
(
SELECT SUM(fav_grouping)
FROM user_behavior_cnt_grouping
WHERE pv_grouping > 0 AND fav_grouping > 0
),
pv_fav_buy AS 
(
SELECT SUM(fav_grouping)
FROM user_behavior_cnt_grouping
WHERE pv_grouping > 0 AND fav_grouping > 0 AND buy_grouping > 0
)
SELECT (SELECT * FROM pv_fav) / (SELECT * FROM pv) '浏览用户到收藏用户的转化率',
			 (SELECT * FROM pv_fav_buy) / (SELECT * FROM pv_fav) '浏览且收藏的用户到成功购买的转化率';

结果：