SQL编程-组队学习打卡task04-集合运算

集合运算

• 1. 表的加减法
• • 1.1 并集运算A∪B - UNIOn
  • 1.2 交集运算A∩B - INTERSECT
  • 1.3 减法运算A-B - EXCEPT
  • 1.4 对称差A⊕B
• 2. 联结 JOIN
• • 2.1 内联结 INNER JOIN
  • • 2.1.1 结合 GROUP BY 子句使用内连结
    • 2.1.2 自联结
    • 2.1.3 内联结与关联子查询
    • 2.1.4 自然连结(NATURAL JOIN)
    • 2.1.5 使用联结求交集
  • 2.2 外联结
  • • 2.2.1 选择主表
    • 2.2.2 全外联结
    • 2.2.3 多表联结
    • 2.2.4 on子句的非等值联结
• 3 习题
• • 3.1 找出 product 和 product2 中售价高于 500 的商品的基本信息
  • 3.2 借助对称差的实现方式, 求product和product2的交集
  • 3.3 每类商品中售价最高的商品都在哪些商店有售 ？
  • 3.4 分别使用内连结和关联子查询每一类商品中售价最高的商品。
  • 3.5 用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
  • 旧题新做：使用内联结实现task03-复杂一点的查询-习题6.4

1. 表的加减法

集合运算：A∪B，A∩B，A-B，A⊕B，~A
集合运算符：UNIOn,INTERSECT, EXCEPT。集合运算符通常会去重
所有的表–以及查询结果–都可以视为集合，所以可以把表视为集合进行上述集合运算
理解为记录行数的增减。

1.1 并集运算A∪B - UNIOn

-- 表的加法-UNIOn
select product_id,product_name
from product p 
union
select product_id,product_name 
from product2;

product_id|product_name|
----------+------------+
0001      |T恤          |
0002      |打孔器         |
0003      |运动T恤        |
0004      |菜刀          |
0005      |高压锅         |
0006      |叉子          |
0007      |擦菜板         |
0008      |圆珠笔         |
0009      |手套          |
0010      |水壶          |

• UNIOn 与 OR的区别：对同一个表做并集运算，两者都可以；但是对不同的表做并集运算就需要UNIOn了。

-- 分别使用 UNIOn 或者 OR 谓词,找出成本利润率不足 30%或成本利润率未知的商品。
select *
from product p 
where sale_price /purchase_price <1.3
	or sale_price /purchase_price is null;

等价于	

select *
from product p 
where sale_price /purchase_price <1.3
union 
select *
from product p2 
where sale_price /purchase_price is null;

• 并集运算时保留重复行，可以用：UNIOn ALL

-- 想要知道 product 和 product2 中所包含的商品种类及每种商品的数量
select product_type,count(product_type)
from 
	(select product_type
	from product p 
	union all
	select product_type 
	from product2) as a
group by product_type ;

这里报错：Every derived table must have its own alias
解决办法：每一个派生出来的表都必须有一个自己的别名，所以给派生表加上别名即可

• 隐式数据类型转换：数据类型不同的列也可以放在一列来显示。hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜

• hive 数据倾斜：由于数据分布不均匀，造成数据大量的集中到一点，造成数据热点
• 表现现象：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大，单一reduce的记录数与平均记录数差异过大，最长时长远大于平均时长。
• 产生原因：

1. key分布不均匀
2. 业务数据本身的特性
3. 建表时考虑不周
4. 某些SQL语句本身就有数据倾斜

SELECt product_id, product_name, sale_price
  FROM Product
union
SELECt product_id, product_name, 'sale_price'
  FROM Product2;
product_id|product_name|sale_price|
----------+------------+----------+
0001      |T恤          |1000      |
0002      |打孔器         |500       |
0003      |运动T恤        |4000      |
0004      |菜刀          |3000      |
0005      |高压锅         |6800      |
0006      |叉子          |500       |
0007      |擦菜板         |880       |
0008      |圆珠笔         |100       |
0001      |T恤          |sale_price|
0002      |打孔器         |sale_price|
0003      |运动T恤        |sale_price|
0009      |手套          |sale_price|
0010      |水壶          |sale_price|


