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

select
    store_no, area_type_no, area,
    case
       when area_type_no=8 then 1
       when area_type_no=7 then 2
       when area_type_no=1 then 3
       when area_type_no=2 then 4
       else 5
    end as type_num
from tb_store_area

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

select
    store_no, area_type_no, area,
    row_number() over (
        partition by store_no
        order by (case
                       when area_type_no=8 then 1
                       when area_type_no=7 then 2
                       when area_type_no=1 then 3
                       when area_type_no=2 then 4
                       else 5
                   end) 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

select
    store_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的值

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

代码：

# 法一
with t2 as (
    select
        store_no, area_type_no, area,
        row_number() over (
            partition by store_no
            order by (case
                           when area_type_no=8 then 1
                           when area_type_no=7 then 2
                           when area_type_no=1 then 3
                           when area_type_no=2 then 4
                           else 5
                       end) asc
        ) as rn
    from db_1.tb_store_area
)
select store_no, area from t2
where rn=1
;

# 法二
with t1 as (
    select
        store_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
)
select
    store_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的第一个值