用sqlview实现复杂功能数据准备
1,基础数据情况不通的时候可以left join 不同left join 代表不同情况数据
2,以1为基础where中用 not exits 按优先级获取对应情况数据
or (
not exists ()
and mvs.draft_published_time=spd.draft_published_time
)
基础视图:
SELECT
`sgs`.`store_code` AS `store_code`,
`tv`.`pos_menu_version` AS `pos_menu_version`,
`tv`.`sequence_no` AS `sequence_no`,
`tv`.`name` AS `version_name`,
`tv`.`data_version` AS `data_version`,
`tv`.`status` AS `version_status`,
`tv`.`draft_published_time` AS `draft_published_time`,
`tv`.`published_time` AS `published_time`,
`tu`.`channel_group_code` AS `channel_group_code`,
`tu`.`template_id` AS `template_id`,
`tsg`.`store_group_code` AS `store_group_code`
FROM
(((
`t_templatev` `tv`
JOIN `t_templateu` `tu` ON (((
`tu`.`data_version` = `tv`.`data_version`
)
AND ( `tu`.`pos_menu_version` = `tv`.`pos_menu_version` )
AND ( `tu`.`sequence_no` = `tv`.`sequence_no` ))))
JOIN `t_templatesg` `tsg` ON (((
`tsg`.`data_version` = `tv`.`data_version`
)
AND ( `tsg`.`channel_group_code` = `tu`.`channel_group_code` )
AND ( `tsg`.`template_id` = `tu`.`template_id` ))))
JOIN `t_store_grous` `sgs` ON (((
`sgs`.`data_version` = `tv`.`data_version`
)
AND ( `sgs`.`store_group_code` = `tsg`.`store_group_code` ))))
WHERE
(
`tv`.`status` IN ( 12, 13, 14, 15 ))
业务sql:
其实下面的a也可作为基础视图
select
a.store_code as storeCode,a.pos_menu_version as posMenuVersion,a.sequence_no as sequenceNo
,a.version_name as versionName,a.data_version as dataVersion
,a.version_status as status,a.channel_group_code as channelGroupCode,a.store_group_code as storeGroupCode
,sg.`name` as storeGroupName,mcg.`name` as channelGroupName,pv.`name` as dataVersionName
,s.`name` as storeName,sg.store_be_type as storeBeType
from (
select mvs.*
from v_m_versions mvs
-- 餐厅+渠道管理组的最新上线的pos菜单版本的上线时间
LEFT OUTER JOIN (
select store_code,channel_group_code,max(published_time) as published_time from v_m_versions group by
store_code,channel_group_code
) spo ON spo.store_code=mvs.store_code and spo.channel_group_code=mvs.channel_group_code
-- 餐厅+渠道管理组的最新预发布的pos菜单版本的预发布时间
LEFT OUTER JOIN (
select store_code,channel_group_code,max(draft_published_time) as draft_published_time from v_m_versions
group by store_code,channel_group_code
) spd ON spd.store_code=mvs.store_code and spd.channel_group_code=mvs.channel_group_code
-- 餐厅最新的POS菜单版本
LEFT OUTER JOIN (
select spv.store_code,spv.pos_menu_version
from t_store_pos_version spv
INNER JOIN (select store_code, max(synchronized_time) as synchronized_time from t_store_pos_version group by
store_code) last
ON last.store_code=spv.store_code and last.synchronized_time=spv.synchronized_time
) spc ON spc.store_code=mvs.store_code
Where
-- 白名单店看预发布以后的版本,非白名单店只看线上版本
<choose>
<when test="testStores != null and testStores.size()>0">
case when mvs.store_code in (
<foreach collection="testStores" item="obj" index="index" separator=",">
#{obj}
</foreach>
)then mvs.version_status in (12,13,14,15) else mvs.version_status=15 end
</when>
<otherwise>
mvs.version_status=15
</otherwise>
</choose>
and (
-- 餐厅+渠道管理组,当前pos菜单版本有对应的上线版本
(mvs.version_status=15 and mvs.pos_menu_version=spc.pos_menu_version
-- 取这个pos菜单版本里最后发布的那个
and mvs.published_time=(select max(published_time) from v_m_versions where store_code=mvs.store_code and channel_group_code=mvs.channel_group_code and pos_menu_version=mvs.pos_menu_version)
)
-- 餐厅+渠道管理组,当前pos菜单版本没有对应的上线版本,但是有对应的预发布版本
or (
not exists (select 1 from v_m_versions where store_code=mvs.store_code and
channel_group_code=mvs.channel_group_code and version_status=15 and pos_menu_version=spc.pos_menu_version)
and mvs.version_status in (12,13,14) and mvs.pos_menu_version=spc.pos_menu_version
-- 取这个pos菜单版本里最后发布的那个
and mvs.draft_published_time=(select max(draft_published_time) from v_m_versions where store_code=mvs.store_code and channel_group_code=mvs.channel_group_code and pos_menu_version=mvs.pos_menu_version)
)
-- 餐厅+渠道管理组,当前pos菜单版本没有对应的版本时,找最新的上线版本
or (
not exists (select 1 from v_m_versions where store_code=mvs.store_code and
channel_group_code=mvs.channel_group_code and pos_menu_version=spc.pos_menu_version)
and mvs.published_time=spo.published_time
)
-- 餐厅+渠道管理组,当前pos菜单版本没有对应的版本,且没有最新上线版本时,找最新的预发布版本
or (
not exists (select 1 from v_m_versions where store_code=mvs.store_code and
channel_group_code=mvs.channel_group_code and pos_menu_version=spc.pos_menu_version)
and not exists (select 1 from v_m_versions where store_code=mvs.store_code and
channel_group_code=mvs.channel_group_code and published_time=spo.published_time)
and mvs.draft_published_time=spd.draft_published_time
)
)
) a
LEFT OUTER JOIN t_store_group sg ON (sg.data_version=a.data_version and sg.`code`=a.store_group_code)
INNER JOIN t_master_channel_group mcg ON (mcg.`code`=a.channel_group_code) and mcg.is_effective=1
LEFT OUTER JOIN t_product_version pv ON(pv.data_version=a.data_version)
INNER JOIN t_store s ON (s.`code`=a.store_code) and s.is_effective=1
where 1=1
<if test="storeGroupCode != null and storeGroupCode != ''">
and a.store_group_code=#{storeGroupCode}
</if>
<if test="priceTierCode != null and priceTierCode != ''">
and s.price_tier_code=#{priceTierCode}
</if>
<if test="name != null and name != ''">
and (lower(s.`name`) like concat('%',lower(#{name}),'%') or lower(s.`code`) like
concat('%',lower(#{name}),'%'))
</if>
<if test="channelGroupCode != null and channelGroupCode != ''">
and a.channel_group_code=#{channelGroupCode}
</if>
order by a.store_code,a.channel_group_code,a.version_status desc