Oracle 中的外部联接表 - 按每个日期单独联接

Question

>假设我有一些数据并做一个查询，我得到了这样的东西 -

-----------------------------
trun(date) | location | sum |
-----------------------------
14-June-11 | B        | 5   |
-----------------------------
13-June-11 | B        | 5   |
-----------------------------
14-June-11 | C        | 5   |
-----------------------------
13-June-11 | C        | 5   |
-----------------------------
SELECT TRUNC(DATE_TIME),MIN(LOCATION) AS LOCATION, SUM(CREDIT) AS SUM FROM 
(SELECT * FROM TABLE A
WHERE
A.DATE_TIME >= TO_DATE('13/JUN/2011','dd/mon/yyyy')
AND A.DATE_TIME <= TO_DATE('15/JUN/2011','dd/mon/yyyy'))
GROUP BY TRUNC(DATE_TIME), LOCATION 

还有另一个表 B，其中有一个位置列表

----
A  |
----
B  |
----
C  |
----

我想要这样的东西——

-----------------------------
trun(date) | location | sum |
-----------------------------
14-June-11 | A        | 0   |
-----------------------------
14-June-11 | B        | 5   |
-----------------------------
14-June-11 | C        | 5   |
-----------------------------
13-June-11 | A        | 0   |
-----------------------------
13-June-11 | B        | 5   |
-----------------------------
13-June-11 | C        | 5   |
-----------------------------

我尝试对表 B 使用右连接，但我无法为 14-6-11 和 13-6-11 创建 2 个单独的记录。任何建议或帮助将不胜感激。

Answer 1

无需单独读取tablea即可获取不同的date_time值。 相反，这是经常被忽视的分区外联接的工作。

这是您使用该功能的答案。 (威廉·罗伯逊(William Robertson(回答中的表格和插页将用于设置这一点(。

select a.date_time
, b.location
, coalesce(a.asum,0) as asum
from  ( SELECT trunc(a.date_time) date_time, 
a.location, 
sum(a.credit) as asum 
FROM tablea a 
WHERE  a.date_time between date '2011-06-13' and date '2011-06-15' or a.date_time is null
GROUP BY trunc(a.date_time), a.location ) a 
-- This is the key part here...
PARTITION BY (date_time)
right join tableb b on a.location = b.location
order by 1 desc, 2;

PARTITION BY关键字的作用是使外连接对date_time的每个不同值单独操作，根据需要为每个值创建空的外连接行。

+-----------+----------+------+
| DATE_TIME | LOCATION | ASUM |
+-----------+----------+------+
| 14-JUN-11 | A        |    0 |
| 14-JUN-11 | B        |    5 |
| 14-JUN-11 | C        |    5 |
| 13-JUN-11 | A        |    0 |
| 13-JUN-11 | B        |    5 |
| 13-JUN-11 | C        |    5 |
+-----------+----------+------+

Answer 2

这个怎么样：

with dates as
( select distinct trunc(date_time) as date_time from tablea )
select trunc(d.date_time)
, b.location
, coalesce(sum(credit),0) as sum
from   dates d
cross join tableb b
left join tablea a
on  a.location = b.location
and trunc(a.date_time) = d.date_time
where  a.date_time between date '2011-06-13' and date '2011-06-15' or a.date_time is null
group by d.date_time, b.location
order by 1 desc, 2;

测试数据：

create table tablea
( date_time date
, location varchar2(1)
, credit number(1,0) );
create table tableb(location varchar2(1));
insert into tablea values (date '2011-06-14' + dbms_random.value, 'B', 2);
insert into tablea values (date '2011-06-14' + dbms_random.value, 'B', 3);
insert into tablea values (date '2011-06-14' + dbms_random.value, 'C', 5);
insert into tablea values (date '2011-06-13' + dbms_random.value, 'B', 1);
insert into tablea values (date '2011-06-13' + dbms_random.value, 'B', 2);
insert into tablea values (date '2011-06-13' + dbms_random.value, 'B', 2);
insert into tablea values (date '2011-06-13' + dbms_random.value, 'C', 1);
insert into tablea values (date '2011-06-13' + dbms_random.value, 'C', 2);
insert into tablea values (date '2011-06-13' + dbms_random.value, 'C', 2);
insert into tableb values('A');
insert into tableb values('B');
insert into tableb values('C');