复杂SQL解析

背景表

1、sale_log as result: 主表，大部分字段都是取自这个表

2、sale_num as sale：需要从这个表获取真实销量sale_num字段

3、schedule as snap: 需要从这个表获取最终售最大售卖量total_stock字段

SQL关键字

• Case When Then End

( Case 1 When 1 Then '提高销售量' END) as `降低|带来多货|提高销量`;
1

• 运算

(sale.sale_num - result.origin_max_sale) as `降低|带来多货|提高销量PCS`;
(sale.sale_num - result.origin_max_sale) * result.sku_price as `销售额提高`;
1
2

分析

1、result表 left join sale 表，并按照 where条件形成场景1-降多货

• 其中left join中的on内容是result和sale二者中一一对应的关系字段
• where条件即降多货的条件

2、result 表 left join sale 表，并按照 where条件形成场景2-提升售卖量即提升GMV

3、场景1 UNION ALL场景2，组成临时表t

4、result表left join snap表，组成临时表m

5、表t INNER JOIN 表m，组成最终的Sql语句，即

select
	t.主要字段，t.真实销量, m.最终最大售卖量
from t
inner join
	m
on
	t和m一一对应关系
where
	xxx
1
2
3
4
5
6
7
8
9

具体Sql

select distinct
	t.`商品SKUID`,
	t.`网店ID`,
	t.`网店名称`,
	t.`销售日期`,
	t.`角色`,
	t.`修改前数值`,
	t.`修改后数值`,
	t.`真实销量`,
	m.total_stock as `实际修改量`,
	t.`OR值`,
	t.`降低|带来多货|提高销量`,
	t.`降低|带来多货|提高销量PCS`,
	t.`销售价`
from(
	 (
        select
        	result.sku_id as `商品SKUID`,
            result.poi_id as `网店ID`,
        	sale.poi_name as `网店名称`,
        	result.sale_date as `销售日期`,
        	(CASE result.role WHEN 1 THEN '商家'  WHEN 2 THEN '普通用户' END) as `角色`,
        	result.origin_max_sale as `修改前数值`,
        	result.update_max_sale as `修改后数值`,
        	sale.sale_num as `真实销量`,
        	result.or_qty as `OR值`,
        	(CASE 1 WHEN 1 THEN '降低多货'  END) as `降低|带来多货|提高销量`,
        	(result.update_max_sale - result.origin_max_sale) as `降低|带来多货|提高销量PCS`,
        	result.sku_price as `销售价`
        from
        	sale_log as result
        Left join
        	sale_num as sale
        on
      		result.sku_id = sale.sku_id
        	and result.poi_id = sale.poi_id
        	and replace(result.sale_date,'_','') = sale.sale_date
        where
        	result.sale_date between '$$begindate' and '$$enddate'
        	and result.update_status = 1
        	and result.update_type = 1
        	and sale.sale_num < result.update_max_sale
        Order by
        	result.operate_time
        Desc
        Limit
        	5000000
    )
    
    Union All
    
    (
        select
        	result.sku_id as `商品SKUID`,
            result.poi_id as `网店ID`,
        	sale.poi_name as `网店名称`,
        	result.sale_date as `销售日期`,
        	(CASE result.role WHEN 1 THEN '商家'  WHEN 2 THEN '普通用户' END) as `角色`,
        	result.origin_max_sale as `修改前数值`,
        	result.update_max_sale as `修改后数值`,
        	sale.sale_num as `真实销量`,
        	result.or_qty as `OR值`,
        	(CASE 1 WHEN 1 THEN '提高销量'  END) as `降低|带来多货|提高销量`,
        	(result.update_max_sale - result.origin_max_sale) as `降低|带来多货|提高销量PCS`,
        	result.sku_price as `销售价`
        from
        	sale_log as result
        Left join
        	sale_num as sale
        on
      		result.sku_id = sale.sku_id
        	and result.poi_id = sale.poi_id
        	and replace(result.sale_date,'_','') = sale.sale_date
        where
        	result.sale_date between '$$begindate' and '$$enddate'
        	and result.update_status = 0
        	and result.update_type = 2
        	and sale.sale_num <= result.origin_max_sale
        Order by
        	result.operate_time
        Desc
        Limit
        	5000000
    )
) as t
Inner join(
    select
    	snap.total_stock as total_stock, snap.base_sku_id as base_sku_id, snap.poi_id as 	  poi_id,snap.schedule_date as schedule_date
    from
    	sale_log as result
    Left join
    	schedule as snap
    on
    	result.sku_id = snap.base_sku_id
    	and result.poi_id = sanp.poi_id
    	and result.sale_date = snap.schedule_date
    	and snap.dt = replace(snap.schedule_date,'-','')
    where
    	result.sale_date between '$$begindate' and '$$enddate' 
) as m

On
	t.`商品SKUID` = m.base_sku_id
	and t.`网店ID` = m.poi_id
	and t.`销售日期` = m.schedule_date
where
	t.`网店ID` in($poiId)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

注意点

• 注意‘’引号 和 ``的区别

补充：select的字段，也可以带有计算逻辑

select 
	e.name, e.age, e.salaty_of_month * 12 as `年薪`
from
	employee e
where
	xxx;
1
2
3
4
5
6