--时间日期类型和字符串,数值以及缺失值均能兼容。
select sysdate(),sysdate(),sysdate()
union
select 123,'123',null;
sysdate()          |sysdate()          |sysdate()          |
-------------------+-------------------+-------------------+
2022-07-18 16:08:08|2022-07-18 16:08:08|2022-07-18 16:08:08|
123                |123                |                   |

1.2 交集运算A∩B - INTERSECT

MySQL 8.0 不支持 INTERSECT。用 inner join 代替。

-- 求product表和product2表的交集
select p1.product_id, p1.product_name
from product p1 
inner join product p2
on p1.product_id =p2.product_id ;

也可以等价地将两个查询的检索条件用AND谓词连接来实现。

1.3 减法运算A-B - EXCEPT

MySQL 8.0 不支持 EXCEPT 。用not in 来实现

-- 找出只存在于Product表但不存在于Product2表的商品。
select *
from product p 
where product_id not in (select product_id from product2);

1.4 对称差A⊕B

对称差 = UNIOn求两个表的并集 - INTERSECT求两个表的交集 = (A-B) UNIOn (B-A)

-- 使用Product表和Product2表的对称差来查询哪些商品只在其中一张表
select * 
from product
where product_id not in (select product_id from product2)
union
select *
from product2
where product_id not in (select product_id from product);

2. 联结 JOIN

与表达加减法实现的‘行的增减’不同，联结将其他表的列拿过来进行‘添加列’的操作。

与关联子查询的区别：

与关联子查询的区别：
关联子查询以表 A 为主表, 然后根据表 A 的关联列的每一行的取值,逐个到表 B 中的关联列中去查找取值相等的行。如果外部主查询返回结果有10000行，那么内部子查询也要执行10000次，导致计算开销大。

2.1 内联结 INNER JOIN

语法：

select xxx
from table1
inner join tabel2
on 联结条件

找出东京商店里的衣服类商品的商品名称,商品价格,商品种类,商品数量信息。
对比两张表如下，用 product_id 将Product和ShopProduct这两张表连接起来。

select s.shop_id,s.shop_name,s.product_id,
		p.product_name,p.product_type,p.sale_price,s.quantity
from product p 
inner join shopproduct s 
on p.product_id = s.product_id ;

--加上where。执行顺序：FROM 子句(先联结两张表)->WHERe 子句->SELECt 子句
select s.shop_id,s.shop_name,s.product_id,
		p.product_name,p.product_type,p.sale_price,s.quantity
from product p 
inner join shopproduct s 
on p.product_id = s.product_id
where shop_name = '东京' and product_type = '衣服' ;

2.1.1 结合 GROUP BY 子句使用内连结

-- 每个商店中, 售价最高的商品的售价分别是多少?
select s.shop_id ,s.shop_name ,max(p.sale_price)as max_price
from shopproduct s 
inner join product p 
on s.product_id =p.product_id 
group by s.shop_id ;

2.1.2 自联结

一张表也可以与自身作连结。自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法。

2.1.3 内联结与关联子查询

-- 找出每个商品种类当中售价高于该类商品的平均售价的商品
-- 法1.子查询
select product_type,product_name,sale_price
from product p 
where sale_price > (select avg(sale_price)
					from product p2
					where p.product_type=p2.product_type
					group by product_type 
					);
				
select product_type,avg(sale_price)as avg_price
from product p 
group by product_type ;
-- 法2.内联结
select *
from product p1
inner join (select product_type,avg(sale_price)as avg_price
			from product p 
			group by product_type )as p2
on p1.product_type =p2.product_type
where p1.sale_price >p2.avg_price;

2.1.4 自然连结(NATURAL JOIN)

自动按照两个表都包含的列(比如product_id)进行等值内联结，不需要on。

SELECt *  FROM shopproduct NATURAL JOIN Product
等价于
SELECt  SP.product_id,SP.shop_id,SP.shop_name,SP.quantity
       ,P.product_name,P.product_type,P.sale_price
       ,P.purchase_price,P.regist_date  
  FROM shopproduct AS SP 
 INNER JOIN Product AS P 
    ON SP.product_id = P.product_id

2.1.5 使用联结求交集

