文章目录
- 背景表
- SQL关键字
- 分析
- 具体Sql
- 注意点
- 补充:select的字段,也可以带有计算逻辑
背景表
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;