我正在使用Oracle 11gR2,并且试图编写一个查询,该查询返回两个表CUSTOMERSLOCATIONS的地址数据。给定的客户可能(也可能没有)有不同的位置,每个位置都有自己的地址。

我想返回每个客户及其所有位置的地址。例如,如果表包含如下数据:

CUSTOMERS
CUSTOMER_ID    ADDRESS
    1         "New York"
    2         "California"

LOCATIONS CUSTOMER_ID LOCATION_ID ADDRESS 1 1 "New Jersey"

Then I want the results to look like:

CUSTOMER_ID    LOCATION_ID    ADDRESS
   1                         "New York"
   1                1       "New Jersey"
   2                        "California"

My first thought was something like this:

SELECT
 CUSTOMERS.CUSTOMER_ID,
 LOCATIONS.LOCATION_ID,
 NVL(LOCATIONS.ADDRESS,CUSTOMERS.ADDRESS) ADDRESS
FROM
CUSTOMERS
 LEFT JOIN
LOCATIONS ON (CUSTOMERS.CUSTOMER_ID=LOCATIONS.CUSTOMER_ID)

这样做的问题是,当客户确实有位置时,它不会返回位置数据为空值的行,因此我在CUSTOMERS表中没有得到地址所在的行。它给了我这样的东西:
CUSTOMER_ID    LOCATION_ID    ADDRESS
   1                1       "New Jersey"
   2                        "California"

It's missing the New York address for customer 1. I tried this...

SELECT
 CUSTOMERS.CUSTOMER_ID,
 LOCATIONS.LOCATION_ID,
 NVL(LOCATIONS.ADDRESS,CUSTOMERS.ADDRESS) ADDRESS
FROM
CUSTOMERS
 LEFT JOIN
LOCATIONS ON (CUSTOMERS.CUSTOMER_ID=LOCATIONS.CUSTOMER_ID OR LOCATIONS.CUSTOMER_ID IS NULL)

但这给了我与第一个查询相同的结果。即使连接条件匹配,有没有办法为第二张表返回空记录?

最佳答案

您完全不需要在这里加入:

SELECT  customer_id, NULL AS location_id, address
FROM    customers
UNION ALL
SELECT  customer_id, location_id, address
FROM    locations

09-30 14:56
查看更多