case when给列根据情况赋值（别名列）

红太狼 2022-12-27 07:29 222阅读 0赞

在else中才可以用列名，否则报名称找不到  
        (CASE WHEN default\_product\_code IS NOT NULL AND default\_product\_code=ci.item\_product\_code THEN '1'  
        WHEN default\_product\_code IS NOT NULL AND default\_product\_code !=ci.item\_product\_code THEN '0'  
        ELSE ci.is\_default END) AS is\_defaul

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.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,  
                cc.default\_product\_code,  
        ci.item\_product\_code,  
        p1.bu AS itembu,  
        ci.is\_default AS isdefault,  
                (CASE WHEN default\_product\_code IS NOT NULL AND default\_product\_code=ci.item\_product\_code THEN '1'  
        WHEN default\_product\_code IS NOT NULL AND default\_product\_code !=ci.item\_product\_code THEN '0'  
        ELSE ci.is\_default END) AS is\_defaul,  
        pi.channel ichannel,  
        le1.content as roundNameEn,  
        le2.content AS productNameEn,  
        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\_choice\_item ci ON cc.data\_version = ci.data\_version  
        AND cc.composition\_code = ci.choice\_code  
        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\_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 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 cc.round\_type=5  
        AND p.data\_version = '20201120a'  
        AND p.\`code\` in('508185')  
        order by  
        cc.sort,ci.sort