在SQL中有更好的替代CASE吗?

Question

我正在处理以下SQL查询(见下文)。是否有更好的方法来处理条件列"shipment_type"?我可能有更多的条件要添加，而且一遍又一遍地重复同一行的想法对我来说效率很低。任何建议吗?

SELECT
u.user_id,
u.user_email,
o.order_id,
o.product,
o.amount,
s.shipment_method,
s.shipment_date,
CASE
WHEN s.shipment_comment LIKE '%international%' THEN 'international'
WHEN s.shipment_comment LIKE '%worldwide%' THEN 'international'
WHEN s.shipment_comment LIKE '%global%' THEN 'international'
-- more conditions
ELSE 'national'
END AS shipment_type
FROM users AS u
JOIN orders AS o 
ON u.user_id = o.user_id
JOIN shipments AS s
ON s.order_id = o.order_id
WHERE
u.country IN ('US', 'UK');

Answer 1

可以使用正则表达式。在MySQL中，这看起来像:

(CASE WHEN s.shipment_comment regexp 'international|worldwide|global'
THEN 'international'
-- more conditions
ELSE 'national'
END) AS shipment_type

Postgres有类似的功能，但是使用~而不是REGEXP。