SQL sql避免笛卡尔积
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14540736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql avoid cartesian product
提问by user2014025
I'm pretty new to SQL and am struggling with a query (using Access, FWIW). I have Googled and searched StackOverflow, but I haven't seen this exact scenario. (That could also be because I don't know the correct search terms.)
我对 SQL 很陌生，并且正在为查询而苦苦挣扎（使用 Access、FWIW）。我在谷歌上搜索并搜索了 StackOverflow，但我还没有看到这个确切的场景。（这也可能是因为我不知道正确的搜索词。）
I have two pretty simple tables that contain similar data.
我有两个包含相似数据的非常简单的表。
table1: state, lname, fname, network
table2: state, lname, fname, network
What I want is to find each person/state combo that match in the two tables plus the networks from each table that the person is in:
我想要的是找到两个表中匹配的每个人/状态组合以及该人所在的每个表中的网络：
state, lname, fname, t1.network, t2.network.
The person may be in more than one network in each table. I want to see each network (from both tables) that the person belongs to.
该人可能在每个表中的多个网络中。我想查看此人所属的每个网络（来自两个表）。
I started by using a JOIN as below:
我开始使用 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
I quickly figured out that I get a Cartesian product. So if "NY, Smith, John" was in two networks in t1 and three networks in t2 I would get something like this:
我很快发现我得到了笛卡尔积。因此，如果“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
What I really want to see is just:
我真正想看到的只是：
NY, Smith, John, NetworkA, NetworkA
NY, Smith, John, NetworkB, NetworkB
NY, Smith, John, NULL, NetworkC
Can anyone give me some advice on how to proceed or point me in the right direction?
任何人都可以就如何继续或为我指明正确的方向给我一些建议吗？
采纳答案by sgeddes
So it looks like you want all records from each of tables that are identical, and then only those from each that are distinct. That means you need to UNION 3 sets of queries.
因此，您似乎希望每个表中的所有记录都相同，然后只希望每个表中的记录不同。这意味着您需要 UNION 3 组查询。
Try something like this:
尝试这样的事情：
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
This should give you your desired results.
这应该会给你你想要的结果。
And here is the SQL Fiddleto confirm.
这是要确认的SQL Fiddle。
--EDIT
- 编辑
Not thinking today -- you don't really need that first query. You can remove the WHERE condition from the 2nd query and it works the same way. Tired :-)
今天不考虑——你真的不需要第一个查询。您可以从第二个查询中删除 WHERE 条件，它的工作方式相同。疲劳的 ：-）
Here is the updated query -- both should work just fine though, this is just easier to read:
这是更新后的查询——不过两者都应该可以正常工作，这更容易阅读：
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
And the updated fiddle.
和更新的小提琴。
BTW -- these should both work in MSAccess as it supports UNION.
顺便说一句 - 这些都应该在 MSAccess 中工作，因为它支持UNION。
Good luck.
祝你好运。
回答by Aniket Inge
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
and you get what you want.
你会得到你想要的。
Almost every join starts off building a cartesian product anyway(see Inside SQL Server: Querying book). When you try to filter out the data, usually, the virtual tables created will return a distinct set when you use a distinct. In reality, there is no stoping the cartesian product.
无论如何，几乎每个连接都是从构建笛卡尔积开始的（请参阅 SQL Server 内部：查询书）。当您尝试过滤数据时，通常创建的虚拟表会在您使用 distinct 时返回一个 distinct 集。实际上，没有停止笛卡尔积。
回答by Aniket Inge
In this case, a UNION should be your best bet:
在这种情况下，UNION 应该是你最好的选择：
SELECT * FROM t1
UNION
SELECT * FROM t2
(Note that this query should only work as is if all the columns in both tables are of the same type, in the same order - otherwise it's better to specify each required column, rather than using SELECT *).
（请注意，此查询应该仅在两个表中的所有列都具有相同类型和相同顺序时才能正常工作 - 否则最好指定每个必需的列，而不是使用SELECT *）。
回答by Paulb
What you want is a UNION.
你想要的是一个 UNION。
I'd create a query (in SQL view) to the effect of:
我会创建一个查询（在 SQL 视图中）以达到以下效果：
SELECT state, lname, fname, network, network
FROM t1
UNION ALL
SELECT state, lname, fname, network, network
FROM t2
Then I would create other queries that query that one.
然后我会创建其他查询来查询那个查询。
I'd try it myself to give you more details, but I don't have MS Access installed.
我会亲自尝试为您提供更多详细信息，但我没有安装 MS Access。
回答by chue x
One option is to use a full outer join:
一种选择是使用完整的外连接：
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
Output:
输出：
NY SMITH JOHN A A
NY SMITH JOHN B B
NY SMITH JOHN NULL C
Edit: I didn't see you are using Access - this is standard SQL, but I don't know if it will work there.
编辑：我没有看到你在使用 Access——这是标准的 SQL，但我不知道它是否可以在那里工作。
