Oracle PL/SQL 中的自定义顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2809418/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Custom order in Oracle PL/SQL
提问by Andromeda
I have an oracle query in which and i want the result to be in custom order 'SENIOR DIRECTOR', 'DIRECTOR', 'MANAGER', 'EMPLOYEE' which is from the field GRADE_DESCRIPTON. I am using the below query.
我有一个 oracle 查询，我希望结果是来自 GRADE_DESCRIPTON 字段的自定义顺序“SENIOR DIRECTOR”、“DIRECTOR”、“MANAGER”、“EMPLOYEE”。我正在使用以下查询。
However I am not getting the desired result The order of the result im getting is 'SENIOR DIRECTOR','MANAGER', DIRECTOR,'EMPLOYEE'
但是我没有得到想要的结果我得到的结果的顺序是“高级总监”、“经理”、“总监”、“员工”
SELECT DISTINCT GRADE_DESCRIPTION
, HIRING_FORECATS.*
FROM GRADE_MASTER left join HIRING_FORECATS
ON (HIRING_FORECATS.GRADE = GRADE_MASTER.GRADE_DESCRIPTION
and HIRING_FORECATS.LOCATION = 'HO' )
order by decode
(GRADE_MASTER.GRADE_DESCRIPTION, 'SENIOR DIRECTOR', 'DIRECTOR', 'MANAGER', 'EMPLOYEE')
Any Suggestions??
有什么建议？？
回答by Quassnoi
ORDER BY DECODE(
GRADE_MASTER.GRADE_DESCRIPTION,
'SENIOR DIRECTOR', 1,
'DIRECTOR', 2,
'MANAGER', 3,
'EMPLOYEE', 4,
5)
回答by APC
The point about decode()is that for each pair of values it substitutes teh second value for the first. So your posted query sorts 'SENIOR DIRECTOR' as 'DIRECTOR', 'MANAGER' as 'EMPLOYEE' and everybody else randomly after that.
关键decode()是对于每对值，它用第二个值代替第一个值。因此，您发布的查询将“SENIOR DIRECTOR”排序为“DIRECTOR”，将“MANAGER”排序为“EMPLOYEE”，然后随机将其他所有人排序。
So what you need to do is assign a sort order to each value. The most future-proof way of handling this would be to assign a SORT_ORDER column to the GRADE_DESCRIPTION table, but that is not always practical. So if you only have one or two reports which need to be sorted in this way (seems likely) then you can just continue with the hard-coded descriptions:
所以你需要做的是为每个值分配一个排序顺序。处理此问题的最面向未来的方法是将 SORT_ORDER 列分配给 GRADE_DESCRIPTION 表，但这并不总是可行的。因此，如果您只有一两个报告需要以这种方式排序（似乎很可能），那么您可以继续使用硬编码描述：
SELECT DISTINCT GRADE_DESCRIPTION
, HIRING_FORECATS.*
FROM GRADE_MASTER left join HIRING_FORECATS
ON (HIRING_FORECATS.GRADE = GRADE_MASTER.GRADE_DESCRIPTION
and HIRING_FORECATS.LOCATION = 'HO' )
order by decode
(GRADE_MASTER.GRADE_DESCRIPTION
, 'SENIOR DIRECTOR', 10
, 'DIRECTOR', 20
, 'MANAGER', 30
, 'EMPLOYEE', 40
, 100)
It is a good idea to include a default value, just in case. I also like to leave large gaps in the sort numbers, to make it easier to slot in a new value.
包含一个默认值是个好主意，以防万一。我还喜欢在排序数字中留下很大的空白，以便更容易地插入新值。
回答by Bernard
You should use a SQL CASE instead of a PL/SQL DECODE:
您应该使用 SQL CASE 而不是 PL/SQL DECODE：
order by CASE WHEN GRADE_MASTER.GRADE_DESCRIPTION = 'SENIOR DIRECTOR' THEN 1
WHEN GRADE_MASTER.GRADE_DESCRIPTION = 'DIRECTOR' THEN 2
WHEN GRADE_MASTER.GRADE_DESCRIPTION = 'MANAGER' THEN 3
WHEN GRADE_MASTER.GRADE_DESCRIPTION = 'EMPLOYEE' THEN 4
ELSE 5
END
This will keep the parser from switching between SQL and PL/SQL modes.
这将防止解析器在 SQL 和 PL/SQL 模式之间切换。
回答by a'r
You could add a new column to GRADE_MASTERto represent the ordering or levels. This would allow you to change the order by clause to:
您可以添加一个新列GRADE_MASTER来表示排序或级别。这将允许您将 order by 子句更改为：
ORDER BY GRADE_MASTER.GRADE_ORDER
And has the advantage of only requiring changes to the GRADE_MASTERtable when a new grade is introduced.
并且具有仅GRADE_MASTER在引入新等级时才需要对表进行更改的优点。
