避免SQL笛卡尔积

Question

避免SQL笛卡尔积

8

我对SQL还是很新手，在查询方面遇到了一些困难（使用Access，仅供参考）。我已经通过谷歌和StackOverflow进行了搜索，但没有找到完全符合我的情况的结果。（这也可能是因为我不知道正确的搜索术语。）

我有两个相似数据的简单表格。

table1: state, lname, fname, network
table2: state, lname, fname, network

我希望您能够找到在两个表中匹配的每个人/州组合，以及每个人所在的表中的网络：

state, lname, fname, t1.network, t2.network.

每个人可能在每张表中属于多个网络。我想看到这个人属于哪些网络（来自两张表）。

我开始使用以下 JOIN：

SELECT t1.state, t1.lname, t1.fname, t1.network, t2.network
FROM t1 INNER JOIN t2 
ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state
GROUP BY t1.state, t1.lname, t1.fname, t1.network, t2.network

我很快就发现我得到了一个笛卡尔积。因此，如果“NY，Smith，John”在t1中的两个网络中，在t2中有三个网络，我会得到类似于以下内容：

NY, Smith, John, NetworkA, NetworkB
NY, Smith, John, NetworkA, NetworkA
NY, Smith, John, NetworkB, NetworkA
NY, Smith, John, NetworkB, NetworkB
NY, Smith, John, NetworkA, NetworkC
NY, Smith, John, NetworkB, NetworkC

我希望看到的只是：

NY, Smith, John, NetworkA, NetworkA
NY, Smith, John, NetworkB, NetworkB
NY, Smith, John, NULL, NetworkC

有人能给我一些如何继续进行或指导我的建议吗？

- user2014025

恐怕这种情况基本上总是会给你留下这样的结果。尝试将查询分为两个部分：第一部分->州、姓和名。一旦你获得了这些数据，就询问这些人所在的网络。为此，你应该使用某种ID。（因为来自纽约的John Smith可能不止一个） - Refugnic Eternium

1

这些表实在是太混乱了。请对它们进行规范化处理。http://msdn.microsoft.com/zh-cn/library/aa216117(v=sql.80).aspx - flup

4

为什么需要两张拥有完全相同列和重复数据的表格？ - Aniket Inge

1

你并没有真正执行笛卡尔积；你正在执行一个“内连接”（INNER JOIN）。ON 子句控制了表的组合方式。将来请在你的问题中包含完整的表定义（即CREATE TABLE部分）。那会帮助到其他人更好地回答你的问题。欢迎来到 StackOverflow！ - BellevueBob

@Aniket 可能是语义问题；我非常确定大多数数据库中的产品结果集并没有被“实体化”，尽管在Access中可能会这样。 - BellevueBob

显示剩余2条评论

5个回答

0

SELECT DISTINCT t1.state, t1.lname, t1.fname, t1.network, t2.network
FROM t1 INNER JOIN t2 
ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state
GROUP BY t1.state, t1.lname, t1.fname, t1.network, t2.network

然后你就可以得到你想要的。

几乎每个连接都是从构建笛卡尔积开始的（请参阅《Inside SQL Server: Querying》一书）。当你尝试过滤数据时，通常情况下，使用 DISTINCT 时创建的虚拟表将返回一个不同的集合。实际上，无法停止笛卡尔积的产生。

- Aniket Inge

感谢您的回复。然而在这种情况下，DISTINCT 似乎并没有什么区别。当我使用您的查询时，我并没有看到任何不同之处。 - user2014025

0

在这种情况下，UNION 应该是您的最佳选择：

SELECT * FROM t1
UNION
SELECT * FROM t2

（请注意，如果两个表中的所有列都是相同类型、相同顺序，则此查询应该按原样工作——否则最好指定每个所需列，而不是使用 SELECT *。）

- user359040

连接两个表的简单方法，SQL Server采用相同的执行计划。 - Aniket Inge

0

你需要的是一个UNION。

