MySQL基础练习题23-门店处理

目录

题目

准备数据 

分析数据 

方法一

方法二

---

题目

从分店明细表中获取门店面积。

准备数据 

-- 创建库
drop database if exists db_1;
create database db_1;
use db_1;

-- 创建门店面积表 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);

输入：

 门店面积表 tb_store_area

分析数据 

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

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

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

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

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

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

方法一

with t2 as (
    with t1 as (
        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
                    end rn
        from tb_store_area
    ) select
          store_no,
          area_type_no,
          area,
          row_number() over (partition by store_no) as rn
    from t1
)select store_no,area from t2
where rn = 1;

说明：利用case when将门店面积分级别，然后排序，选最高级别的 

简化

with t1 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
                end
            )
        ) as rn
    from tb_store_area
)
select * from t1
where rn = 1
;

方法二

第一步：先判断最大值是否是8，7，1，2，如果不是为null

-- 第一步：先判断最大值是否是8，7，1，2，如果不是为null
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函数选出第一个不是null的

-- 第二步：用coalesce函数选出第一个不是null的
select
    store_no,
    coalesce(
            max(if(area_type_no = 8, area, null)),
            max(if(area_type_no = 7, area, null)),
            max(if(area_type_no = 1, area, null)),
            max(if(area_type_no = 2, area, null))
    ) as area
from tb_store_area
group by store_no;