oracle计数记录每日加入

Question

大师，(Oracle 12c(我很难每天统计多个表的记录。我认为查询的基础应该是一个日历。我创建了一个日历如下：

with calendar as(select rownum -1 as day from dual
connect by rownum < sysdate - TO_DATE('2020-08-01')+1)
select to_date('2020-08-01')+ day as sale_date from calendar

来源表：

CREATE TABLE "CARS1" 
(    "COUNT_CARS" NUMBER(10,0), 
"EQUIPMENT_TYPE" VARCHAR2(100 BYTE), 
"LOCATION" VARCHAR2(500 BYTE), 
"SALE_DATE" DATE) 
SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
CREATE TABLE "CARS2" 
(    "COUNT_CARS" NUMBER(10,0), 
"EQUIPMENT_TYPE" VARCHAR2(100 BYTE), 
"LOCATION" VARCHAR2(500 BYTE), 
"SALE_DATE" DATE) 
SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

DML脚本：

INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION,SALE_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2020-08-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('9', 'Jaguars', 'coventry', TO_DATE('2020-08-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('7', 'Rovers', 'leamington', TO_DATE('2020-08-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('10','Trans Am', 'leamington', TO_DATE('2020-08-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2020-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('4', 'Rovers', 'leamington', TO_DATE('2020-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;

INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION,SALE_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2020-08-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('9', 'Jaguars', 'coventry', TO_DATE('2020-08-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('7', 'Rovers', 'leamington', TO_DATE('2020-08-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('10','Trans Am', 'leamington', TO_DATE('2020-08-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2020-08-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('4', 'Rovers', 'leamington', TO_DATE('2020-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;

我可以简单地从一个表中获得汽车数量，而不使用日历作为结果的基础，但我不知道如何将日历日作为基础，并将多个表连接起来，从该表中获得每日汽车数量(比如添加40个表，以获得每一天的每日汽车数量(。

结果看起来像：

#SALE_DATE #  cars1  cars2
2020-08-01    12      4
2020-08-02    0       17
2020-08-03    0       25
2020-08-04    7       0
2020-08-05    17      0

Answer 1

正如我一开始所想的那样，任务似乎有点难。这就是你如何获得你需要的结果

with calendar as(select level + to_date('2020-08-01', 'yyyy-mm-dd') - 1 day 
from dual
connect by level + to_date('2020-08-01', 'yyyy-mm-dd') < to_date('2020-09-01', 'yyyy-mm-dd')),
c1_sales as (select sale_date, sum(count_cars) count_cars from cars1 group by sale_date),
c2_sales as (select sale_date, sum(count_cars) count_cars from cars2 group by sale_date)
select c.day, nvl(t1.count_cars, 0), nvl(t2.count_cars, 0)
from calendar c,
c1_sales t1,
c2_sales t2
where t1.sale_date(+) = c.day
and t2.sale_date(+) = c.day
order by c.day;

感谢您提出一个棘手的问题-解决很有趣