如何消除连接sql语句中的重复结果

Question

我有四个这样相关的表：

TABLE A: 1 to many with TABLE B
TABLE B: 1 to many with TABLE C
TABLE D: many to 1 with TABLE B

我想创建一个不包含重复项的结果集。

SELECT A.f1
       A.f2
       B.f1
       C.f1
       D.f1
from   A LEFT JOIN (B INNER JOIN D on D.fk_b = B.id) on A.id = B.fk_a
         LEFT JOIN C on C.fk_b = B.id

一些但不是所有记录都是重复的。仅TABLE D 中与TABLE B 具有相同FK 的记录。

如果有 2 个相同的 D.fk_B 字段，那么我只想选择第一个。我的 INNER JOIN 会变成这样：

B INNER JOIN TOP 1 D on..... 

但这不可能？

谢谢你！

Answer 1

假设所讨论的结构接近下图所示的结构，

<块引用>

以下 SELECT 语句将仅显示主键，简要介绍返回的数据。

SELECT
  A.id AS `A_id`,
  B.id AS `B_id`,
  COALESCE(C.id, 0) AS `C_id`,
  COALESCE(
    (SELECT D.id FROM D WHERE D.fk_b = B.id LIMIT 1), 0
  ) AS `D_id`
FROM B
INNER JOIN A ON (B.fk_a = A.id)
LEFT JOIN C ON (B.id = C.fk_b);

现在您要求的选择：

SELECT
  A.f1 AS `A_f1`,
  A.f2 AS `A_f2`,
  B.f1 AS `B_f1`,
  COALESCE(C.f1, '-') AS `C_f1`,
  COALESCE(
    (SELECT D.f1 FROM D WHERE D.fk_b = B.id LIMIT 1), '-'
  ) AS `D_f1`
FROM B
INNER JOIN A ON (B.fk_a = A.id)
LEFT JOIN C ON (B.id = C.fk_b);

上面的代码已经过测试并且在 MySQL 5.6 上运行良好

Answer 2

看来您有笛卡尔连接。

您可以尝试将 DISTINCT 放在列定义前面，或者可以使用子查询将表链接在一起。

Answer 3

尝试

SELECT DISTINCT A.f1
       A.f2
       B.f1
       C.f1
       D.f1
from   A LEFT JOIN (B INNER JOIN D on D.fk_b = B.id) on A.id = B.fk_a
         LEFT JOIN C on C.fk_b = B.id

你可以这样做：

B INNER JOIN TOP 1 D on..... 

像这样

B INNER JOIN (SELECT TOP 1 fields from table) D ON ...

尝试这个：

  SELECT DISTINCT A.f1
           A.f2
           B.f1
           C.f1
           D.f1
    from   A 
    LEFT JOIN (B INNER JOIN (SELECT DISTINCT fk_b FROM D) D on D.fk_b = B.id) 
         on A.id = B.fk_a
    LEFT JOIN C on C.fk_b = B.id