Mysql case when end 的巧妙使用

最新推荐文章于 2023-04-24 15:53:08 发布

weixin_33849942

最新推荐文章于 2023-04-24 15:53:08 发布

阅读量231

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3556610/blog/1996836

版权

2019独角兽企业重金招聘Python工程师标准>>>

           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 的条件优先排序;

转载于:https://my.oschina.net/u/3556610/blog/1996836