Mysql case when end 的巧妙使用

原创
2018/09/08 15:51
阅读数 176
           SELECT  m.template_id                     template_id,
                   nvl(m.fixed_fee,0)                fixed_fee,
                   nvl(m.unit_price_per_wgt,0)       unit_price_per_wgt,
                   nvl(m.unit_price_per_vol,0)       unit_price_per_vol,
                   m.subsidy_type                    subsidy_type,
                   nvl(m.subsidy_fee,0)              subsidy_fee,
                   nvl(m.ship_price_per_vol,0)       ship_price_per_vol,
                   nvl(m.ship_price_per_wgt,0)       ship_price_per_wgt,
                   nvl(m.ship_fixed_fee,0)           ship_fixed_fee,
                   --
                   c.from_weight              from_weight,
                   c.to_weight                to_weight,
                   c.from_volume              from_volume,
                   c.to_volume                to_volume,
                   c.charge_type              charge_type
           FROM    route_charge_rule_tab m, charge_template_tab c
           WHERE   ( weight_*1000 BETWEEN c.from_weight + 0.0001 AND nvl(c.to_weight, 9999999) OR  -- 重量按吨计费,但规则设定为KG
                     volume_      BETWEEN c.from_volume + 0.0001 AND nvl(c.to_volume, 9999999)
                   )
           AND     ( nvl(trans_spec_,'') = '' OR m.trans_spec = trans_spec_)
           AND     m.template_id = c.template_id
           AND     m.contract = contract_
           AND     m.warehouse = warehouse_
           AND     m.route_id = route_id_
		   ORDER BY CASE when ( weight_*1000 BETWEEN c.from_weight AND nvl(c.to_weight, 9999999) and  -- 优先and的顺序
								volume_      BETWEEN c.from_volume AND nvl(c.to_volume, 9999999)
                                ) THEN 0 ELSE 1 END, nvl(m.priority_no,0) DESC, route_id asc; -- 按优先度,然后按编号的顺序升序

这里的

ORDER BY CASE when ( weight_*1000 BETWEEN c.from_weight AND nvl(c.to_weight, 9999999) and  -- 优先and的顺序
								volume_      BETWEEN c.from_volume AND nvl(c.to_volume, 9999999)
                                ) THEN 0 ELSE 1 END

order by 里面插入 case when 在 or 里面 的插入 and 的条件优先排序;

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部