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，相當于我建構的臨時列中每行裡的元素之後又進行了行轉列。