我有下面的代码
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 title下。
我该怎么做?
2条答案
按热度按时间hgqdbh6s1#
由于您使用'*'查询表,因此您将始终获得两个表中的所有列。为了省略此列,您必须手动命名要查询的所有列。为了满足您的其他需求,您只需要在联合查询中的每个子句中插入一个伪列。下面是一个例子,应该工作,以允许你想要的-
nkoocmlb2#
要获得所需的投影,请构建正确形状的子查询,并将其联合以获得结果集。UNION ALL比UNION好,因为它避免了排序:你知道你会得到一个不同的集合,因为你在两个不同的表上连接。
下面是我的玩具数据集的输出(缺少
publishedby
列: