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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值