MySQL 查询将两个表连接在一起，并获取包含组合数据的一行

Question

我的数据库建模很差，并且有两个表(groupowner 和 groupmembers)，其数据几乎相同。我想创建一个将它们组合并按用户分组的 View 。

使用以下查询...

 SELECT 'group' AS resourceType, gm.gid AS resource_id, gm.personid AS account_id, false AS isManager, true AS isMember
 FROM data.groupmembers gm
     UNION
 SELECT 'group' AS resourceType, gn.gid AS resource_id, gn.owner AS account_id, true AS isManager, false AS isMember
 FROM data.groupowner gn
     ORDER BY resource_id ASC, account_id ASC;

...我得到这样的东西:

resourceType | resource_id | account_id | isManager | isMember
------------ | ----------- | ---------- | --------- | --------
group        | 105         | 506        | 1         | 0
group        | 105         | 506        | 0         | 1
etc...

..当我想要的是这个(从两个表或一个或另一个表组合)时:

resourceType | resource_id | account_id | isManager | isMember
------------ | ----------- | ---------- | --------- | --------
group        | 105         | 506        | 1         | 1
etc...

有什么方法可以通过 MySQL 查询来完成此操作吗？

Answer 1

我认为您需要join而不是union all。假设所有所有者都是成员，这应该可以工作并且在 MySQL 中与“ View ”兼容:

SELECT 'group' AS resourceType, gm.gid AS resource_id, gm.personid AS account_id,
       (gn.personid is not null) AS isManager, true AS isMember
FROM data.groupmembers gm LEFT JOIN
     data.groupowner gn
     ON gm.gid = gn.gid and
        gm.personid = gn.personid;

另一种方法使用相关子查询:

select gm.*,
       (exists (select 1
                from data.groupowner gn
                where gm.gid = gn.gid and gm.personid = gn.personid
       ) as IsManager
from data.groupmembers;

如果所有者是成员(member)，我不确定您为什么需要“isMember”列。

编辑:

如果所有者不是成员，那么您将面临获取 View 兼容逻辑的挑战。但是，这应该有效:

SELECT 'group' AS resourceType, gm.gid AS resource_id, gm.personid AS account_id,
       (gn.personid is not null) AS isManager, true AS isMember
FROM data.groupmembers gm LEFT JOIN
     data.groupowner gn
     ON gm.gid = gn.gid and
        gm.personid = gn.personid
UNION ALL
SELECT 'group', gn.gid, gn.personid,
       true, false
FROM data.groupowner gn
WHERE NOT EXISTS (SELECT 1
                  FROM data.groupmembers
                  WHERE gm.gid = gn.gid and gm.personid = gn.personid
                 );