mybatis示例用法
SELECT
FROM fact_store_label_m
WHERE id=#{id}
SELECT
FROM fact_store_label_m
WHERE 1=1
ORDER BY #{params.orderBy}
LIMIT #{params.page},#{params.pageSize}
SELECT count(1) FROM fact_store_label_m WHERE 1=1
select concat(SUBSTRING(MIN(settle_month),1,4),'01') start_settle_month,max(settle_month) end_settle_month from fact_store_label_m limit 1;
select
t.id id,
t.store_no store_no,
t.store_name store_name,
t.store_channel_desc2 store_channel_desc2,
t.region_name region_name,
concat(t.normal_province_name,t.normal_city_name) normal_province_name,
GROUP_CONCAT(distinct (case shop_brand_flag when '主营' then brand_name else '' end)) brand_name,
concat((case d.profit_loss
when '00000100020001' then '高利润店铺'
when '00000100020002' then '正常店铺'
when '00000100020003' then '预警店铺'
when '00000100020004' then '亏损店铺'
when '00000100020005' then '非正常店铺'
else ''
end),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.overall_operation_label_desc),',','、'),''),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.abcd_label_desc),',','、'),''),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.holidays_effect_label_desc),',','、'),''),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.business_city_label_desc),',','、'),''),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.store_age_label_desc),',','、'),''),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.color_hot_label_desc),',','、'),''),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.heel_hot_label_desc),',','、'),''),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.style_hot_label_desc),',','、'),''),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.order_price_label_desc),',','、'),''),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.order_avg_label_desc),',','、'),''),'、',
ifnull(REPLACE(GROUP_CONCAT(distinct t.payway_favour_label_desc),',','、'),''),'、') label,
round(d.terminal_revenue,2) terminal_income,
round((d.terminal_revenue/sum(business_area)),2) ground_effect,
round((d.terminal_revenue/d.price_revenue)*100,2) avg_discount,
round((d.gross_profit/d.terminal_revenue)*100,2) gross_profit_margin,
round((d.business_expense/d.terminal_revenue)*100,2) expense_rate,
round((d.profit/d.terminal_revenue)*100,2) profit_margin
from fact_store_label_m t
left join dw_shop_channel_analyze d
on t.store_no=d.store_no
and t.settle_month=d.financial_month
where 1=1
and d.profit_loss=#{params.profitLossLabelNo}
group by t.store_no
order by ${params.orderBy}
LIMIT #{params.page},#{params.pageSize}
SELECT
count(f.c)
FROM
(
SELECT
count(1) c
FROM
fact_store_label_m t
LEFT JOIN dw_shop_channel_analyze d
ON t.store_no = d.store_no
AND t.settle_month = d.financial_month
where 1=1
and d.profit_loss=#{params.profitLossLabelNo}
GROUP BY
t.store_no
) f
SELECT
t.store_no,
t.store_name,
GROUP_CONCAT(distinct (case shop_brand_flag when '主营' then brand_name else '' end)) brand_name,
t.store_age,
t.store_open_status,
sum(t.business_area) business_area,
t.business_circle_name,
t.staff_number,
t.region_name,
t.order_unit_name,
t.manage_city_name,
t.business_city_name,
t.cman_address,
t.store_channel_desc2,
replace(GROUP_CONCAT(distinct t.overall_operation_label_desc),',','、') overall_operation_label_desc,
(case d.profit_loss
when '00000100020001' then '高利润店铺'
when '00000100020002' then '正常店铺'
when '00000100020003' then '预警店铺'
when '00000100020004' then '亏损店铺'
when '00000100020005' then '非正常店铺'
else ''
end) as profit_loss_label_desc,
replace(GROUP_CONCAT(distinct t.abcd_label_desc),',','、') abcd_label_desc,
replace(GROUP_CONCAT(distinct t.holidays_effect_label_desc),',','、') holidays_effect_label_desc,
replace(GROUP_CONCAT(distinct t.business_city_label_desc),',','、') business_city_label_desc,
replace(GROUP_CONCAT(distinct t.store_age_label_desc),',','、') store_age_label_desc,
replace(GROUP_CONCAT(distinct t.color_hot_label_desc),',','、') color_hot_label_desc,
replace(GROUP_CONCAT(distinct t.heel_hot_label_desc),',','、') heel_hot_label_desc,
replace(GROUP_CONCAT(distinct t.style_hot_label_desc),',','、') style_hot_label_desc,
replace(GROUP_CONCAT(distinct t.order_price_label_desc),',','、') order_price_label_desc,
replace(GROUP_CONCAT(distinct t.order_avg_label_desc),',','、') order_avg_label_desc,
replace(GROUP_CONCAT(distinct t.payway_favour_label_desc),',','、') payway_favour_label_desc,
round(d.terminal_revenue,2) terminal_income,
round((d.terminal_revenue/sum(t.business_area)),2) ground_effect,
round((d.terminal_revenue/d.price_revenue)*100,2) avg_discount,
round((d.gross_profit/d.terminal_revenue)*100,2) gross_profit_margin,
round((d.business_expense/d.terminal_revenue)*100,2) expense_rate,
round((d.profit/d.terminal_revenue)*100,2) profit_margin
FROM
fact_store_label_m t
LEFT JOIN dw_shop_channel_analyze d ON t.store_no = d.store_no
AND t.settle_month = d.financial_month
WHERE 1=1
and t.store_no=#{params.storeNo}
GROUP BY
t.store_no
LIMIT 1
SELECT
FROM
fact_store_label_m
where 1=1 AND settle_month = #{settleMonth} AND store_no = #{storeNo}
select min(opendt) start_settle_month from fact_store_label_m group by opendt limit 1
SELECT
d.financial_month
FROM
dw_shop_channel_analyze d
INNER JOIN fact_store_label_m m ON d.financial_month = m.settle_month
AND d.store_no = m.store_no
group by d.financial_month
id,settle_month,store_no,store_code,store_name,brand_unit_code,brand_unit_name,brand_code,brand_name,shop_brand_flag,store_category_no1,store_category_desc1,normal_province_no,normal_city_no,normal_county_no,normal_province_name,normal_city_name,normal_county_name,store_channel_no2,store_channel_desc2,business_city_no,business_city_name,manage_city_no,manage_city_name,order_unit_no,order_unit_name,region_no,region_name,affiliation,affiliation_desc,main_brand_detail_no,secondary_brand_detail_no,area_total,business_area,opendt,closedt,store_level,digits,staff_number,floor_number,telno,contact,mobile_phone,assistant_amount,store_age,store_open_status,cman_address,business_circle_no,business_circle_name,overall_operation_label_no,profit_loss_label_no,abcd_label_no,holidays_effect_label_no,business_city_label_no,store_age_label_no,color_hot_label_no,heel_hot_label_no,style_hot_label_no,order_price_label_no,order_avg_label_no,payway_favour_label_no,overall_operation_label_desc,profit_loss_label_desc,abcd_label_desc,holidays_effect_label_desc,business_city_label_desc,store_age_label_desc,color_hot_label_desc,heel_hot_label_desc,style_hot_label_desc,order_price_label_desc,order_avg_label_desc,payway_favour_label_desc,bdp_update_time
and (t.store_no like concat(concat('%',#{params.keyword}),'%') or t.store_name like concat(concat('%',#{params.keyword}),'%'))
and t.settle_month=#{params.settleMonth}
and t.affiliation=#{params.affiliation}
and t.region_no=#{params.regionNo}
and t.store_channel_no2=#{params.channel}
and t.brand_unit_code=#{params.brandUnitCode}
and t.brand_code=#{params.brandNo}
and t.shop_brand_flag='主营'
and t.normal_province_no=#{params.provinceNo}
and t.overall_operation_label_no=#{params.overallOperationLabelNo}
and t.abcd_label_no=#{params.abcdLabelNo}
and t.holidays_effect_label_no=#{params.holidaysEffectLabelNo}
and t.business_city_label_no=#{params.businessCityLabelNo}
and t.store_age_label_no=#{params.storeAgeLabelNo}
and FIND_IN_SET(#{params.colorHotLabelNo},t.color_hot_label_no)
and FIND_IN_SET(#{params.heelHotLabelNo},t.heel_hot_label_no)
and t.style_hot_label_no=#{params.styleHotLabelNo}
and t.order_price_label_no=#{params.orderPriceLabelNo}
and t.order_avg_label_no=#{params.orderAvgLabelNo}
and FIND_IN_SET(#{params.paywayFavourLabelNo},t.payway_favour_label_no)
