我有一个 MySQL 查询，它LEFT JOIN多次使用以连接相同的不同表author。但是，当我echo输出结果时，当我32,920在grouppost表中只记录了 4 行，而在其他三行中记录了大约 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 |
+----+------+--------------+--------+------+-------+------+---------------------+
桌子 grouppost
+----+-----+--------+--------+------+-------+----------------------+
| 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);
寻找可能的错误：
var_dump($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)返回 32,920，这是完全错误的。
var_dump($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) }
同样，我想从、和table 中选择所有受影响的行article_status，其中相同，就像在示例中一样。grouppostphotos_statusstatusauthorJohn
首先，您的查询应简化为以下内容。
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'。
至于重复，可能是基于笛卡尔结果，您有多个“约翰”记录作为作者，并且它根据名称而不是您认为要获得的名称连接到每个其他 ID。
防爆数据。
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)
现在，把它也放到其他桌子上，你会发现你得到的比你预期的要多。与通用的通用名称相比，您可能需要更好的表之间的“ID”关联。
例如，您的 GroupPost 应该具有 authorID，而不仅仅是名称...与其他表相同。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句