如何从表中插入每个外键的新行

发布于 2025-01-31 17:53:34 字数 1242 浏览 4 评论 0原文

我想为每个用户添加一个查询，所有ID_JOB，ID_ROLE对使用ID 0（ID_USER，默认用户）。在MySQL中可能吗？

user_role 表

ID_USER	ID_JOB	ID_role
0	1	1 1
0	2	2
1	1 1	1
1	2	2 2
2	3	1

这些是我要到达的日期。如您在第一张表中所看到的，对于具有ID 2的用户，我们没有ID_JOB和ID_ROLE [（1,1），（2,2）]，我想运行一个查询，以使缺失的对这个用户。

user_role_updated 表

ID_USER	ID_JOB	ID_ROL
0	1	1
0	2 2	2
1	1	1 1
1	1 2	2
2 2	3	1
2 2	1	1
2	2	2

预先感谢您！

原文

I want to make a query to add for each user all the id_job, id_role pairs from the user with id 0 (id_user, default user). Is it possible in mysql?

user_role table

id_user	id_job	id_role
0	1	1
0	2	2
1	1	1
1	2	2
2	3	1

These are the dates I want to get to. As you can see in the first table, for the user with id 2, we don't have id_job and id_role [(1,1), (2,2)] and I want to run a query that populates the missing pairs for this user.

user_role_updated table

id_user	id_job	id_role
0	1	1
0	2	2
1	1	1
1	2	2
2	3	1
2	1	1
2	2	2

Thank you in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晚风撩人 2025-02-07 17:53:34

insert into user_role(id_user, id_job, id_role)
with cte_0 as(select id_user
                     , id_job
                     , id_role 
              from user_role 
              where id_user = 0)
select distinct ur.id_user
                , cte_0.id_job
                , cte_0.id_role
from user_role ur
     , cte_0
where ur.id_user <> 0
and (ur.id_user, cte_0.id_job, cte_0.id_role) not in (select id_user, id_job, id_role from user_role where id_user <> 0)

insert into user_role(id_user, id_job, id_role)
with cte_0 as(select id_user
                     , id_job
                     , id_role 
              from user_role 
              where id_user = 0)
select distinct ur.id_user
                , cte_0.id_job
                , cte_0.id_role
from user_role ur
     , cte_0
where ur.id_user <> 0
and (ur.id_user, cte_0.id_job, cte_0.id_role) not in (select id_user, id_job, id_role from user_role where id_user <> 0)

Here is a demo

回复收藏 0 原文

~没有更多了~