-- 使用内连结求 Product 表和 Product2 表的交集
select p1.*
from product p1
inner join product2 p2
on p1.product_id =p2.product_id ;
product_id|product_name|product_type|sale_price|purchase_price|regist_date|
----------+------------+------------+----------+--------------+-----------+
0001      |T恤          |衣服          |      1000|           500| 2009-09-20|
0002      |打孔器         |办公用品        |       500|           320| 2009-09-11|
0003      |运动T恤        |衣服          |      4000|          2800|           |

2.2 外联结

外连结会根据外连结的种类有选择地保留无法匹配到的行。有三种：左连结, 右连结和全外连结。

-- 左连结     
FROM tabel1 LEFT  OUTER JOIN tabel2 ON 条件
-- 右连结     
FROM tabel1 RIGHT OUTER JOIN tabel2 ON 条件
-- 全外连结
FROM tabel1 FULL  OUTER JOIN tabel2 ON 条件

select s.shop_id ,s.shop_name ,s.product_id ,p.product_name ,p.sale_price 
from product p 
left outer join shopproduct s 
on s.product_id = p.product_id 
where p.product_name not in (select p2.product_name
							from product p2
							inner join shopproduct s2
							on p2.product_id =s2.product_id );

shop_id|shop_name|product_id|product_name|sale_price|
-------+---------+----------+------------+----------+
       |         |          |高压锅         |      6800|
       |         |          |圆珠笔         |       100|

多出的 2 条记录是高压锅和圆珠笔，这两条记录不在shopproduct表中。

2.2.1 选择主表

最终结果会包含主表内所有的数据。
LEFT 时 ，左侧的表是主表； RIGHT 时，右侧的表是主表.

--使用外连结从ShopProduct表和Product表中找出那些在某个商店库存少于50的商品及对应的商店
select s.shop_id ,s.shop_name ,s.product_id ,p.product_name ,p.sale_price ,s.quantity 
from product p 
left outer join shopproduct s 
on s.product_id = p.product_id 
where s.quantity<50;

shop_id|shop_name|product_id|product_name|sale_price|quantity|
-------+---------+----------+------------+----------+--------+
000A   |东京       |0001      |T恤          |      1000|      30|
000A   |东京       |0003      |运动T恤        |      4000|      15|
000B   |名古屋      |0002      |打孔器         |       500|      30|
000B   |名古屋      |0004      |菜刀          |      3000|      20|
000B   |名古屋      |0006      |叉子          |       500|      10|
000B   |名古屋      |0007      |擦菜板         |       880|      40|
000C   |大阪       |0003      |运动T恤        |      4000|      20|

这样就少了在所有商店都无货的高压锅和圆珠笔。
原因是先外联结，那么高压锅和圆珠笔的quantity就为null，再进行where筛选所以就筛掉了。
解决办法，先进行where筛选quantity<50的，再进行外联结就有高压锅和圆珠笔了。

select s.shop_id ,s.shop_name ,s.product_id ,p.product_name ,p.sale_price ,s.quantity 
from product p 
left outer join (select * from shopproduct where quantity < 50)as s
on s.product_id = p.product_id ;

shop_id|shop_name|product_id|product_name|sale_price|quantity|
-------+---------+----------+------------+----------+--------+
000A   |东京       |0001      |T恤          |      1000|      30|
000B   |名古屋      |0002      |打孔器         |       500|      30|
000A   |东京       |0003      |运动T恤        |      4000|      15|
000C   |大阪       |0003      |运动T恤        |      4000|      20|
000B   |名古屋      |0004      |菜刀          |      3000|      20|
       |         |          |高压锅         |      6800|        |
000B   |名古屋      |0006      |叉子          |       500|      10|
000B   |名古屋      |0007      |擦菜板         |       880|      40|
       |         |          |圆珠笔         |       100|        |

2.2.2 全外联结

对左表和右表的所有行都予以保留,
on可以关联的记录显示在一行，不能关联的记录就分别显示，null补充。
MySQL8.0 不支持全外联结，解决办法：左联结结果 UNIOn 右联结结果

2.2.3 多表联结

-- 多表内联结
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id
INNER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id

-- 多表外联结
FROM Product AS P
LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id
   

2.2.4 on子句的非等值联结

比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)都可以作为联结条件。

• • 非等值自左联结(SELF JOIN)
    希望对 Product 表中的商品按照售价赋予排名.

