sql_day14（获取各门店的面积）

描述：获取各门店的面积

• 获取各门店的面积

• 门店面积信息可以从分店面积明细表中获取。

  • 先取实际经营面积(8)，

  • 如果取不到（实际经营面积为空）再取经营面积(7)。

  • 如果取不到（经营面积为空）再取合同面积(1)。

  • 如果取不到（合同面积为空）再取外滩面积(2)。

  • 总结一下: 优先级就是 8 > 7 > 1 > 2

输入：

输出：
 

数据准备：

-- 创建门店面积表 tb_store_area
CREATE TABLE tb_store_area (store_no     VARCHAR(50),area_type_no INT,area         INT
);-- 插入数据到门店面积表 tb_store_area
INSERT INTO tb_store_area (store_no, area_type_no, area) VALUES
('T065', 8, 135),
('T067', 8, 139),
('T065', 7, 157),
('T066', 7, 178),
('T068', 2, 178),
('T065', 1, 158),
('T066', 1, 160),
('T067', 1, 145);

分析：

法一：

①考虑到优先顺序为 8 > 7 > 1 > 2 ，那么就以这样的顺序赋值1，2，3，4

selectstore_no, area_type_no, area,casewhen area_type_no=8 then 1when area_type_no=7 then 2when area_type_no=1 then 3when area_type_no=2 then 4else 5end as type_num
from tb_store_area

②根据这个type_num进行分组排序

selectstore_no, area_type_no, area,row_number() over (partition by store_noorder by (casewhen area_type_no=8 then 1when area_type_no=7 then 2when area_type_no=1 then 3when area_type_no=2 then 4else 5end) asc) as rn
from db_1.tb_store_area

③筛选出rn为1的数据

select store_no, area from t2
where rn=1

法二：

①求出各门店area_8,area_7.area_1,area_2的面积，没有的话显示为0

selectstore_no,max(if(area_type_no=8, area, null)) as area_8,max(if(area_type_no=7, area, null)) as area_7,max(if(area_type_no=1, area, null)) as area_1,max(if(area_type_no=2, area, null)) as area_2
from tb_store_area
group by store_no

②使用函数coalesce(area_8, area_7, area_1, area_2) 筛选出各门店第一个不为null的值

selectstore_no,coalesce(area_8, area_7, area_1, area_2) as area
from t1

代码：

# 法一
with t2 as (selectstore_no, area_type_no, area,row_number() over (partition by store_noorder by (casewhen area_type_no=8 then 1when area_type_no=7 then 2when area_type_no=1 then 3when area_type_no=2 then 4else 5end) asc) as rnfrom db_1.tb_store_area
)
select store_no, area from t2
where rn=1
;# 法二
with t1 as (selectstore_no,max(if(area_type_no=8, area, null)) as area_8,max(if(area_type_no=7, area, null)) as area_7,max(if(area_type_no=1, area, null)) as area_1,max(if(area_type_no=2, area, null)) as area_2from tb_store_areagroup by store_no
)
selectstore_no,coalesce(area_8, area_7, area_1, area_2) as area
from t1
;

总结：

① 通过分组然后根据多个 max(if(area_type_no=n, area, null)) 拼接出各门店的数据，将列变行

②coalesce（列1，列2，...）函数的使用 返回若干列里不为null的第一个值