sql - 不能在别名列上使用 to_date()

Question

我正在尝试按组和按月排序。因为我使用 to_char(date_field, 'Month') as month 将月份作为字符串而不是日期获取。这会导致结果按字母顺序而不是按时间顺序对月份进行排序。

select fac.facility_cd,
  fac.name,
  to_char(oh.create_date, 'Month') as month,
  count(ol.ord_line_id) as number_orders,
  sum(ol.retail_price) as total
from ord_header oh
  join ord_line ol using(ord_id)
  join ord_subline os using(ord_line_id)
  join facility fac using(facility_cd)
group by 1, 2, 3
order by 1, 2, 3;

产生:

fac_cd |                   name                 |   month   | number_orders |      total
-------+------------------------------------------+-----------+---------------+------------------
 502 | ART FURNITURE CO                         | December  |             1 |             1099
 503 | ABCOASTER                                | December  |             5 |           144.75
 503 | ABCOASTER                                | January   |             4 |            115.8
 503 | ABCOASTER                                | November  |             2 |             57.9
 205 | CHAR-GRILLER (A&J MFG LLC)               | April     |             6 |              424
 205 | CHAR-GRILLER (A&J MFG LLC)               | August    |            29 |             4786
 205 | CHAR-GRILLER (A&J MFG LLC)               | December  |            21 |          2397.98
 205 | CHAR-GRILLER (A&J MFG LLC)               | February  |             5 |              525
 205 | CHAR-GRILLER (A&J MFG LLC)               | January   |             2 |              148
 205 | CHAR-GRILLER (A&J MFG LLC)               | July      |            16 |             1504
 205 | CHAR-GRILLER (A&J MFG LLC)               | June      |            18 |             1762
 205 | CHAR-GRILLER (A&J MFG LLC)               | March     |            10 |              720

请注意月份是按字母顺序排列的。我搜索了一个答案，但是当我尝试解决这个问题时，我尝试了:

order by 1, 2, to_date(3, 'Month');

这给出了错误:

ERROR:  function to_date(integer, unknown) does not exist
LINE 11: order by 1, 2, to_date(3, 'Month');
                        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

我也试过这个:

order by 1, 2, extract('Month' from oh.create_date);

返回这个错误:

ERROR:  column "oh.create_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 11: order by 1, 2, extract('Month' from oh.create_date);
                                             ^

如何更改此查询以按月份出现的顺序对月份进行排序？

Answer 1

我想这是 postgresql，因为您尝试使用 to_date()？无论如何，您可以尝试@vkp 的建议，但也可以更改 group by 子句中的列:

group by 1, 2, date_trunc('month',oh.create_date)
order by 1, 2, date_trunc('month',oh.create_date);

我现在无法测试它，所以如果它不起作用，请告诉我今天晚些时候寻找决定:)

编辑

这不是最干净的方法，但类似的方法应该可以解决问题:

SELECT
  t.facility_cd,
  t.name,
  to_char(to_timestamp(m::text, 'MM'), 'Month') as month,
  t.number_orders,
  t.total
FROM 
(select fac.facility_cd,
  fac.name,
  extract(month from oh.create_date) as m,
  count(ol.ord_line_id) as number_orders,
  sum(ol.retail_price) as total
from ord_header oh
  join ord_line ol using(ord_id)
  join ord_subline os using(ord_line_id)
  join facility fac using(facility_cd)
group by 1, 2, 3
order by 1, 2, 3) as t