主页 / dba / 问题 / 316304
Accepted
Jonathon Doesen
Asked: 2022-09-01 10:27:19 +0800 CST

按单个成员选择组并对其他值求和

  • 772

使用 MySql 5.7.34 并有一个表 groups_members，有 4 列

ID 团体 成员 组织
1 一个 鲍勃 苹果
2 一个 苏珊 苹果
3 一个 乔 苹果
4 乙 史蒂夫 微软
5 乙 麦克风 微软
6 乙 鲍勃 微软
7 C 苏珊 苹果
8 C 鲍勃 苹果
9 C 乔 苹果

我想选择同时拥有 Bob 和 Joe 作为成员的所有组，并且该组织仅是 Apple，每个组在结果中只有一行。

预期成绩：

团体
一个
C

如果可能的话，理想情况下，我还希望从同一查询中的另一个表中为每个组选择和求和一些值。基本上得到所有以 Bob 和 Joe 作为成员的团体，并获得该团体的综合积分和里程：

ID 团体 积分 英里
1 一个 10 100
1 C 10 150
1 一个 10 100
1 乙 10 200
1 乙 10 100

所以理想的预期结果实际上是：

团体 积分 英里
一个 20 200
C 10 150

我可以想出一些方法来使用多个查询和一些应用程序逻辑来做到这一点。但是，如果可以通过一个查询完成所有操作，那就太好了。

mysql group-by

2 个回答

  1. Best Answer

    一种可能的方式：

    select gm.`group`,SUM(at.points) as points,SUM(at.miles) as miles
from another_table at 
inner join (select `group` 
            from groups_members
            where member in ('Bob','Joe')
            group by `group`
            having count(distinct member) >=2 
            ) as gm on at.`group`=gm.`group`
where not exists (select 1 
                 from groups_members g 
                 where gm.`group` = g.`group` 
                 and organization <> 'Apple')
group by gm.`group`;

    注意。我将组织从 更改(2,'A','Susan','Apple'),为(2,'A','Susan','Microsoft'),，仅用于测试目的

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=af373d5f67a7f9918b6e441be95723d6

    请不要在您的情况下使用MySQL保留字group。如果可能的话，我建议重命名，否则你必须把它放在反引号内。

    子查询只返回group至少有 ('Bob','Joe') 的，它也可能是其他的。

    这where not exists将过滤所有具有组织！='Apple'的组。

    下面的索引应该有助于优化器加速查询

    alter table `groups_members`  add index `gr_me` (`group`,`member`);
alter table `another_table `  add index `group` (`group`);
    • 0

  2. 第一个查询：

    SELECT DISTINCT gm1.group
  FROM groups_members AS gm1
  JOIN groups_members AS gm2  ON gm2.group = gm1.group
                             AND gm2.member <> gm1.member
 WHERE gm1.member IN ("Bob","Joe")
   AND gm2.member IN ("Bob","Joe")
   AND gm1.organization = "Apple" 
   AND gm2.organization = "Apple"
;

    第二个查询有点不清楚，特别是C组的积分和里程计算。

    • 0

相关问题