按联邦会计年度排列月份

在运行以下查询时，我试图找到一种显示联邦会计年度而不是正常顺序值的返回月份的方法。

（即我想按以下顺序显示月份：10月，11月，12月，1月，2月，3月，4月，5月6月，7月，8月，9月而不是1月至12月）感谢

select wrkgrp,
    sum (case when extract(month from reportdate) = 1 then 1 else 0 end) as January,
    sum (case when extract(month from reportdate) = 2 then 1 else 0 end) as February,
    sum (case when extract(month from reportdate) = 3 then 1 else 0 end) as March,
    sum (case when extract(month from reportdate) = 4 then 1 else 0 end) as April,
    sum (case when extract(month from reportdate) = 5 then 1 else 0 end) as May,
    sum (case when extract(month from reportdate) = 6 then 1 else 0 end) as June,
    sum (case when extract(month from reportdate) = 7 then 1 else 0 end) as July,
    sum (case when extract(month from reportdate) = 8 then 1 else 0 end) as August,
    sum (case when extract(month from reportdate) = 9 then 1 else 0 end) as September,
    sum (case when extract(month from reportdate) = 10 then 1 else 0 end) as October,
    sum (case when extract(month from reportdate) = 11 then 1 else 0 end) as November,
    sum (case when extract(month from reportdate) = 12 then 1 else 0 end) as December,
from workorder
where reportdate between to_date ('2014-10-01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
and   to_date ('2015-09-30 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
and   wrkgrp = 'PublicWorks'
group by 'wrkgrp;'

---

这些字段将按您在select语句中列出的顺序显示在您的结果中（水平）。 首先像下面这样列出10月份的结构：

select wrkgrp,
sum (case when extract(month from reportdate) = 10 then 1 else 0 end) as October,
sum (case when extract(month from reportdate) = 11 then 1 else 0 end) as November,
sum (case when extract(month from reportdate) = 12 then 1 else 0 end) as December,
sum (case when extract(month from reportdate) = 1 then 1 else 0 end) as January,
sum (case when extract(month from reportdate) = 2 then 1 else 0 end) as February,
sum (case when extract(month from reportdate) = 3 then 1 else 0 end) as March,
sum (case when extract(month from reportdate) = 4 then 1 else 0 end) as April,
sum (case when extract(month from reportdate) = 5 then 1 else 0 end) as May,
sum (case when extract(month from reportdate) = 6 then 1 else 0 end) as June,
sum (case when extract(month from reportdate) = 7 then 1 else 0 end) as July,
sum (case when extract(month from reportdate) = 8 then 1 else 0 end) as August,
sum (case when extract(month from reportdate) = 9 then 1 else 0 end) as September
from workorder
where reportdate between to_date ('2014-10-01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') ad to_date ('2015-09-30 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and
wrkgrp = 'PublicWorks'
group by 'wrkgrp;'

---

在您的order by子句中添加case语句以获得所需的排序。

ORDER BY 
CASE WHEN extract(month from reportdate) = 10 THEN 1 
CASE WHEN extract(month from reportdate) = 11 THEN 2 
ASC 

链接地址: http://www.djcxy.com/p/65613.html

上一篇: Ordering the Months By Federal Fiscal Year

下一篇: Oracle TRUNC date by quarter but with custom year start date