Shulou(Shulou.com)06/01 Report--

This article mainly explains the method of Oracle using decode function or CASE-WHEN to achieve custom sorting, the content is clear, interested friends can learn, I believe it will be helpful after reading.

1 problem

For SQL sorting, just add a field after order by, and you can choose descending or ascending order by adding desc or asc. But the collation is default, and numbers, times, strings, and so on all have their own default collation. Sometimes you need to sort by your own mind, rather than by the default collation of fields.

For example, the field values are English fields: Monday, Tuesday, Wednesday, etc. If you press the field, the default sorting rule is:

Friday

Monday

Saturday

Sunday

Thursday

Tuesday

Wednesday

What I really need is:

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

2 decode function

This can be achieved through the decode function, as follows:

Select * from table_dateorder by decode (DateStr, 'Monday',1' Tuesday',2 'Wednesday',3' Thursday',4 'Friday',5' Saturday',6 'Sunday',7, 0)

The following numbers can be discontinuous.

3 case when statement

In addition, you can also use case when to achieve:

Select * from table_dateorder by (CASE DateStr WHEN 'Monday' then 1 WHEN' Tuesday' then 2 WHEN 'Wednesday' then 3 WHEN' Thursday' then 4 WHEN 'Friday' then 5 WHEN' Saturday' then 6 WHEN 'Sunday' then 7 ELSE 0 END)

4 Summary

Of course, it is much more efficient to assign a field directly to the corresponding number, and then establish a mapping relationship through another table.

After reading the above, do you have a further understanding of how Oracle uses the decode function or CASE-WHEN to achieve custom sorting? if you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.