在sql中定义变量，动态运算、赋值

ゝ一世哀愁。 2022-12-30 12:54 64阅读 0赞

定义标量用子查询的概念，专门用一个子查询定义变量用@i:=赋值，用@i=比较,一般用于行号记录mysql没有rownum  
    (select @i:=0,@k:='') idx  
    @i:=case when @k=store\_code then @i+1 else 1 end as row\_no,@k:=store\_code  
    @k,开始第一行用自定义的初始值，后续用每行新赋值的  
      
    <select id="getAllStorePriceTireInfo"  
            resultType="com.mcd.cn.rdc.pcm.product.business.vo.store.StorePriceTierVO">  
        select s.\`code\`, s.price\_tier\_code as priceTierCode, mds.price\_tier\_code as mdsPriceTierCode, mcf.price\_tier\_code as mcCafePriceTierCode  
        from t\_store s  
        LEFT OUTER JOIN (  
         select \`code\` as beCode, price\_tier\_code, store\_code,@i:=case when @k=store\_code then @i+1 else 1 end as row\_no,@k:=store\_code  
         from t\_store\_be,(select @i:=0,@k:='') idx  
         where type=2 and is\_effective=1 and price\_tier\_code is not null  
         order by store\_code,updated\_date DESC,\`code\` DESC  
        ) mds on (mds.store\_code=s.\`code\` and mds.row\_no=1)  
        LEFT OUTER JOIN (  
         select \`code\` as beCode, price\_tier\_code, store\_code,@i:=case when @k=store\_code then @i+1 else 1 end as row\_no,@k:=store\_code  
         from t\_store\_be,(select @i:=0,@k:='') idx  
         where type=3 and is\_effective=1 and price\_tier\_code is not null  
         order by store\_code,updated\_date DESC,\`code\` DESC  
        ) mcf on (mcf.store\_code=s.\`code\` and mcf.row\_no=1)  
        where s.is\_effective=1  
    </select>