My SQL-DW组队学习3️⃣

在行方向上实现不同种类的聚合（聚合函数+group by）

select product_type,sum(sale_price)as sum_price
from product
group by product_type;
           

在列方向上实现不同种类的聚合（聚合函数+case when...then）

select sum(case when(product_type="衣服")then sale_price else null end)as sum_price_cloth,
sum(case when(product_type="办公用品")then sale_price else null end)as sum_price_office,
sum(case when(product_type="厨房用具")then sale_price else null end)as sum_price_kitchen
from product;
           

行转列问题 （把多行的数据聚合成一行可以使用聚合函数，

max()

、

min()

、

sum()、avg()

 在这里都可以使用）

注意：其中 sum\avg 不能用于待转换列为 文本 时！！！

select 聚合函数(case when...then语句)as xxx,

          聚合函数(case when...then语句)as yyy

from Atable

group by 具体情况具体分析;

练习题

3.1

create view Viewpractice5_1 
as
select product_name,sale_price,regist_date
from product
where (sale_price>=1000 
and regist_date="20090920");
           

3.2 不符合刚才设定的sale_price>=1000的规则，加不进去。

视图插入数据时，原表也会插入数据，而原表数据插入时不满足约束条件，所以会报错。

Error Code: 1423. Field of view 'shop.viewpractice5_1' underlying table doesn't have a default value
           

3.3

select product_id,product_name,product_type,sale_price,
(select avg(sale_price) from product )as sale_price_all
from product;
           

3.4 先写出我的错误答案

create view AvgPriceByType
as
select product_id,product_name,product_type,sale_price,
(select avg (sale_price) as a_vg from product group by product_type)as avg_sale_price
from product;
           

再放上标准答案

create view AvgPriceByType
as
select product_id,product_name,product_type,sale_price,
(select avg (sale_price) as a_vg from product as p1
where p1.product_id=p2.product_id
 group by product_type)as avg_sale_price
from product as p2;
           

差别就在 【where p1.product_id=p2.product_id 】这里，我以为既然是同一张表就不用整两个昵称了，事实证明我错了，他还是需要一个架起子查询和原查询的桥梁(两个表中的product_id)。

3.5 函数coalesce(null,null,2...null)->2

正确

3.6 这里我错得离谱。。。

谓词无法与 

NULL

 进行比较

NOT IN

 的参数中不能包含 

NULL

，否则，查询结果通常为空

3.7之后写不出来了。。。

alter table product add appraise varchar(10);
select
max(case when sale_price<1000 then appraise="低档商品" else null end)as low_price,
max(case when sale_price between 1001 and 3000 then appraise="中档商品" else null end)as mid_price,
max(case when sale_price>3001 then appraise="高档商品" else null end)as high_price
from product
group by appraise;
           

正确答案 

SELECT SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END)               AS low_price,
       SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price,
       SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END)               AS high_price
  FROM product;
           

这个方法多好啊，直接能算出数量了就也不用group by，我这个整的挺麻烦的，还加了一列，不但如此最后不能得出数量，以后要多多灵活运用0和1！！！

最后解释一下这里0和1，相当于我构建的临时列中每行里的元素之后又进行了行转列。