Question

我有以下表格:

表:用户组(many-to-many)

user_id (int)
group_id (varchar)

表:群体概况(many-to-many)

profile_id (int)
group_id (varchar)

因此,我基本上想写一份简要的文字,以找出分配给每个用户的情况。

因此,最终只有两栏:user_id和profile_id。

我怎么做呢?

<><>Edit>: 这实际上比简单的加入更加复杂。

E.g.

用户群体可能拥有以下各行:

1 group1
1 group2
1 group3
2 group1
2 group2
3 group4

(a) 团体或团体:

11 group1
11 group2
11 group3
21 group1
21 group2
22 group4

因此,结果应当

1 11
2 21
3 22

每个用户只应有一份情况说明。

Answer 1

我今天刚刚看到这样一个问题。我认为,这里的硬部分是,你重新寻找用户_id/profile_id组合,用户_id有每个团体_id_id_id_,至少没有。因此,要加入通常的做法,并增加一些关联性,以计算每个群体/用户的分布情况,并确保其匹配(这几次已经编辑):

 select user_id, profile_id 
    from user_groups join profile_groups on 
    user_groups.group_id=profile_groups.group_id 
    group by user_id, profile_id
    having count(user_groups.group_id) = 
    (select count(*) from profile_groups as pg where 
    pg.profile_id=profile_groups.profile_id)
    and count(profile_groups.group_id) = (select count(*) from user_groups as ug where 
    ug.user_id=user_groups.user_id)
    ;

这里的节目有两组,各有三组,其中一组是共同的,另一组是新用户。

sqlite>  create table user_groups (user_id integer, group_id varchar);
sqlite>  create table profile_groups (profile_id integer, group_id varchar);
sqlite>  insert into user_groups values(1,  group1 );
sqlite>  insert into user_groups values(1,  group2 );
sqlite>  insert into user_groups values(1,  group3 );
sqlite>  insert into user_groups values(2,  group1 );
sqlite>  insert into user_groups values(2,  group2 );
sqlite>  insert into user_groups values(3,  group4 );
sqlite>  insert into user_groups values(4,  group1 );
sqlite>  insert into user_groups values(4,  group5 );
sqlite>  insert into user_groups values(4,  group6 );
sqlite> 
sqlite>  insert into profile_groups values (11,  group1 );
sqlite>  insert into profile_groups values (11,  group2 );
sqlite>  insert into profile_groups values (11,  group3 );
sqlite> 
sqlite>  insert into profile_groups values (21,  group1 );
sqlite>  insert into profile_groups values (21,  group2 );
sqlite> 
sqlite>  insert into profile_groups values (22,  group4 );
sqlite> 
sqlite>  insert into profile_groups values (23,  group1 );
sqlite>  insert into profile_groups values (23,  group5 );
sqlite>  insert into profile_groups values (23,  group6 );
sqlite>  select user_id, profile_id 
   ...>     from user_groups join profile_groups on 
   ...>     user_groups.group_id=profile_groups.group_id 
   ...>     group by user_id, profile_id
   ...>     having count(user_groups.group_id) = 
   ...>     (select count(*) from profile_groups as pg where 
   ...>     pg.profile_id=profile_groups.profile_id)
   ...>     and count(profile_groups.group_id) = (select count(*) from user_groups as ug where 
   ...>     ug.user_id=user_groups.user_id)
   ...>     ;
1|11
2|21
3|22
4|23

Answer 2

   SELECT ug.user_id, pg.profile_id
     FROM user_groups AS ug
LEFT JOIN profile_groups AS pg
       ON ug.group_id = pg.group_id

Answer 3

这将向您提供一份与简介有关的用户名单:

SELECT ug.user_id,
       pg.profile_id
  FROM USER_GROUPS ug
  JOIN PROFILE_GROUPS pg ON pg.group_id = ug.group_id

......与此同时,这份清单将列出所有可能与简介相关的用户。如果没有,<代码>profile_id 页: 1

   SELECT ug.user_id,
          pg.profile_id
     FROM USER_GROUPS ug
LEFT JOIN PROFILE_GROUPS pg ON pg.group_id = ug.group_id

铭记由于这些关系是用户之一,对许多简介很熟悉,<代码>用户_id可能会有多个时间展示,并可能重复。对于未重复的数据清单,按条款添加“DISTINCT”条款或界定小组。 IE:

Using DISTINCT

   SELECT DISTINCT
          ug.user_id,
          pg.profile_id
     FROM USER_GROUPS ug
LEFT JOIN PROFILE_GROUPS pg ON pg.group_id = ug.group_id

Using GROUP BY

   SELECT ug.user_id,
          pg.profile_id
     FROM USER_GROUPS ug
LEFT JOIN PROFILE_GROUPS pg ON pg.group_id = ug.group_id
 GROUP BY ug.user_id, pg.profile_id

Answer 4

jjia6395, judging from the comments below zzzeek s answer, you want to use the ALL operator. BTW, your question is terribly unclear, the edit didn t help.

Using DISTINCT

Using GROUP BY

友情链接