复杂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 `降低|带来多货|提高销量`;

• 运算

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

分析

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语句，即

selectt.主要字段，t.真实销量, m.最终最大售卖量
from t
inner joinm
ont和m一一对应关系
wherexxx

具体Sql

select distinctt.`商品SKUID`,t.`网店ID`,t.`网店名称`,t.`销售日期`,t.`角色`,t.`修改前数值`,t.`修改后数值`,t.`真实销量`,m.total_stock as `实际修改量`,t.`OR值`,t.`降低|带来多货|提高销量`,t.`降低|带来多货|提高销量PCS`,t.`销售价`
from((selectresult.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 `销售价`fromsale_log as resultLeft joinsale_num as saleonresult.sku_id = sale.sku_idand result.poi_id = sale.poi_idand replace(result.sale_date,'_','') = sale.sale_datewhereresult.sale_date between '$$begindate' and '$$enddate'and result.update_status = 1and result.update_type = 1and sale.sale_num < result.update_max_saleOrder byresult.operate_timeDescLimit5000000)Union All(selectresult.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 `销售价`fromsale_log as resultLeft joinsale_num as saleonresult.sku_id = sale.sku_idand result.poi_id = sale.poi_idand replace(result.sale_date,'_','') = sale.sale_datewhereresult.sale_date between '$$begindate' and '$$enddate'and result.update_status = 0and result.update_type = 2and sale.sale_num <= result.origin_max_saleOrder byresult.operate_timeDescLimit5000000)
) as t
Inner join(selectsnap.total_stock as total_stock, snap.base_sku_id as base_sku_id, snap.poi_id as 	  poi_id,snap.schedule_date as schedule_datefromsale_log as resultLeft joinschedule as snaponresult.sku_id = snap.base_sku_idand result.poi_id = sanp.poi_idand result.sale_date = snap.schedule_dateand snap.dt = replace(snap.schedule_date,'-','')whereresult.sale_date between '$$begindate' and '$$enddate' 
) as mOnt.`商品SKUID` = m.base_sku_idand t.`网店ID` = m.poi_idand t.`销售日期` = m.schedule_date
wheret.`网店ID` in($poiId)

注意点

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

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

select e.name, e.age, e.salaty_of_month * 12 as `年薪`
fromemployee e
wherexxx;