我会创建一个查询（在SQL视图中）来实现：

SELECT state, lname, fname, network, network
FROM t1
UNION ALL
SELECT state, lname, fname, network, network
FROM t2

然后我会创建其他查询来查询那个查询。

我想自己尝试一下以便给你更多细节，但我没有安装MS Access。

- Paulb

你可以安装MS Access或SQL Server，甚至是MySQL。它说的是通用的“SQL” - 你真的不需要MS Access :-) - Aniket Inge

但是我需要先有Windows.. :) - Paulb

0

一种选择是使用全外连接：

SELECT coalesce(t1.state, t2.state), coalesce(t1.lname, t2.lname), coalesce(t1.fname, t2.fname),
  t1.network, t2.network
FROM t1 FULL OUTER JOIN t2 
ON t1.fname=t2.fname AND t1.lname=t2.lname AND t1.state=t2.state
and t1.network = t2.network

输出：

NY  SMITH   JOHN    A   A
NY  SMITH   JOHN    B   B
NY  SMITH   JOHN    NULL    C

编辑：我没有看到你正在使用Access - 这是标准SQL，但我不知道它是否适用于那里。

- chue x

感谢您的回复。Access不支持FULL OUTER JOIN，但是我可以通过使用单独的OUTER JOIN并使用UNION将它们连接在一起来解决这个问题，就像sgeddes在下面建议的那样。 - user2014025

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- sgeddes · Accepted Answer

所以看起来您想要从每个相同的表中获取所有记录，然后只选择其中不同的记录。这意味着您需要联合3组查询。

可以尝试像这样的语句：

SELECT t1.state, 
   t1.lname, 
   t1.fname, 
   t1.network as t1Network, 
   t2.network as t2Network
FROM table1 t1 
   INNER JOIN table2 t2 
      ON t1.fname=t2.fname 
      AND t1.lname=t2.lname 
      AND t1.state=t2.state
      AND t1.network=t2.network
UNION 
SELECT t1.state, 
   t1.lname, 
   t1.fname, 
   t1.network as t1Network, 
   t2.network as t2Network
FROM table1 t1 
   LEFT JOIN table2 t2 
      ON t1.fname=t2.fname 
      AND t1.lname=t2.lname 
      AND t1.state=t2.state
      AND t1.network=t2.network
WHERE t2.network IS NULL
UNION 
SELECT t2.state, 
   t2.lname, 
   t2.fname, 
   t1.network as t1Network, 
   t2.network as t2Network
FROM table2 t2 
   LEFT JOIN table1 t1
      ON t1.fname=t2.fname 
      AND t1.lname=t2.lname 
      AND t1.state=t2.state
      AND t1.network=t2.network
WHERE t1.network IS NULL

这应该会给你想要的结果。

这里是 SQL Fiddle，可以确认一下。

--编辑

今天有点糊涂了 -- 你实际上不需要第一个查询。从第二个查询中移除 WHERE 条件也可以达到同样的效果。累了 :-)

这是更新后的查询 -- 两个查询都应该正常工作，只是这个更容易阅读：

SELECT t1.state, 
   t1.lname, 
   t1.fname, 
   t1.network as t1Network, 
   t2.network as t2Network
FROM table1 t1 
   LEFT JOIN table2 t2 
      ON t1.fname=t2.fname 
      AND t1.lname=t2.lname 
      AND t1.state=t2.state
      AND t1.network=t2.network
UNION 
SELECT t2.state, 
   t2.lname, 
   t2.fname, 
   t1.network as t1Network, 
   t2.network as t2Network
FROM table2 t2 
   LEFT JOIN table1 t1
      ON t1.fname=t2.fname 
      AND t1.lname=t2.lname 
      AND t1.state=t2.state
      AND t1.network=t2.network
WHERE t1.network IS NULL

还有更新的fiddle。

顺便说一下--这两个应该在MSAccess中都可以工作，因为它支持UNION。

祝好运。