sql动态列值，动态查询条件，left join 动态过滤（不影响主表数据）

不念不忘少年蓝@ 2022-12-30 10:54 136阅读 0赞

动态列值：if case when decola等函数  
    where动态查询条件：<choose> <when>  
     <choose>  
            <when test="productCodes != null  and productCodes.size()>0">  
                case when tcp.template\_id is not null and tcp.dimension\_id is not null and tcp.template\_id=\#\{templateId\} and tcp.dimension\_id=\#\{dimensionId\}  
                then  tcp.template\_id=\#\{templateId\} and tcp.dimension\_id=\#\{dimensionId\} else 1=1 end  
            </when>  
        </choose>  
      
      
    left join 中途过滤不影响主数据:  
    过滤条件放的不同的left join不同，结果不同（按执行顺序过滤），放在需要过滤表后的那个left join才有效果，放在这后面其他left join  
    过滤不了  
    过滤条件放在where 会对之前的主表数据也过滤  
    where 中过滤影响主数据

SELECT  
            c.product\_code      productCode,  
            c.round,  
            c.\`name\`            roundNameCn,  
            c.is\_cost\_inclusive costInclusive,  
            c.default\_quantity  quantity,  
            c.max\_quantity      maxQuantity,  
            c.min\_quantity      minQuantity,  
            CASE  
                WHEN c.round\_type = 1 THEN  
                    0  
                WHEN c.round\_type = 5 THEN  
                    1  
                END             isChoices,  
            c.composition\_code  itemProductCode,  
            IF(  
                            c.default\_product\_code IS NULL || LENGTH(trim(c.default\_product\_code)) = 0,  
                            i.item\_product\_code,  
                            c.default\_product\_code  
                )               defaultProductCode  
        FROM  
            t\_product\_combo\_composition c  
                LEFT JOIN t\_product\_choice\_item i ON c.data\_version = i.data\_version  
                AND c.composition\_code = i.choice\_code  
                AND is\_default = 1  
        WHERE  
            c.data\_version = \#\{dataVersion\}  
    </select>  
      
    <select id="getStoreForMenuOnline"  
            resultType="com.mcd.cn.rdc.pcm.product.business.vo.store.StoreMenuVersionVO">

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  
        from (  
        select mvs.\*  
        from v\_menu\_version\_store mvs  
        -- 餐厅+渠道管理组的最新上线的pos菜单版本的上线时间  
        LEFT OUTER JOIN (  
        select store\_code,channel\_group\_code,max(published\_time) as published\_time from v\_menu\_version\_store 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\_menu\_version\_store  
        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菜单版本没有对应的上线版本，但是有对应的预发布版本  
        or (  
        not exists (select 1 from v\_menu\_version\_store 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菜单版本没有对应的版本时，找最新的上线版本  
        or (  
        not exists (select 1 from v\_menu\_version\_store 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\_menu\_version\_store 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\_menu\_version\_store 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  
    </select>  
      
    left join 中途过滤不影响主数据  
    过滤条件放的不同的left join不同，结果不同（按执行顺序过滤）  
    过滤条件放在where 会对之前的主表数据也过滤  
      
         
    <select id="getCompositionItemProductListNoChoiceForNew"  
            parameterType="com.mcd.cn.rdc.pcm.product.business.vo.template.TemplateParam"  
            resultMap="compositionProductVOResultOnline">  
        SELECT  
        p.id,  
        p. CODE,  
        p. NAME,  
        p.long\_name,  
        p.description,  
        p.type,  
        p.sales\_flag,  
        p.ui\_type,  
        p.dayweek,  
        p.bu,  
        p.has\_grill,  
        p.daypart,  
        p.channel,  
        p.title AS titleCn,  
        p.management\_name,  
        ttr. CODE tax\_rate\_type\_code,  
        ttr.tax\_rate,  
        date\_format(tsp.start\_date,'%Y%m%d') AS start\_date ,  
        date\_format(tsp.end\_date,'%Y%m%d') AS end\_date,  
        tsd.start\_time,  
        tsd.end\_time,  
        le.content as productLongNameEn,  
        cc.id ccid,  
        cc.round,  
        cc.name cc\_name,  
        cc.is\_cost\_inclusive,  
        case when cc.round\_type=5 then 1 else 0 end AS isChoices,  
        case when cc.round\_type=5 then cc.composition\_code else null end AS choicesCode,  
        cc.composition\_classification,  
        cc.default\_quantity,  
        cc.max\_quantity,  
        cc.min\_quantity,  
        ci.id as cid,  
        ci.item\_product\_code,  
        p1.bu AS itembu,  
        ci.is\_default,  
        pi.channel ichannel,  
        le1.content as roundNameEn,  
        le2.content AS productNameEn,  
        le5.content AS titleEn,  
        mst.CODE tab\_code,  
        mst.NAME tab\_name,  
        mst.image tab\_image  
        FROM  
        t\_product p  
        LEFT JOIN t\_product\_sales\_period tsp ON p. CODE = tsp.product\_code  
        AND p.data\_version = tsp.data\_version  
        LEFT JOIN t\_product\_sales\_daypart tsd ON p. CODE = tsd.product\_code  
        AND p.data\_version = tsd.data\_version  
        LEFT JOIN t\_master\_tax\_rate ttr ON p.tax\_rate\_type\_code = ttr. CODE  
        LEFT JOIN t\_international\_language le ON le.key\_code = p.code  
        AND le. LANGUAGE = 'en\_US'  
        AND le.field\_name = 'long\_name'  
        AND le.table\_name = 't\_product'  
        AND le.data\_version = p.data\_version  
        LEFT JOIN t\_product\_combo\_composition cc ON cc.data\_version = p.data\_version AND cc.product\_code=p.code  
        LEFT JOIN t\_product\_combo\_item ci ON cc.data\_version = ci.data\_version  
        AND cc.product\_code = ci.product\_code  
        AND cc.round = ci.round  
        LEFT JOIN t\_product pi ON pi.data\_version=ci.data\_version AND pi.\`code\` = ci.item\_product\_code  
        LEFT JOIN t\_international\_language le1 ON le1.key\_code = concat(cc.product\_code,"-",cc.round)  
        AND le1. LANGUAGE = 'en\_US'  
        AND le1.field\_name = 'name'  
        AND le1.table\_name = 't\_product\_combo\_composition'  
        AND le1.data\_version = cc.data\_version  
        LEFT JOIN t\_international\_language le2 ON le2.key\_code = p.CODE  
        AND le2.LANGUAGE = 'en\_US'  
        AND le2.field\_name = 'name'  
        AND le2.table\_name = 't\_product'  
        AND le2.data\_version = p.data\_version  
        LEFT JOIN t\_international\_language le5 ON le5.key\_code = p.CODE  
        AND le5.LANGUAGE = 'en\_US'  
        AND le5.field\_name = 'title'  
        AND le5.table\_name = 't\_product'  
        AND le5.data\_version = p.data\_version  
        LEFT JOIN t\_product p1 ON p1.data\_version = ci.data\_version  
        AND p1.\`code\` = ci.item\_product\_code  
        LEFT JOIN t\_template\_category\_product tcp ON tcp.data\_version = p.data\_version  
        AND tcp.product\_code = p.CODE  
        and  tcp.template\_id=\#\{templateId\} and tcp.dimension\_id=\#\{dimensionId\}  
        LEFT JOIN t\_template\_product\_tag tpt ON tcp.data\_version = tpt.data\_version  
        AND tcp.template\_id = tpt.template\_id  
        AND tcp.dimension\_id = tpt.dimension\_id  
        AND tcp.category\_id = tpt.category\_id  
        AND tcp.product\_code = tpt.product\_code  
        LEFT JOIN t\_master\_sales\_tag mst ON tpt.sales\_tag\_code = mst.CODE  
        AND mst.type = 2  
        WHERE  
        p.\`status\`='1' and p.channel is not null  
        AND cc.round\_type=1  
        AND p.data\_version = \#\{dataVersion\}  
        <if test="productCodes != null  and productCodes.size()>0">  
            AND p.code IN (  
            <foreach collection="productCodes" item="obj" index="index" separator=",">  
                \#\{obj\}  
            </foreach>  
            )  
        </if>  
        order by  
        cc.sort,ci.sort,mst.sort  
    </select>