Oracle 如何创建按日期计算的月度表

我们的环境是
数据库10g 10.2.0.1.0，表格6i
操作系统: 视窗服务器2008R2


我们有表格记录我们的经销商的时间表，如下所示

create table distributor ( dcode int, distributor_name varchar2 (200));

create table loadplan (mnth_no number, year_no number, dcode int, day_no int);
复制

其中包含数据:

insert into distributor values ( 1, 'DAVID');
insert into distributor values ( 2, 'TAHIR');
insert into distributor values ( 3, 'ABBOTT');


insert into loadplan values ( 10, 2017, 1, 1);
insert into loadplan values ( 10, 2017, 1, 11);
insert into loadplan values ( 10, 2017, 1, 22);

insert into loadplan values ( 10, 2017, 2, 2);
insert into loadplan values ( 10, 2017, 2, 12);
insert into loadplan values ( 10, 2017, 2, 24);

insert into loadplan values ( 10, 2017, 3, 3);
insert into loadplan values ( 10, 2017, 3, 13);
insert into loadplan values ( 10, 2017, 3, 26);

insert into loadplan values ( 11, 2017, 1, 1);
insert into loadplan values ( 11, 2017, 1, 11);
insert into loadplan values ( 11, 2017, 1, 22);

insert into loadplan values ( 11, 2017, 2, 2);
insert into loadplan values ( 11, 2017, 2, 12);
insert into loadplan values ( 11, 2017, 2, 24);

insert into loadplan values ( 11, 2017, 3, 3);
insert into loadplan values ( 11, 2017, 3, 13);
insert into loadplan values ( 11, 2017, 3, 26);
复制

我们需要开发一个查询，以便我们获得所有分销商的日程表

SNo Distributor_Name    Month_No    Year_No   1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31
复制

我不确定您到底想在日栏中显示什么？

在任何情况下，你想要枢轴从11g起就像:

select * from loadplan
pivot (
  count(*) for day_no in (
    1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 
    17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)
);

MNTH_NO   YEAR_NO   DCODE   1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   
       10      2017       1   1   0   0   0   0   0   0   0   0    0    1    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0    0 
       10      2017       2   0   1   0   0   0   0   0   0   0    0    0    1    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0 
       10      2017       3   0   0   1   0   0   0   0   0   0    0    0    0    1    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0 
       11      2017       1   1   0   0   0   0   0   0   0   0    0    1    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0    0 
       11      2017       2   0   1   0   0   0   0   0   0   0    0    0    1    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0 
       11      2017       3   0   0   1   0   0   0   0   0   0    0    0    0    1    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0
复制

将count(*) 替换为您想要在日列中显示的任何内容。注意这必须是一个聚合 (最小/平均/等) 函数!

可悲的是你的体重是10克。

所以你必须用老式的手工方式来做。按月/年/等分组。然后重复每天的汇总。在其中，检查当前日期值是否等于该列表示的日期。如果是，则返回一个值。如果不是null。

例如:

select mnth_no, year_no, dcode, 
       count(case when day_no = 1 then 1 end) d1, 
       count(case when day_no = 2 then 1 end) d2, 
       count(case when day_no = 3 then 1 end) d3, 
       count(case when day_no = 4 then 1 end) d4
       -- etc.
from   loadplan
group  by mnth_no, year_no, dcode;

MNTH_NO   YEAR_NO   DCODE   D1   D2   D3   D4   
       11      2017       1    1    0    0    0 
       10      2017       1    1    0    0    0 
       11      2017       2    0    1    0    0 
       11      2017       3    0    0    1    0 
       10      2017       3    0    0    1    0 
       10      2017       2    0    1    0    0 
复制

如果您想了解有关旋转的更多信息，请阅读:

https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot#unpivot