SQL学习打卡Task4

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql

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

代码1如下：

SELECT product_id,product_name,sale_price,SUM(P2_price) AS sum_price

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

AND P1.product_id = P2.product_id)

ORDER BY P1.sale_price,P2.product_id)

GROUP BY product_id,product_name,sale_price

ORDER BY sale_price,sum_price;

输入这段代码会报错

报错提示：Every derived table must have its own alias 每个派生表都必须有自己的别名

这时可以明白通过子查询建立的派生表必须设立一个别名用AS

正确代码如下：

SELECT product_id,product_name,sale_price,SUM(P2_price) AS sum_price

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

AND P1.product_id = P2.product_id)

ORDER BY P1.sale_price,P2.product_id) AS x

GROUP BY product_id,product_name,sale_price

ORDER BY sale_price,sum_price;

现在代码就可以正常运行了~