代码之家 › 专栏 › 技术社区 › squancy

MySQL多左联接返回重复行

left-join duplicates mysql php

-1

squancy · 技术社区 · 8 年前

我有一个mysql查询,它使用 LEFT JOIN 多次连接不同的表, author 是一样的。但是,当我 echo 结果它返回了大量的重复行(实际上 32,920 )当我只有4行记录在 grouppost 桌子和另外三张桌子里的大约100个。

表 status

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

表 article_status

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

表 群发

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

表 photos_status

+----+------+--------------+--------+------+-------+------+---------------------+
| 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) }

同样,我想从中选择所有受影响的行 文章\状态 ,请 群发 ,请 照片\状态 和 地位 表 作者 是一样的,就像 John 在示例中。

1 回复 | 直到 8 年前

DRapp 8 年前

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'

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

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 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)