database - 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-June-11 和 13-June-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 |
+-----------+----------+------+