我需要从两个包含类似结构数据的表中选择匹配的对。这里的“匹配对”是指在'match‘列中相互引用的两行。
一个单表匹配对示例:
TABLE
----
id | matchid
1 | 2
2 | 1ID 1和2是匹配的对,因为它们都有一个匹配项。
现在真正的问题是:选择出现在两个表中的匹配对的最佳(最快)方法是什么:
Table ONE (id, matchid)
Table TWO (id, matchid)示例数据:
ONE TWO
---- ----
id | matchid id | matchid
1 | 2 2 | 3
2 | 3 3 | 2
3 | 2
4 | 5
5 | 4所需的结果是is 2和3的单行。
RESULT
----
id | id
2 | 3这是因为2&3是表一和表二中的匹配对。4&5是表1中的匹配对,而不是2,所以我们不选择它们。1和2根本不是一对匹配的,因为2没有匹配的条目1。
我可以从一个表中得到匹配的对,如下所示:
SELECT a.id, b.id
FROM ONE a JOIN ONE b
ON a.id = b.matchid AND a.matchid = b.id
WHERE a.id < b.id如何构建只选择出现在两个表中的匹配对的查询?
我是否应该:
(由于这是一个效率问题,值得注意的是,匹配将非常稀疏,可能是1/1000或更少,而且每个表都有100,000+行。)
发布于 2013-04-17 05:13:15
我想我明白你的意思了。您希望筛选在两个表中都存在的记录,。
SELECT LEAST(a.ID, a.MatchID) ID, GREATEST(a.ID, a.MatchID) MatchID
FROM One a
INNER JOIN Two b
ON a.ID = b.ID AND
a.matchID = b.matchID
GROUP BY LEAST(a.ID, a.MatchID), GREATEST(a.ID, a.MatchID)
HAVING COUNT(*) > 1发布于 2013-04-17 16:28:17
朴素版本,它检查需要存在的所有四行:
-- EXPLAIN ANALYZE
WITH both_one AS (
SELECT o.id, o.matchid
FROM one o
WHERE o.id < o.matchid
AND EXISTS ( SELECT * FROM one x WHERE x.id = o.matchid AND x.matchid = o.id)
)
, both_two AS (
SELECT t.id, t.matchid
FROM two t
WHERE t.id < t.matchid
AND EXISTS ( SELECT * FROM two x WHERE x.id = t.matchid AND x.matchid = t.id)
)
SELECT *
FROM both_one oo
WHERE EXISTS (
SELECT *
FROM both_two tt
WHERE tt.id = oo.id AND tt.matchid = oo.matchid
);这个比较简单:
-- EXPLAIN ANALYZE
WITH pair AS (
SELECT o.id, o.matchid
FROM one o
WHERE EXISTS ( SELECT * FROM two x WHERE x.id = o.id AND x.matchid = o.matchid)
)
SELECT *
FROM pair pp
WHERE EXISTS (
SELECT *
FROM pair xx
WHERE xx.id = pp.matchid AND xx.matchid = pp.id
)
AND pp.id < pp.matchid
;https://stackoverflow.com/questions/16051961
复制相似问题