Oracle 日期/按问题排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4597586/
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
Oracle date / order by question
提问by BourneAgain
I want to select a date from oracle table formatted like select (to_char(req_date,'MM/YYYY'))but I also want to order the result set on this date format.
我想从格式类似 select 的 oracle 表中选择一个日期，(to_char(req_date,'MM/YYYY'))但我也想以此日期格式对结果集进行排序。
I want them to be ordered like dates not strings.
我希望它们像日期而不是字符串一样排序。
Like this
像这样
09/2009
10/2009
11/2009
12/2009
01/2010
02/2010
03/2010
04/2010
05/2010
06/2010
07/2010
08/2010
09/2010
10/2010
11/2010
12/2010
Not like
不喜欢
01/2010
02/2010
03/2010
04/2010
05/2010
06/2010
07/2010
08/2010
09/2009
09/2010
10/2009
10/2010
11/2009
11/2010
12/2009
12/2010
Any way to do this in sql?
有什么办法可以在sql中做到这一点？
Full SQL is:
完整的 SQL 是：
SELECT (to_char(req_date,'MM/YYYY')) as monthYear, count(req_id) as count
FROM REQUISITION_CURRENT t
GROUP BY to_char(req_date,'MM/YYYY')
Thanks
谢谢
采纳答案by Nilesh
Try this
尝试这个
select monthyear,yr,month,count(req_id)
from
(
SELECT (to_char(req_date,'MM/YYYY')) as monthYear, to_char(req_date,'YYYY') yr, to_char(req_date,'mm') month, req_id
FROM REQUISITION_CURRENT t
) x
GROUP BY monthyear,yr,month
order by yr, month
回答by Ronnis
Try this. It works and it's efficient, but looks a little messy.
尝试这个。它有效且高效，但看起来有点凌乱。
select to_char(trunc(req_date, 'MM'),'MM/YYYY') as monthYear
,count(req_id) as count
from requisition_current
group
by trunc(req_date, 'MM')
order
by trunc(req_date, 'MM');
回答by rajah9
Please try
请尝试
Select req_date, (to_char(req_date,'MM/YYYY')) from MY_TABLE order by req_date
You are free to add additional sort fields, even if they are the same field.
您可以随意添加其他排序字段，即使它们是相同的字段。
回答by david
Try
尝试
SELECT ...
ORDER BY MIN(req_date)
That'll get around Oracle's rules about what can be selected after a GROUP BY.
这将绕过 Oracle 关于在 GROUP BY 之后可以选择的内容的规则。
回答by Klaus Byskov Pedersen
Just use order by req_dateinstead of order by to_char(req_date,'MM/YYYY').
只需使用order by req_date代替order by to_char(req_date,'MM/YYYY').
回答by tittaen?lg
I am considerably late to the party, but the most intuitive way I've found to achieve this is the following:
我参加聚会已经很晚了，但我发现实现这一目标的最直观的方法如下：
SELECT DISTINCT
to_char(req_date,'MM/YYYY') as monthYear,
count(req_id) as count
FROM
REQUISITION_CURRENT t
GROUP BY
to_char(req_date,'MM/YYYY')
ORDER BY
to_date(monthYear,'MM/YYYY')
It's may not the most computationally efficient method since it converts the date to a character and then back to a date, but that is precisely what you are asking a query like this to do. It also saves you from adding support columns or nesting subqueries.
它可能不是计算效率最高的方法，因为它将日期转换为字符然后再转换回日期，但这正是您要求这样的查询执行的操作。它还可以避免您添加支持列或嵌套子查询。
