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 ENDorder by 里面插入 case when 在 or 里面 的插入 and 的条件优先排序;
