Postgresql
在postgresql中，我从datetime列中提取了month，它显示为1.0而不是01、2.0而不是02。
SELECT
cast(extract(year from ord_datetime) as int ) as year,
extract(month from ord_datetime) as month
FROM analysis a inner join orders o on o.ord_an = a.an_id
inner join groups g on g.gr_id = a.an_group
预期答案是：
year month group sum
-----------------------------
2018 01 1 16
2018 02 1 31
2018 03 1 38
2018 04 1 53
我的答案是：
year ord_datetime month gr_id ord_id an_id
----------------------------------------------------------------
2018 2018-09-13 00:00:00 9.0 8 1 9
2019 2019-05-04 00:00:00 5.0 10 2 5
月份列应为09,05
我需要按原样从ord_datetime中提取月份，也许不需要使用extract((
EXTRACT函数被记录为返回双精度浮点。您已经在使用CAST将年份转换为整数。月份也一样。
SELECT
cast(extract(year from ord_datetime) as int ) as year,
cast(extract(month from ord_datetime) as int ) as month
FROM analysis a inner join orders o on o.ord_an = a.an_id
inner join groups g on g.gr_id = a.an_group;
要保留前导零，请使用to_char():
select to_char(ord_datetime, 'YYYY') as year,
to_char(ord_datetime, 'MM') as month
from analysis a
join orders o on o.ord_an = a.an_id
join groups g on g.gr_id = a.an_group