mysql - 当我们需要使用upper(列名)排序时，如何在oracle中使用order by子句和union子句？

Question

以下查询出现错误:

SELECT DISTINCT dealer_state
FROM A
WHERE country='SS' 
UNION
SELECT DISTINCT dealer_state
FROM B
WHERE country='SS'
ORDER BY upper(dealer_state)

如何在 order by 子句中使用 upper(column) 进行排序？

Answer 1

假设您的查询给出的“错误”是它没有按照您的预期对 B 中的记录在 A 中的记录进行排序，您需要将 UNION 查询一个执行排序的子查询:

SELECT dealer_state FROM (
    SELECT dealer_state FROM A WHERE country='SS' 
  UNION
    SELECT dealer_state FROM B WHERE country='SS'
) t
ORDER BY upper(dealer_state)