我试着按组和按月排序。因为我使用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);
^
如何根据发生的顺序将此查询更改为订单月份？
最佳答案
我想这是postgresql，因为你想用to_date（）？无论如何，您可以尝试@vkp的建议，但也可以更改GROUPBY子句中的列：
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
关于sql - 无法在别名列上使用to_date()，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41594961/