--使用自左连结对每种商品找出价格不低于它的商品
SELECt P1.product_id
       ,P1.product_name
       ,P1.sale_price
       ,P2.product_id AS P2_id
       ,P2.product_name AS P2_name
       ,P2.sale_price AS P2_price 
  FROM Product AS P1 
  LEFT OUTER JOIN Product AS P2 
    ON P1.sale_price <= P2.sale_price ;

product_id|product_name|sale_price|P2_id|P2_name|P2_price|
----------+------------+----------+-----+-------+--------+
0001      |T恤          |      1000|0001 |T恤     |    1000|
0001      |T恤          |      1000|0003 |运动T恤   |    4000|
0001      |T恤          |      1000|0004 |菜刀     |    3000|
0001      |T恤          |      1000|0005 |高压锅    |    6800|
0002      |打孔器         |       500|0001 |T恤     |    1000|
0002      |打孔器         |       500|0002 |打孔器    |     500|
0002      |打孔器         |       500|0003 |运动T恤   |    4000|
0002      |打孔器         |       500|0004 |菜刀     |    3000|
0002      |打孔器         |       500|0005 |高压锅    |    6800|
0002      |打孔器         |       500|0006 |叉子     |     500|
0002      |打孔器         |       500|0007 |擦菜板    |     880|
0003      |运动T恤        |      4000|0003 |运动T恤   |    4000|
0003      |运动T恤        |      4000|0005 |高压锅    |    6800|
0004      |菜刀          |      3000|0003 |运动T恤   |    4000|
0004      |菜刀          |      3000|0004 |菜刀     |    3000|
0004      |菜刀          |      3000|0005 |高压锅    |    6800|
0005      |高压锅         |      6800|0005 |高压锅    |    6800|
0006      |叉子          |       500|0001 |T恤     |    1000|
0006      |叉子          |       500|0002 |打孔器    |     500|
0006      |叉子          |       500|0003 |运动T恤   |    4000|
0006      |叉子          |       500|0004 |菜刀     |    3000|
0006      |叉子          |       500|0005 |高压锅    |    6800|
0006      |叉子          |       500|0006 |叉子     |     500|
0006      |叉子          |       500|0007 |擦菜板    |     880|
0007      |擦菜板         |       880|0001 |T恤     |    1000|
0007      |擦菜板         |       880|0003 |运动T恤   |    4000|
0007      |擦菜板         |       880|0004 |菜刀     |    3000|
0007      |擦菜板         |       880|0005 |高压锅    |    6800|
0007      |擦菜板         |       880|0007 |擦菜板    |     880|
0008      |圆珠笔         |       100|0001 |T恤     |    1000|
0008      |圆珠笔         |       100|0002 |打孔器    |     500|
0008      |圆珠笔         |       100|0003 |运动T恤   |    4000|
0008      |圆珠笔         |       100|0004 |菜刀     |    3000|
0008      |圆珠笔         |       100|0005 |高压锅    |    6800|
0008      |圆珠笔         |       100|0006 |叉子     |     500|
0008      |圆珠笔         |       100|0007 |擦菜板    |     880|
0008      |圆珠笔         |       100|0008 |圆珠笔    |     100|

-- 然后对售价不低于它的商品使用 COUNT 函数计数.
SELECt  product_id
       ,product_name
       ,sale_price
       ,COUNT(p2_id) AS my_rank
FROM (
        SELECt P1.product_id
               ,P1.product_name
               ,P1.sale_price
               ,P2.product_id AS P2_id
               ,P2.product_name AS P2_name
               ,P2.sale_price AS P2_price 
          FROM Product AS P1 
          LEFT OUTER JOIN Product AS P2 
            ON P1.sale_price <= P2.sale_price 
        ) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY my_rank; 

product_id|product_name|sale_price|my_rank|
----------+------------+----------+-------+
0005      |高压锅         |      6800|      1|
0003      |运动T恤        |      4000|      2|
0004      |菜刀          |      3000|      3|
0001      |T恤          |      1000|      4|
0007      |擦菜板         |       880|      5|
0002      |打孔器         |       500|      7|
0006      |叉子          |       500|      7|
0008      |圆珠笔         |       100|      8|

使用非等值自左连结进行累计求和，思路：
1.对每种商品使用自左连结, 找出比该商品售价价格更低或相等的商品
2.按照 P1.product_Id 分组,对 P2_price 求和

