sql - 如何从 SQL 中的连接中删除重复的列

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

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

我怎样才能做到这一点？

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;