oracle sql自定义排序规则,sql - Oracle自定义行排序列表 - 堆栈内存溢出

我写了一个查询，该查询返回总进入和退出的一些门列表。 我想通过自定义排序对它们进行排序。 我已经按门asc发出命令，但这仅是按字母顺序排序，我想像下面这样自定义排序

Gate 1

Gate 2

Gate 3

Gate 4

Gate 5

CPAR

NCY

CCT2

CCT3

NCT1

NCT2

NCT3

这是我的查询

SELECT

gate,

COUNT(total_in_rn) total_entry,

COUNT(total_out_rn) total_exit

FROM

(

SELECT

eofficeuat.gatelist.shortname AS gate,

CASE

WHEN eofficeuat.entrylog_vehicle.action = 'IN' THEN

ROW_NUMBER() OVER(

PARTITION BY eofficeuat.entrylog_vehicle.agent_id

ORDER BY

eofficeuat.entrylog_vehicle.agent_id

)

END AS total_in_rn,

CASE

WHEN eofficeuat.entrylog_vehicle.action = 'OUT' THEN

ROW_NUMBER() OVER(

PARTITION BY eofficeuat.entrylog_vehicle.agent_id

ORDER BY

eofficeuat.entrylog_vehicle.agent_id

)

END AS total_out_rn

FROM

eofficeuat.entrylog_vehicle

INNER JOIN eofficeuat.gatelist ON eofficeuat.entrylog_vehicle.gate_id =

eofficeuat.gatelist.id

INNER JOIN eofficeuat.cnf_agents ON eofficeuat.entrylog_vehicle.agent_id =

eofficeuat.cnf_agents.agent_id

INNER JOIN eofficeuat.gatepass ON eofficeuat.entrylog_vehicle.passnumber =

eofficeuat.gatepass.id

WHERE

eofficeuat.entrylog_vehicle.scantime BETWEEN

to_date('2019-11-07 11:00:00 am', 'YYYY-MM-DD HH:MI:SS pm')

and

to_date('2019-11-07 12:00:00 pm', 'YYYY-MM-DD HH:MI:SS pm')

ORDER BY

eofficeuat.gatelist.shortname desc

)

GROUP BY

gate

ORDER BY

gate

我怎样才能做到这一点？