mysql组by并从右联接表中获取特定记录

Question

我的数据库具有以下数据结构和样本数据。每家公司都有多个成员。关系在company_member表中。请注意我在下面给出的需要的字段。

公司

id   title
1    company-1
2    company-2

company_member

companyid  memberid
1          1
1          2
1          3
2          4
2          5
2          6

成员

id   firstname  member_type_id
1    Name-1     2
2    Name-2     3
3    Name-3     3
4    Name-4     3
5    Name-5     2
6    Name-6     1

member_type

id   user_level
1    0
2    1
3    2

我想要唯一的公司名单，每个成员一个。但成员应该是最低的 user_level 在公司内。即，应取得以下结果；

结果

companyid  company_title  memberid  member_name  user_level
1          company-1      1         Name-1       1
2          company-2      6         Name-6       0

我想知道如何在同一家公司中得到一个用户水平最低的成员。

Answer 1

这有点复杂，但是这是使用not exists的一种方法，对于更大的表来说，使用not exits是明智的，因为它不使用索引。

select
c.id,
c.title,
m.id as member_id,
m.firstname,
mt.user_level
from company_member_map cmp
join company c on c.id = cmp.companyid
join member m on m.id = cmp.memberid
join member_type mt on mt.id = m.member_type_id
where not exists
(
  select 1 from company_member_map t1 
  join member t2 on t2.id = t1.memberid
  join member_type t3 on t3.id = t2.member_type_id
  where
  t1.companyid = cmp.companyid
  and t3.user_level < mt.user_level
)

演示

Answer 2

最后，我找到了解决方案：

select c.id companyid, c.title company_title, m.firstname member_name, mt.user_level
from company c
inner join company_member cm on cm.companyid = c.id
inner join member m on m.id = cm.memberid
inner join member_type mt on mt.id = m.member_type_id
inner join 
    (select c1.id companyid, mt1.user_level
        from company c1
        join company_member cm1 on cm1.companyid = c1.id
        join member m1 on m1.id = cm1.memberid
        join member_type mt1 on mt1.id = m1.member_type_id
        group by c1.id,m1.id
        order by user_level asc
    ) sq on c.id = sq.companyid and sq.user_level = mt.user_level
group by c.id;

如果有人有更好的解决方案或简化的解决方案，请纠正这一点。

检查这个