SQL笛卡尔连接问题

Question

我有三个表

• A：A.pID 主键，A.Name nvarchar(250)
• B：B.pID 主键，B.Name nvarchar(250)
• C：C.pID 主键，C.Name nvarchar(250)

有A 和 B 之间是 am to n 关系（表 lA_B 具有主键 lA_B.pID 和 .pInstanceA 表 A 和 的外键>.pInstanceB 表 B 的外键）

A 和 C 之间存在 am to n 关系（表 lA_C 具有主键 lA_C.pID 和 .pInstanceA 表 A 的外键和 .pInstanceB 表 C 的外键）

• A1 与 B1、B2 和 C1 相关
• A2 与 B3 和 C2、C3 相关
• A3 位于与 B4 的关系
• A4 与 C4 相关
• A5 没有关系

这是我的 SQL：

CREATE TABLE [dbo].[A]( [pID] [bigint] NOT NULL, [Name] [nvarchar](250) NULL )
CREATE TABLE [dbo].[B]( [pID] [bigint] NOT NULL, [Name] [nvarchar](250) NULL )
CREATE TABLE [dbo].[C]( [pID] [bigint] NOT NULL, [Name] [nvarchar](250) NULL)
CREATE TABLE [dbo].[lA_B]( [pID] [bigint] NOT NULL, [pInstanceA] [bigint] NULL, [pInstanceB] [bigint] NULL )
CREATE TABLE [dbo].[lA_C]( [pID] [bigint] NOT NULL, [pInstanceA] [bigint] NULL, [pInstanceB] [bigint] NULL )

INSERT INTO [dbo].[A] ([pID] ,[Name]) VALUES (1,'A1')
INSERT INTO [dbo].[A] ([pID] ,[Name]) VALUES (2,'A2')
INSERT INTO [dbo].[A] ([pID] ,[Name]) VALUES (3,'A3')
INSERT INTO [dbo].[A] ([pID] ,[Name]) VALUES (4,'A4')
INSERT INTO [dbo].[A] ([pID] ,[Name]) VALUES (5,'A5')

INSERT INTO [dbo].[B] ([pID] ,[Name]) VALUES (1,'B1')
INSERT INTO [dbo].[B] ([pID] ,[Name]) VALUES (2,'B2')
INSERT INTO [dbo].[B] ([pID] ,[Name]) VALUES (3,'B3')
INSERT INTO [dbo].[B] ([pID] ,[Name]) VALUES (4,'B4')

INSERT INTO [dbo].[C] ([pID] ,[Name]) VALUES (1,'C1')
INSERT INTO [dbo].[C] ([pID] ,[Name]) VALUES (2,'C2')
INSERT INTO [dbo].[C] ([pID] ,[Name]) VALUES (3,'C3')
INSERT INTO [dbo].[C] ([pID] ,[Name]) VALUES (4,'C4')

INSERT INTO [dbo].[lA_B] ([pID],[pInstanceA],[pInstanceB])   VALUES   (1,1,1)
INSERT INTO [dbo].[lA_B] ([pID],[pInstanceA],[pInstanceB])   VALUES   (2,1,2)
INSERT INTO [dbo].[lA_B] ([pID],[pInstanceA],[pInstanceB])   VALUES   (3,2,3)
INSERT INTO [dbo].[lA_B] ([pID],[pInstanceA],[pInstanceB])   VALUES   (4,3,4)

INSERT INTO [dbo].[lA_C] ([pID],[pInstanceA],[pInstanceB])   VALUES   (1,1,1)
INSERT INTO [dbo].[lA_C] ([pID],[pInstanceA],[pInstanceB])   VALUES   (2,2,2)
INSERT INTO [dbo].[lA_C] ([pID],[pInstanceA],[pInstanceB])   VALUES   (3,2,3)
INSERT INTO [dbo].[lA_C] ([pID],[pInstanceA],[pInstanceB])   VALUES   (4,4,4)

此查询：

SELECT
  A.Name AS A, 
  B.Name AS B, 
  C.Name AS C
FROM
  A  
  left JOIN lA_B  ON (A.pID = lA_B.pInstanceA)
  left JOIN B     ON (B.pID = lA_B.pInstanceB)
  left JOIN lA_C  ON (A.pID = lA_C.pInstanceA)
  left JOIN C     ON (C.pID = lA_C.pInstanceB) 

返回

A1      B1      C1
A1      B2      C1
A2      B3      C2
A2      B3      C3
A3      B4      NULL
A4      NULL    C4
A5      NULL    NULL 

现在的问题:-) 如何查询接收

A1      B1      NULL
A1      B2      NULL
A1      NULL    C1
A2      B3      NULL
A2      NULL    C2
A2      NULL    C3
A3      B4      NULL
A4      NULL    C4
A5      NULL    NULL 

问题是，当我与 B 和 C 进行连接时，结果具有 B C 的所有组合。我怎样才能消除这个问题？

Answer 1

您也许可以使用 UNION 来做到这一点：

SELECT A.Name AS A, B.Name AS B, NULL AS C 
FROM A
left JOIN lA_B ON (A.pID=lA_B.pInstanceA) 
left JOIN B ON (lA_B.pInstanceB=B.pID) 
UNION
SELECT A.Name AS A, NULL AS B, C.Name AS C 
FROM A
left JOIN lA_C ON (A.pID=lA_C.pInstanceA) 
left JOIN C ON (lA_C.pInstanceB=C.pID)

第一部分选择 A 和 B 的所有组合，第二部分选择 A 和 C 的所有组合。

如果您希望过滤掉像 (A4,NULL,NULL) 这样的行，因为有已经是一行 (A4,NULL,C4)，请尝试以下查询：

SELECT A.Name AS A, B.Name AS B, NULL AS C 
FROM A
LEFT JOIN lA_B ON (A.pID=lA_B.pInstanceA) 
LEFT JOIN B ON (lA_B.pInstanceB=B.pID) 
WHERE b.name is not null 
    or not exists(select * from lA_C where A.pID=lA_C.pInstanceA) 
UNION
SELECT A.Name AS A, NULL AS B, C.Name AS C 
FROM A
LEFT JOIN lA_C ON (A.pID=lA_C.pInstanceA) 
LEFT JOIN C ON (lA_C.pInstanceB=C.pID)
WHERE c.name is not null 
ORDER BY A,B,C

对于 B 上的联接，这表示包括在 B 中匹配的行，或者在 C 中没有匹配的行。C 上的联接仅包括行与 C 中匹配的行。两者都不匹配的行将从 B 上的联接中包含进来。

请注意，UNION 会过滤掉重复的行，如 DISTINCT。 要包含每一行，您可以使用 UNION ALL。

Answer 2

我认为这样做可以：

select a,
       case when b='zzz' then null else b end as b,
       case when c='zzz' then null else c end as c
from (SELECT  A.Name AS A
             ,b.Name as b
             ,'zzz' as c
      FROM  A    
      JOIN lA_B  ON (A.pID = lA_B.pInstanceA)  
      JOIN B     ON (B.pID = lA_B.pInstanceB)  
      union
      select  a.Name
             ,'zzz'
            ,c.NAme
      from A
      left JOIN lA_C  ON (A.pID = lA_C.pInstanceA)  
      left JOIN C     ON (C.pID = lA_C.pInstanceB)) as a