获取匹配另一个表的记录的查询？

Question

使用 SQL Server 2008，我有 abc 和 xyz 表。

abc

abc_id  | xyz_id1 | xyz_id2
---------------------------
 1     |  foo123  |  foo125
 2     |  foo127  |  foo129

XYZ表

xyzid      | abc_id | location   | sequence_id
------------------------------------------
foo123     |  1     | park       | 1
foo124     |  1       mall       | 2
foo125     |  1     | park       | 3
foo127     |  2     | restaurant | 1
foo128     |  2     | lake       | 2
... -- several xyz records for order 2
foo130     |  2     | mall       | 5

我需要获取所有abc_id，其中xyz_id1的位置等于xyz_id2的位置。(park == park)。xyz_id1和xyz_id2将始终是xyz表中的最小和最大序列号，因此查询中可能会使用max(sequence_id)。

在这个例子中，它将返回'1'。(这将返回许多记录，而不仅仅是标量值)。

我的代码有一些业务逻辑，我不想分享。有人可以提供帮助吗？我相当确定我需要一些子查询或临时表？

- James

Answer 1

SELECT abc_id
FROM abc
INNER JOIN xyz1 ON abc.xyz_id1=xyz1.xyzid
INNER JOIN xyz2 ON abc.xyz_id2=xyz2.xyzid
WHERE xyz1.location=xyz2.location

这样可以避免分组操作。

- Jaroslav Jandek

---

避免两次分组和连接的好处是什么？（不是说它不好，没有查看计划，我不确定哪个执行效果更好） - Martin Smith

@Martin：在正确建立索引的表上执行这样的查询，使用GROUP BY和HAVING会比另一种连接方式慢10-30倍。 - Jaroslav Jandek

+1 我之前对此持怀疑态度，因为我认为 xyz.xyzid IN (abc.xyz_id1, abc.xyz_id2) 会被转换为2个索引查找。但我刚刚进行了测试，结果并非如此。这种方法的执行计划确实好得多。 - Martin Smith

@Martin：基本上是两个哈希连接（非常快）与嵌套循环（相当慢）和一个聚合函数之间的比较。在理想情况下，“GROUPING”版本可以具有类似的性能，但这几乎从不发生。根据pgAnalyser，GROUPING查询可能会慢40倍（我那里有点偏差）。 - Jaroslav Jandek

不确定为什么您正在使用pgAnalyser。OP正在使用SQL Server。可用的转换规则可能会非常不同。 - Martin Smith

@Martin：在我看来，pgAnalyser看起来更好，并且有一些我喜欢的功能。对于这种类型的查询，所有引擎的重要部分看起来都差不多。 - Jaroslav Jandek

Answer 2

将表格加入两次，每次一条记录对应一个 xyz_id：

select  *
from    abc
join    xyz as xyz1
on      xyz1.xyzid = abc.xyz_id1
join    xyz as xyz2
on      xyz2.xyzid = abc.xyz_id2
where   xyz1.location = xyz2.location
        and xyz1.location = 'park'

- Andomar

Answer 3

SELECT abc_id FROM abc a1 INNER JOIN xyz x1 ON a1.xyz_id1 = x1.xyzid INNER JOIN xyz x2 ON a1.xyz_id1 = x2.xyzid WHERE x1.location = x2.location

- Silx