mybatis示例用法(4)

最新推荐文章于 2023-07-04 03:01:26 发布

I.T10001

最新推荐文章于 2023-07-04 03:01:26 发布

阅读量371

点赞数

文章标签： mybatis

本文链接：https://blog.csdn.net/ls_call520/article/details/103915922

版权

mybatis示例用法

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD MMapper w.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

</resultMap>

</resultMap>

SELECT <include refid="baseColumn" />

FROM fact_store_label_m

WHERE id=#{id}

</select>

SELECT <include refid="baseColumn" />

FROM fact_store_label_m

WHERE 1=1

ORDER BY #{params.orderBy}

</if>

LIMIT #{params.page},#{params.pageSize}

</select>

SELECT count(1) FROM fact_store_label_m WHERE 1=1

</select>

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>

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}

</if>

group by t.store_no

order by ${params.orderBy}

LIMIT #{params.page},#{params.pageSize}

</select>

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}

</if>

GROUP BY

t.store_no

) f

</select>

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}

</if>

GROUP BY

t.store_no

LIMIT 1

</select>

SELECT

FROM

fact_store_label_m

where 1=1 AND settle_month = #{settleMonth} AND store_no = #{storeNo}

</select>

select min(opendt) start_settle_month from fact_store_label_m group by opendt limit 1

</select>

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

</select>

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

</sql>

and (t.store_no like concat(concat('%',#{params.keyword}),'%') or t.store_name like concat(concat('%',#{params.keyword}),'%'))

</if>

and t.settle_month=#{params.settleMonth}

</if>

and t.affiliation=#{params.affiliation}

</if>

and t.region_no=#{params.regionNo}

</if>

and t.store_channel_no2=#{params.channel}

</if>

and t.brand_unit_code=#{params.brandUnitCode}

</if>

and t.brand_code=#{params.brandNo}

</if>

and t.shop_brand_flag='主营'

</if>

and t.normal_province_no=#{params.provinceNo}

</if>

and t.overall_operation_label_no=#{params.overallOperationLabelNo}

</if>

and t.abcd_label_no=#{params.abcdLabelNo}

</if>

and t.holidays_effect_label_no=#{params.holidaysEffectLabelNo}

</if>

and t.business_city_label_no=#{params.businessCityLabelNo}

</if>

and t.store_age_label_no=#{params.storeAgeLabelNo}

</if>

and FIND_IN_SET(#{params.colorHotLabelNo},t.color_hot_label_no)

</if>

and FIND_IN_SET(#{params.heelHotLabelNo},t.heel_hot_label_no)

</if>

and t.style_hot_label_no=#{params.styleHotLabelNo}

</if>

and t.order_price_label_no=#{params.orderPriceLabelNo}

</if>

and t.order_avg_label_no=#{params.orderAvgLabelNo}

</if>

and FIND_IN_SET(#{params.paywayFavourLabelNo},t.payway_favour_label_no)

</if>

</sql>

</mapper>

I.T10001

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mybatis示例用法(4)

mybatis示例用法<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD MMapper w.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="...
复制链接

扫一扫