按星期一至星期几按星期几排序 [英] Sort by day of the week from Monday to Sunday

问题描述

如果我写

select ename, to_char(hiredate,'fmDay') as "Day" order by "Day";

然后根据Day like对结果进行排序；从星期五开始，然后是星期一，最后一个星期三， 就像按字符排序一样.

Then it sorts the result based on Day like; from Friday, then Monday and last Wednesday, like sorting by characters.

但是我想按星期几对它进行排序；从星期一到星期日.

But I want to sort it by day of the week; from Monday to Sunday.

推荐答案

您正在按自己的顺序获取它，因为您要按字符串进行排序(而这是行不通的，因为您没有从任何内容中进行选择).

You're getting it in the order you are because you're ordering by a string (and this wouldn't work because you're not selecting from anything).

您可以通过格式模型进行订购用于以数字形式D创建星期几，但是由于星期日是1，因此我建议使用mod()来完成这项工作.

You could order by the format model used to create the day of the week in numeric form, D, but as Sunday is 1 in this I would recommend using mod() to make this work.

即假设表

create table a ( b date );

insert into a
 select sysdate - level
  from dual
connect by level <= 7;

这将起作用:

select mod(to_char(b, 'D') + 5, 7) as dd, to_char(b, 'DAY')
  from a
 order by mod(to_char(b, 'D') + 5, 7)

这是一个 SQL小提琴进行演示.

在您的情况下，您的查询将变为:

In your case your query would become:

select ename, to_char(hiredate,'fmDay') as "Day" 
  from my_table
 order by mod(to_char(hiredate, 'D') + 5, 7)

这篇关于按星期一至星期几按星期几排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！