在SQL中使用DISTINCT内连接

Question

我有三个表A、B、C，其中A对应B是多对一的关系，而B对应C也是多对一的关系。我想要列出A中所有C的列表。

我的表格大致如下：A[id, valueA, lookupB]、B[id, valueB, lookupC]、C[id, valueC]。我写了一个带有两个嵌套SELECT的查询，但我想知道是否可能使用DISTINCT来进行INNER JOIN。

SELECT valueC
FROM C
INNER JOIN
(
    SELECT DISTINCT lookupC
    FROM B INNER JOIN
    (
        SELECT DISTINCT lookupB
        FROM A
    ) 
    A2 ON B.id = A2.lookupB
) 
B2 ON C.id = B2.lookupC

编辑： 这些表相当大，A有500k行，B有10k行，C有100行，如果进行基本的内连接并在最后使用DISTINCT，会出现很多不必要的信息，如下：

SELECT DISTINCT valueC
FROM 
C INNER JOIN B on C.id = B.lookupB
INNER JOIN A on B.id = A.lookupB

这非常非常慢（比我上面执行的嵌套SELECT慢很多个数量级）。

- mats

Answer 1

我对MS SQL 2005进行了测试，使用了以下表格：A有40万行，B有26千行，C有450行。

估计的查询计划显示基本内部连接比嵌套子查询慢三倍，但实际运行查询时，基本内部连接比嵌套查询快两倍。基本内部连接在非常简单的服务器硬件上只需297毫秒。

您使用的是什么数据库？您看到的时间是多少？如果您看到性能不佳，那么可能是索引问题。

- Darrel Miller

---

这是一个索引问题。我正在使用 MSSQL 2005，并最终找到了 Database Engine Tuning Advisor。我在 A.lookupB 上添加了一个索引，它显著加快了速度。感谢您的帮助！ - Mats Fredriksson

Answer 2

我认为您的1:m关系应该已经隐式地创建了DISTINCT JOIN。

但是，如果您的目标只是每个A中的C，那么在最外层查询上使用DISTINCT可能更容易。

SELECT DISTINCT a.valueA, c.valueC
FROM C
    INNER JOIN B ON B.lookupC = C.id
    INNER JOIN A ON A.lookupB = B.id
ORDER BY a.valueA, c.valueC

- Jonathan Lonowski

---

事实证明，问题的关键在于我没有设置索引。现在已经足够了。 - Mats Fredriksson

Answer 3

SELECT DISTINCT C.valueC 
FROM C 
  LEFT JOIN B ON C.id = B.lookupC
  LEFT JOIN A ON B.id = A.lookupB
WHERE C.id IS NOT NULL

我不明白为什么你想要限制A和B的结果集，因为你想要的是所有被A引用的C的列表。我对C.valueC进行了去重，因为我猜想你需要一个唯一的C列表。

---

编辑：我同意你的观点。即使你的解决方案看起来有点嵌套，但似乎是利用数据知识并减少结果集的最佳且最快的方法。

没有可以使用的distinct join构造，所以请继续使用你已经拥有的方法 :)

- David Humpohl

---

结果是一样的，但似乎要慢一些。我不确定具体原因，但我猜测可能是因为它创建了两个巨大的连接表来处理C.valueC，而C的值非常大。既然我知道B.lookupC本身应该是DISTINCT的，我想利用这个知识来加快速度。 - Mats Fredriksson

Answer 4

这是您的意思吗？

SELECT DISTINCT C.valueC
FROM 
C
INNER JOIN B ON C.id = B.lookupC
INNER JOIN A ON B.id = A.lookupB

- kristian

---

不幸的是，这似乎非常慢，因为它需要先创建巨大的联接表。当我运行我的代码时，速度更快，我猜想这是因为它修剪了联接表。 - Mats Fredriksson

我已经反转了连接顺序，这可能会根据您使用的SQL而改善查询的性能。 - kristian