• • 交叉连结—— CROSS JOIN(笛卡尔积)
    交叉联结：在连结去掉 ON 子句，对两张表中的全部记录进行交叉组合。
    如a表10条记录，b表5条记录，则交叉联结结果有50条记录。
    交叉连接没有应用到实际业务中的原因，一是没有应用价值，二是运算消耗大。

-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
from a 
cross join b
--2.使用逗号分隔两个表,并省略 ON 子句
from a,b

交叉联结 + where (内联结中的on子句条件) = 内联结

3 习题 3.1 找出 product 和 product2 中售价高于 500 的商品的基本信息

select *
from product
where sale_price >500
union 
select *
from product2
where sale_price >500;

product_id|product_name|product_type|sale_price|purchase_price|regist_date|
----------+------------+------------+----------+--------------+-----------+
0001      |T恤          |衣服          |      1000|           500| 2009-09-20|
0003      |运动T恤        |衣服          |      4000|          2800|           |
0004      |菜刀          |厨房用具        |      3000|          2800| 2009-09-20|
0005      |高压锅         |厨房用具        |      6800|          5000| 2009-01-15|
0007      |擦菜板         |厨房用具        |       880|           790| 2008-04-28|
0009      |手套          |衣服          |       800|           500|           |
0010      |水壶          |厨房用具        |      2000|          1700| 2009-09-20|

3.2 借助对称差的实现方式, 求product和product2的交集

A∩B = A∪B - A⊕B
对称差 = UNIOn求两个表的并集 - INTERSECT求两个表的交集 = (A-B) UNIOn (B-A)

select * from product
union
select * from product2
where product_id not in (select product_id from product 
						where product_id not in (select product_id from product2)
						union
						select product_id from product2 
						where product_id not in (select product_id from product)
						);
product_id|product_name|product_type|sale_price|purchase_price|regist_date|
----------+------------+------------+----------+--------------+-----------+
0001      |T恤          |衣服          |      1000|           500| 2009-09-20|
0002      |打孔器         |办公用品        |       500|           320| 2009-09-11|
0003      |运动T恤        |衣服          |      4000|          2800|           |
0004      |菜刀          |厨房用具        |      3000|          2800| 2009-09-20|
0005      |高压锅         |厨房用具        |      6800|          5000| 2009-01-15|
0006      |叉子          |厨房用具        |       500|              | 2009-09-20|
0007      |擦菜板         |厨房用具        |       880|           790| 2008-04-28|
0008      |圆珠笔         |办公用品        |       100|              | 2009-11-11|

select * 
from (select * from product
	  union
	  select * from product2)as a
where product_id not in (select product_id from product 
						where product_id not in (select product_id from product2)
						union
						select product_id from product2 
						where product_id not in (select product_id from product)
						);
product_id|product_name|product_type|sale_price|purchase_price|regist_date|
----------+------------+------------+----------+--------------+-----------+
0001      |T恤          |衣服          |      1000|           500| 2009-09-20|
0002      |打孔器         |办公用品        |       500|           320| 2009-09-11|
0003      |运动T恤        |衣服          |      4000|          2800|           |

3.3 每类商品中售价最高的商品都在哪些商店有售 ？

-- 1.求出每类商品的最高sale_price，使用自联结找到最高sale_price对应的商品id/name/type
select p1.product_id ,p1.product_name ,p1.sale_price ,p1.product_type 
from product p1
inner join
(select product_type, max(sale_price) as max_sale_price
from product 
group by product_type )as a
on p1.product_type =a.product_type
and p1.sale_price =a.max_sale_price ;

product_id|product_name|sale_price|product_type|
----------+------------+----------+------------+
0002      |打孔器         |       500|办公用品        |
0003      |运动T恤        |      4000|衣服          |
0005      |高压锅         |      6800|厨房用具        |

-- 2.用product_id和shopproduct联结，找到商品对应的商店
select p1.product_id ,p1.product_name ,p1.sale_price ,p1.product_type ,
		a.max_sale_price,s.shop_name 
from product p1
inner join shopproduct s 
	on s.product_id = p1.product_id
inner join (select product_type, max(sale_price) as max_sale_price
			from product 
			group by product_type )as a
	on p1.product_type =a.product_type
	and p1.sale_price =a.max_sale_price ;
	
