问题描述
场景:我有 2 个表,分别是 Coverage 和 Product
Scenario: I have 2 tables namely Coverage and Product
覆盖表:CId、CName、CType、CMessage、CDate、CoverageProductId
Coverage Table:CId,CName,CType,CMessage,CDate,CoverageProductId
产品表:PId、CName、CType、CMessage、PDate、CoverageProductId
Product Table:PId,CName,CType,CMessage,PDate,CoverageProductId
通过使用 CoverageProductId 我关联了 2 个表.
By using CoverageProductId i am relating 2 tables.
我需要从两个表中获取 3 列 CName、CMessage 和 CoverageProductId
I need to get 3 columns CName,CMessage and CoverageProductId from both tables
条件如下,
- 如果两个表 CoverageProductId 匹配,则从 Product 表中获取 3 列值.
- 如果两个表 CoverageProductId 不匹配,则从 Coverage 表中获取 3 列值.
我的查询在 MS sql server 下面
My query is below in MS sql server
SELECT Distinct C.CoverageProductID
,C.CName
,C.CType
FROM Coverage C
INNER JOIN Product P
ON C.CoverageProductID!=P.CoverageProductID
UNION
SELECT Distinct P.CoverageProductID
,P.CName
,P.CType
FROM Coverage C
INNER JOIN Product P
ON C.CoverageProductID=P.CoverageProductID
但结果与预期不符,返回重复值.
But the result is not as expected and it returns duplicate values.
推荐答案
我认为这可能会满足您的需求:
I think this might do what you want:
select P.CoverageProductID, P.CName, P.CType
from Product P
where exists (select 1
from coverage c
where p.CoverageProductID = c.CoverageProductID)
union all
select C.CoverageProductID, C.CName, C.CType
from Coverage C
where not exists (select 1
from product p
where p.CoverageProductID = c.CoverageProductID);
它从 Product
中获取在 Coverage
中匹配的所有行,然后从 Coverage
中获取在 产品
.
It gets all rows from Product
that match in Coverage
and then all rows from Coverage
that don't have a match in Product
.
这篇关于连接两个表,从表 1 中获取匹配记录和不匹配记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!