SQL 从星期一到星期日按星期几排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13662451/
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
Sort by day of the week from Monday to Sunday
提问by debaonline4u
If I write
如果我写
select ename, to_char(hiredate,'fmDay') as "Day" order by "Day";
Then it sorts the result based on Day like; from Friday, then Monday and last Wednesday, like sorting by characters.
然后它根据 Day 对结果进行排序；从周五开始，然后是周一和上周三，比如按字符排序。
But I want to sort it by day of the week; from Monday to Sunday.
但我想按星期几排序；从周一到周日。
回答by Ben
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).
你按照你的顺序得到它，因为你是按字符串排序的（这不起作用，因为你没有从任何东西中进行选择）。
You could order by the format modelused 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.
您可以按用于以数字形式创建星期几的格式模型进行排序D，但由于周日是 1，因此我建议使用它mod()来完成这项工作。
i.e. assuming the table
即假设表
create table a ( b date );
insert into a
select sysdate - level
from dual
connect by level <= 7;
This would work:
这会起作用：
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)
Here's a SQL Fiddleto demonstrate.
这是一个用于演示的SQL Fiddle。
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)
回答by Andy Lester
Take a look at other formats for TO_CHAR. Instead of 'fmDay' use 'D' and it will give you the day of the week from 1 to 7. Then you can easily sort on it.
看看其他格式的TO_CHAR. 使用 'D' 代替 'fmDay'，它会给你从 1 到 7 的星期几。然后你可以很容易地对它进行排序。
Here's a list of date formats: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm
以下是日期格式列表：http: //docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm
回答by 2maraf
I just encountered the same requirement -- to order a query result by day of the week, but not starting with Sunday. I used the following query in Oracle to start w/ Monday. (Modify it to start the ordering w/ any day of the week, e.g., change 'MONDAY' to 'TUESDAY'.)
我刚刚遇到了同样的要求——按星期几排序查询结果，但不是从星期日开始。我在 Oracle 中使用以下查询从星期一开始。（修改它以在一周中的任何一天开始排序，例如，将“星期一”更改为“星期二”。）
SELECT ename, to_char(hiredate, 'fmDAY') AS "Day"
FROM emp
ORDER BY (next_day(hiredate, 'MONDAY') - hiredate) DESC
Or:
或者：
SELECT ename, to_char(hiredate, 'fmDAY') AS "Day"
FROM emp
ORDER BY (hiredate - next_day(hiredate, 'MONDAY'))
回答by RealNmae
Why to complicate when you can add another column with numbers 1-7 corresponding to days and then sort by this column...
当您可以添加另一列数字 1-7 对应于天，然后按此列排序时，为什么要复杂化...
回答by Chris Saxon
The Dformat mask of to_charmaps days of the week to the values 1-7.
将星期几映射到值 1-7的D格式掩码to_char。
But!
但！
The output of this depends on the client's setting for NLS_TERRITORY. The US considers Sunday to be day 1. Whereas most of the rest of the world consider Monday to be the start:
此输出取决于客户端的 NLS_TERRITORY 设置。美国认为星期日是第 1 天。而世界其他大多数国家认为星期一是开始：
alter session set nls_territory = AMERICA;
with dts as (
select date'2018-01-01' + level - 1 dt
from dual
connect by level <= 7
)
select to_char ( dt, 'Day' ) day_name,
to_char ( dt, 'd' ) day_number
from dts
order by day_number;
DAY_NAME DAY_NUMBER
Sunday 1
Monday 2
Tuesday 3
Wednesday 4
Thursday 5
Friday 6
Saturday 7
alter session set nls_territory = "UNITED KINGDOM";
with dts as (
select date'2018-01-01' + level - 1 dt
from dual
connect by level <= 7
)
select to_char ( dt, 'Day' ) day_name,
to_char ( dt, 'd' ) day_number
from dts
order by day_number;
DAY_NAME DAY_NUMBER
Monday 1
Tuesday 2
Wednesday 3
Thursday 4
Friday 5
Saturday 6
Sunday 7
Sadly, unlike many other NLS parameters, you can't pass NLS_TERRITORY as the third parameter of to_char:
遗憾的是，与许多其他 NLS 参数不同，您不能将 NLS_TERRITORY 作为 的第三个参数传递to_char：
with dts as (
select date'2018-01-01' dt
from dual
)
select to_char ( dt, 'Day', 'NLS_DATE_LANGUAGE = SPANISH' ) day_name
from dts;
DAY_NAME
Lunes
with dts as (
select date'2018-01-01' dt
from dual
)
select to_char ( dt, 'Day', 'NLS_TERRITORY = AMERICA' ) day_name
from dts;
ORA-12702: invalid NLS parameter string used in SQL function
So any solution relying on Dfor sorting is a bug!
所以任何依赖D排序的解决方案都是一个错误！
To avoid this, subtract the most recent Monday from the date (if today is Monday, the most recent Monday = today). You can do this with the IWformat mask. Which returns the start of the ISO week. Which is always a Monday:
为避免这种情况，请从日期中减去最近的星期一（如果今天是星期一，则最近的星期一 = 今天）。您可以使用IW格式掩码执行此操作。它返回 ISO 周的开始。这总是星期一：
with dts as (
select date'2018-01-01' + level - 1 dt
from dual
connect by level <= 7
)
select to_char ( dt, 'Day' ) day_name,
( dt - trunc ( dt, 'iw' ) ) day_number
from dts
order by day_number;
DAY_NAME DAY_NUMBER
Monday 0
Tuesday 1
Wednesday 2
Thursday 3
Friday 4
Saturday 5
Sunday 6
For Sunday-Saturday sorting, add one to the date before finding the start of the ISO week:
对于周日-周六排序，在找到 ISO 周的开始之前在日期上加一：
with dts as (
select date'2018-01-01' + level - 1 dt
from dual
connect by level <= 7
)
select to_char ( dt, 'Day' ) day_name,
( dt - trunc ( dt + 1, 'iw' ) ) day_number
from dts
order by day_number;
DAY_NAME DAY_NUMBER
Sunday -1
Monday 0
Tuesday 1
Wednesday 2
Thursday 3
Friday 4
Saturday 5
回答by Lukasz Szozda
If you want Monday to be always treated as first day of week you could use:
如果您希望星期一始终被视为一周的第一天，您可以使用：
-- Not affected by NLS_TERRITORY
-- ALTER SESSION SET NLS_TERRITORY="AMERICA"; -- Sunday is first day of week
-- ALTER SESSION SET NLS_TERRITORY="GERMANY"; -- Monday is first day of week
SELECT *
FROM tab
ORDER BY 1+TRUNC(dt)-TRUNC(dt,'IW');
回答by Ramya Roy
SELECT
*
FROM
classes
ORDER BY
CASE
WHEN Day = 'Sunday' THEN 1
WHEN Day = 'Monday' THEN 2
WHEN Day = 'Tuesday' THEN 3
WHEN Day = 'Wednesday' THEN 4
WHEN Day = 'Thursday' THEN 5
WHEN Day = 'Friday' THEN 6
WHEN Day = 'Saturday' THEN 7
END ASC
Assuming that user has a table called classes in that table user has class_id (primary key), class name, Day.
假设用户有一个名为 classes 的表，该表中的 user 有 class_id（主键）、类名、Day。
回答by bootsoon
It's simple.
这很简单。
SELECT last_name, hire_date,TO_CHAR(hire_date, 'DAY') DAY
FROM employees
ORDER BY TO_CHAR(hire_date - 1, 'd');
TO_CHAR(hire_date - 1, 'd')puts a 'Monday' into a box named 'Sunday'.
TO_CHAR(hire_date - 1, 'd')将“星期一”放入名为“星期日”的框中。
回答by bb8
As it's said, there's a function for it:
正如它所说，它有一个功能：
SELECT *
FROM table
ORDER BY WEEKDAY(table.date);
回答by Michele La Ferla
I improved on Ben's answer by giving you a result which starts from 1 rather than 0. The query would be like this:
我通过给你一个从 1 而不是 0 开始的结果来改进 Ben 的答案。查询将是这样的：
select
mod(to_char(b, 'D')+ 5, 7) +1 as dd,
to_char(b, 'DAY')
from a
order by mod(to_char(b, 'D')+ 5, 7);
On the other hand, if you want your week to start from Sunday, then you should use this query:
另一方面，如果你希望你的一周从星期日开始，那么你应该使用这个查询：
select
mod(to_char(b, 'D')+ 6, 7) +1 as dd,
to_char(b, 'DAY')
from a
order by mod(to_char(b, 'D')+ 6, 7)
Hope this helps :)
希望这可以帮助 ：）
