mysql multiple left join返回重复的行

我有一个mysql查询，它使用 LEFT JOIN 多次以连接 author 是一样的。然而，当我 echo 结果返回了大量重复的行（实际上 32,920 )当我只有4行记录在 grouppost 另外三张table大约有100张。

表状态

+----+------+--------------+--------+------+-------+---------------------+
| id | osid | account_name | author | type | data  |      postdate       |
+----+------+--------------+--------+------+-------+---------------------+
|  1 |    1 | John         | John   | a    | lkjg. | 2018-01-01 00:00:00 |
+----+------+--------------+--------+------+-------+---------------------+

表1物品状态

+----+------+--------------+--------+------+-------+------+---------------------+
| id | osid | account_name | author | type | data  | artid |      postdate       |
+----+------+--------------+--------+------+-------+------+---------------------+
|  2 |    1 | John         | John   | a    | bcda. |    1 | 2018-01-01 00:00:00 |
+----+------+--------------+--------+------+-------+------+---------------------+

表grouppost

+----+-----+--------+--------+------+-------+----------------------+
| id | pid | gname  | author | type | data  |         pdate        |
+----+-----+--------+--------+------+-------+----------------------+
|  3 |   1 | Group1 | John   |    1 | ABCD. |  2018-01-01 00:00:00 |
+----+-----+--------+--------+------+-------+----------------------+

表格照片\u状态

+----+------+--------------+--------+------+-------+------+---------------------+
| id | osid | account_name | author | type | data  | photo |      postdate       |
+----+------+--------------+--------+------+-------+------+---------------------+
|  4 |    1 | John         | John   | a    | abcd. | a.jpg | 2018-01-01 00:00:00 |
+----+------+--------------+--------+------+-------+------+---------------------+

预期的结果是将四行连接在一起，并从中获取具有指定名称的id：

+---------+--------+-------+-------+
| stat_id | art_id | gr_id | ph_id |
+---------+--------+-------+-------+
|       1 |      2 |     3 |   4   |
+---------+--------+-------+-------+

mysql查询：

$sql = "
SELECT a.id AS art_id
     , g.id AS gr_id
     , p.id AS ph_id
     , s.id AS stat_id 
  FROM article_status AS a 
  LEFT 
  JOIN grouppost AS g 
    ON a.author = g.author 
  LEFT 
  JOIN photos_status AS p 
    ON a.author = p.author 
  LEFT 
  JOIN status AS s 
    ON a.author = s.author 
   AND a.author = 'John' 
   AND g.author = 'John' 
   AND p.author = 'John' 
   AND s.author = 'John'
";

$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row["gr_id"]."<br>"; // returns back duplicate rows
}
$stmt->close();
mysqli_close($conn);

寻找可能的错误：

变量转储（$result）： object(mysqli_result)#3 (5) { ["current_field"]=> int(0) ["field_count"]=> int(4) ["lengths"]=> NULL ["num_rows"]=> int(32920) ["type"]=> int(0) } 就像在 var_dump 上面， ["field_count"]=> int(4) 这是正确的，因为查询中有4个字段受影响。然而 ["num_rows"]=> int(32920) 返回32920，这是完全错误的。
变量转储（$stmt）： object(mysqli_stmt)#2 (10) { ["affected_rows"]=> int(0) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(0) ["field_count"]=> int(4) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(1) } 同样，我想从中选择所有受影响的行 article_status , grouppost , photos_status 以及 status table在哪里 author 是一样的，就像 John 在这个例子中。

首先，您的查询应该简化为以下内容。。

SELECT 
      a.id art_id, 
      g.id gr_id, 
      p.id ph_id, 
      s.id stat_id
   FROM 
      article_status a 
         LEFT JOIN grouppost g 
            ON a.author = g.author 
         LEFT JOIN photos_status p 
            ON a.author = p.author 
         LEFT JOIN status s 
            ON a.author = s.author 
   where
      a.author = 'John'

对于每个其他联接，您都显式地重新添加了and author='john'。它们应该保留各自的“on”子句，而不是放在末尾。此外，通过传递关联，例如：

A = B   and B = C, therefore A = C.

如果您已经在每个表之间加入了author，那么为“a.author='john'”使用一个where子句也会将其余的都捕获为'john'。
至于重复的，可能是基于笛卡尔的结果，你有多个'约翰'记录作为作者，它是加入到每一个其他身份证的基础上的名字，而不是你认为你想得到什么。
ex数据。

Article Status
id author author_lastName
1  John   A
2  Bill   E
3  John   H
4  Mary   J
5  John   M

GroupPost
id  author  author_lastname
1   Mary    J
2   John    M
3   John    M
4   John    A
5   Bill    E
6   John    H

因此，仅从上面两个表的示例中，对于article表中的每个“john”，它在grouppost表中查找所有“john”名称，并将导致

Article ID   GroupPostIT
1 (John A)   2 (John M)
1 (John A)   3 (John M)
1 (John A)   4 (John A)
1 (John A)   6 (John H)

3 (John H)   2 (John M)
3 (John H)   3 (John M)
3 (John H)   4 (John A)
3 (John H)   6 (John H)

6 (John M)   2 (John M)
6 (John M)   3 (John M)
6 (John M)   4 (John A)
6 (John M)   6 (John H)

现在，把它也放到其他table上，你会发现你得到的比你期望的多。您可能需要更好的表之间的“id”关联，而不是通用名称。
例如，你的grouppost应该有authorid，而不仅仅是一个名字。。。其他table也一样。

mysql multiple left join返回重复的行

1条答案

相关问题

热门标签

最新问答