如何在SQL中从JOIN中删除重复的列

Question

我有以下代码

SELECT *
FROM
    customer
INNER JOIN (
    SELECT
        customerid, newspapername, enddate, n.publishedby
    FROM
        newspapersubscription ns,
        newspaper n
    WHERE
        publishedby IN (
            SELECT publishedby
            FROM newspaper
            WHERE ns.newspapername = n.NewspaperName
        )
    UNION
    SELECT
        customerid, Magazinename, enddate, m.publishedby
    FROM
        magazinesubscription ms,
        magazine m
    WHERE
        publishedby IN (
            SELECT publishedby
            FROM magazine
            WHERE ms.Magazinename = m.MagazineName
        )
) ON
    customer.customerid = customerid
ORDER BY
    customer.customerid;

客户表具有以下内容：

 customerid | customername | customersaddress

此查询返回以下结果：

customerid | customername | customersaddress | customerid | newspapername | enddate| publishedby

我实际想要的是

customerid | customername | customersaddress | newspapername | magazinename | enddate| publishedby

这里，如果magazinename存在，则newspapername字段应为空，反之亦然。此外，在联合操作中的customerid重复字段不应存在，而在我的结果中，newspapername和magazinename的值都放在newspapername标题下。

我该怎么做呢？

- evan

---

您想让客户ID在结果中出现两次吗？还是打错字了？ - Webeng

不良习惯：使用旧式JOIN语句。旧式的“逗号分隔表列表”已经在20多年前被“正确”的ANSI JOIN语法所取代，并且不建议使用，这是在ANSI-92 SQL标准中规定的。 - marc_s

Answer 1

由于您使用了“*”查询表格，您将始终获取两个表格中的所有列。为了省略此列，您需要手动命名要查询的所有列。为了满足您的其他需求，您只需要在联合查询中的每个子句中插入一个虚拟列即可。以下是一个示例，应该可以实现您想要的功能 -

SELECT customer.customerid, customer.customername, customer.customeraddress, newspapername, magazinename, enddate, publishedby 
FROM customer
INNER JOIN
(select  customerid, newspapername, null Magazinename, enddate, n.publishedby 
 from newspapersubscription ns, newspaper n 
 where publishedby in(select publishedby 
                    from newspaper 
                    where ns.newspapername = n.NewspaperName)
UNION
select  customerid, null newspapername, Magazinename, enddate, m.publishedby 
from magazinesubscription ms, magazine m 
 where publishedby in(select publishedby 
                    from magazine 
                     where ms.Magazinename = m.MagazineName))
on customer.customerid = customerid
ORDER BY customer.customerid;

- Yaron Idan

---

很棒的答案和解释！我给你点赞。 - Derek O

Answer 2

为了获得您想要的投影，需要构建正确形状的子查询并将它们联合起来以获得结果集。使用UNION ALL比使用UNION更好，因为它避免了排序：您知道您将获得一个不同的数据集，因为您正在连接两个不同的表。

select * from (
    select customer.* 
           , n.newspapername
           , null as magazinename
           , ns.enddate
          , n.publishedby 
    from customer
        join newspapersubscription ns 
            on ns.customerid = customer.customerid
        join newspaper n
            on  n.newspapername = ns.newspapername 
    union all
    select customer.* 
           , null as newspapername
           , m.magazinename
           , ms.enddate
           , m.publishedby 
    from customer
        join magazinesubscription  ms 
            on ms.customerid = customer.customerid
        join magazine m
            on  m.magazinename = ms.magazinename 
            )
order by customerid, newspapername nulls last, magazinename ;

这是我的玩具数据集的输出结果（缺少“发布者”列）:

CUSTOMERID CUSTOMERNAME         NEWSPAPERNAME          MAGAZINENAME           ENDDATE
---------- -------------------- ---------------------- ---------------------- ---------
        10 DAISY-HEAD MAISIE    THE DAILY BUGLE                               30-SEP-17
        30 FOX-IN-SOCKS         THE DAILY BUGLE                               30-SEP-17
        30 FOX-IN-SOCKS         THE WHOVILLE TIMES                            30-SEP-16
        30 FOX-IN-SOCKS                                GREEN NEWS             31-DEC-17
        30 FOX-IN-SOCKS                                TWEETLE BEETLE MONTHLY 31-DEC-16
        40 THE LORAX                                   GREEN NEWS             31-DEC-18

6 rows selected.

SQL>

- APC

---

为什么在联合操作周围使用了子查询 - 这似乎没有添加任何内容？ - Alex Poole

@AlexPoole - 左连接的问题在于当客户订阅多份报纸和杂志时，需要避免笛卡尔积。外部查询是避免在ORDER BY子句上出现“ORA-00904”的一种方式。另一种方式是使用“order by 1, 3, 4”。 - APC

好的，你会得到ORA-00904错误是因为使用了customer.*；我不知道那个。这就是不使用*的另一个原因。 - Alex Poole