有办法按月显示记录吗

Question

所以。。我正试着一个月一个月地做记录展示。

例如，

select X, Y, Z, (X + Y + Z) as total
from (
select
(select count(x) from table1 a, table2 b where date between '2020-01-01' and '2020-05-01') as X,
(select count(y) from table3 a, table4 b where date between '2020-01-01' and '2020-05-01') as Y,
(select count(z) from table5 a, table6 b where date between '2020-01-01' and '2020-05-01') as Z
from dual
);

这将显示这些日期范围之间的x计数，但如果我像两列一样显示它，比如第一列显示月份，第二列显示x，并按月显示。所以结果如下。这在sql中可行吗？

Month    ||     X      ||     Y     ||     Z     ||   Total
January  ||    125     ||    133    ||    155    ||    413
February ||    150     ||    123    ||    129    ||    402
March    ||    170     ||    177    ||    155    ||    502
....
....

Answer 1

如果你想按月份按时间顺序排序，并且假设update是一个实际的日期数据类型，你需要将其以数字格式包含在你的订单中

select to_char(pdate,'Month') month, 
count(*)
from TableA
where pdate between to_date('2019-01-01','YYYY-MM-DD') 
and to_date('2020-08-31','YYYY-MM-DD')
group by to_char(pdate,'Month')
order by to_char(pdate,'MM');

Answer 2

您只需要按月份分组

Select count(X), TO_CHAR(PDATE , 'MONTH') AS Month_name
From TableA
Where PDATE between '2020-01-01' to '2020-01-31'
group by TO_CHAR(PDATE , 'MONTH')

Answer 3

Select
To_char(trunc(pdate, 'mm' ), 'month' ), count(X)
From TableA
Group by trunc(pdate, 'mm' ) 
Order by trunc(pdate, 'mm' )

Answer 4

将日期转换为所需的显示格式"月"，但按日期排序

我想你不在乎你的记录与哪一年相关，所以我将日期转换为与记录的月份相关的字符串。

接下来，我根据记录的月份进行聚合，并通过将记录的月份转换回日期来执行排序，如下所示：

--sample data---------------       
WITH smple ( record_month ) AS (
SELECT
to_char(last_ddl_time, 'Month') record_month
FROM
all_objects
)
--end sample data-----------  
SELECT
record_month,
COUNT(1)
FROM
smple
GROUP BY
record_month
ORDER BY
to_date(record_month || '-01-2020', 'MONTH-DD-YYYY') ASC;