我有以下代码
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 重复字段不应出现,而在我的结果中,报纸名称和杂志名称的值都放在报纸名称标题下。
我怎样才能做到这一点?
由于您使用“*”查询表,因此您将始终获得两个表中的所有列。为了省略此列,您必须手动命名您想要查询的所有列。为了满足您的其他需求,您只需向联合查询中的每个子句插入一个虚拟列。下面是一个示例,应该可以满足您的需求 -
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;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)