product_id|product_name|sale_price|product_type|max_sale_price|shop_name|
----------+------------+----------+------------+--------------+---------+
0002      |打孔器         |       500|办公用品        |           500|东京       |
0003      |运动T恤        |      4000|衣服          |          4000|东京       |
0002      |打孔器         |       500|办公用品        |           500|名古屋      |
0003      |运动T恤        |      4000|衣服          |          4000|名古屋      |
0003      |运动T恤        |      4000|衣服          |          4000|大阪       |

3.4 分别使用内连结和关联子查询每一类商品中售价最高的商品。

这题比3.3简单

-- 内联结
select p1.product_id,p1.product_name ,p1.product_type ,p2.max_sale_price 
from product p1 
inner join (select product_type,max(sale_price) as max_sale_price
			from product 
			group by product_type )as p2
on p1.product_type =p2.product_type 
and p1.sale_price =p2.max_sale_price;

-- 关联子查询
select product_id,product_name ,product_type ,sale_price as max_sale_price
from product p 
where (product_type,sale_price) 
		in (select product_type,max(sale_price)
			from product
			group by product_type);

product_id|product_name|product_type|max_sale_price|
----------+------------+------------+--------------+
0002      |打孔器         |办公用品        |           500|
0003      |运动T恤        |衣服          |          4000|
0005      |高压锅         |厨房用具        |          6800|

3.5 用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。

select p.product_id ,p.product_name ,p.sale_price ,
		(select sum(sale_price)
		from product p1
		where p.sale_price >=p1.sale_price)as cur_sum_price
from product p 
order by p.sale_price ;
product_id|product_name|sale_price|cur_sum_price|
----------+------------+----------+-------------+
0008      |圆珠笔         |       100|          100|
0002      |打孔器         |       500|         1100|
0006      |叉子          |       500|         1100|
0007      |擦菜板         |       880|         1980|
0001      |T恤          |      1000|         2980|
0004      |菜刀          |      3000|         5980|
0003      |运动T恤        |      4000|         9980|
0005      |高压锅         |      6800|        16780|

结果中打孔器对应cur_sum_price不对，应该为600。分析子查询的执行：

• 当主查询的圆珠笔100传进子查询时，
  select sum(sale_price)
  from product p1
  where 100 >= p1.sale_price)as cur_sum_price
  符合条件的有圆珠笔100.所以圆珠笔cur_sum_price=100
• 当主查询的打孔器500传进子查询时，
  select sum(sale_price)
  from product p1
  where 500 >= p1.sale_price)as cur_sum_price
  符合条件的有圆珠笔100，打孔器500，叉子500.所以打孔器cur_sum_price=1100.
  这里两个物品的sale_Price相等就是问题了。

得想办法在打孔器和叉子相等时，不计算这个叉子500。发现打孔器和叉子的product_id 不同，所以可以在sale_price相等时加上对product_id的限制。

select p.product_id ,p.product_name ,p.sale_price ,
		(select sum(sale_price)
		from product p1
		where p.sale_price > p1.sale_price 
		or (p.sale_price=p1.sale_price and p.product_id>=p1.product_id))as cur_sum_price
from product p 
order by p.sale_price,p.product_id  ;

product_id|product_name|sale_price|cur_sum_price|
----------+------------+----------+-------------+
0008      |圆珠笔         |       100|          100|
0002      |打孔器         |       500|          600|
0006      |叉子          |       500|         1100|
0007      |擦菜板         |       880|         1980|
0001      |T恤          |      1000|         2980|
0004      |菜刀          |      3000|         5980|
0003      |运动T恤        |      4000|         9980|
0005      |高压锅         |      6800|        16780|

这样就正确了。

旧题新做：使用内联结实现task03-复杂一点的查询-习题6.4

方法一：关联子查询

Create view avgpricebytype 
	as
		select product_id, product_name, product_type,sale_price,
				(select avg(sale_price) as sale_price_avg_type
				from product P2
				where p1.product_type = p2.product_type) # 使用关联子查询进行结算
		from product p1;

方法二：内联结

select p1.product_id, p1.product_name, p1.product_type,p1.sale_price,p2.sale_price_avg_type
from product p1
inner join (select product_type, avg(sale_price) as sale_price_avg_type
			from product
			group by product_type 
			) as p2
on p1.product_type =p2.product_type ;