mysql 多个 Id_MySQL查询多个ID

匿名用户

从您的问题来看，我相信您当前的表结构如下:TABLE: user TABLE: project TABLE: shared

id | email | | id | user_id | content | | id | user_id | project_id

---+-------------------- ---+---------+------------------------------ ---+---------+------------

1 | james@website.com | | 1 | 1 | Project for James | | 9 | 1 | 5

2 | hannah@website.com | | 2 | 1 | Some other project for James | | 10 | 3 | 5

3 | lucy@website.com | | 3 | 2 | Project for Hannah | | 11 | 1 | 8

| | | 4 | 2 | A new project for hannah | | 12 | 2 | 8

| | | 5 | 2 | Hannah's pride and Joy | |

| | | 6 | 3 | Lucy cracking down | |

| | | 7 | 3 | Lucy's second project | |

| | | 8 | 3 | Lucy's public stuff | |

示例:https://www.db-fiddle.com/f/6knesgumy5ps42usmzytex/0SELECT project.id, project.user_id AS owner_id, shared.user_id AS shared_id, project.content

FROM project

LEFT JOIN shared

ON project.id = shared.project_id

AND project.user_id <> ?

WHERE project.user_id = ?

OR shared.user_id = ?;

注意:。

此SQL语句与问题中的SQL语句之间的主要区别是AND project.user_id <> ?

如果子句中没有该条件，您将为该用户的每个共享项目获得重复记录。即。如果用户已经与20个用户共享了项目，那么将有20个重复。

这是预期的行为，正如这里所解释的:PHP while语句回显重复项$sql = "

SELECT project.id, project.user_id AS owner_id, shared.user_id AS shared_id, project.content

FROM project

LEFT JOIN shared

ON project.id = shared.project_id

AND project.user_id <> ?

WHERE project.user_id = ?

OR shared.user_id = ?

";

$query = $mysqli->prepare($sql);

$query->bind_param("iii", $user_id, $user_id, $user_id);

$query->execute();

我建议更新表结构，使您有三个表(有效地:，和表充当两个实体(codeusers/code>和TABLE: user TABLE: project TABLE: project_user

id | email | | id | content | | id | user_id | project_id | role

---+-------------------- ---+------------------------------ ---+---------+------------+-----

1 | james@website.com | | 1 | Project for James | | 1 | 1 | 1 | 1

2 | hannah@website.com | | 2 | Some other project for James | | 2 | 1 | 2 | 1

3 | lucy@website.com | | 3 | Project for Hannah | | 3 | 2 | 3 | 1

| | | 4 | A new project for hannah | | 4 | 2 | 4 | 1

| | | 5 | Hannah's pride and Joy | | 5 | 2 | 5 | 1

| | | 6 | Lucy cracking down | | 6 | 3 | 6 | 1

| | | 7 | Lucy's second project | | 7 | 3 | 7 | 1

| | | 8 | Lucy's public stuff | | 8 | 3 | 8 | 1

| | | | | | 9 | 1 | 5 | 2

| | | | | | 10 | 3 | 5 | 2

| | | | | | 11 | 1 | 8 | 2

| | | | | | 12 | 2 | 8 | 2

示例:https://www.db-fiddle.com/f/imqz6cveeff4vgrq4v22qo/0SELECT project.id, project_user.user_id, project_user.role, project.content

FROM project

JOIN project_user

ON project_user.project_id = project.id

WHERE project_user.user_id = ?;$sql = "

SELECT project.id, project_user.user_id, project_user.role, project.content

FROM project

JOIN project_user

ON project_user.project_id = project.id

WHERE project_user.user_id = ?

";

$query = $mysqli->prepare($sql);

$query->bind_param("i", $user_id);

$query->execute();