如何在sql中对复杂的条件进行分组

海森堡

我想按谁有的region地方分组customerstype=a

region customer type score
A       a        a     1
A       b        b     2
A       c        a     3 
B       d        c     4 
B       e        d     5 
C       f        a     6 
C       g        c     7

因此在第一步之后

region customer type score
A       a        a     1
A       b        b     2
A       c        a     3 
C       f        a     6 
C       g        c     7

然后我分组 region

region  sum(score)
A         6
C        13

我也想提取客户 type=a

region customer type
A         a      a
A         c      a 
C         f      a

然后我想合并上面。

我想要的结果如下

customer sum_in_region
a           6
c           6
f          13

有没有办法实现这一目标？

我的工作正在进行到第二步..我该如何继续？

SELECT t1.region,t1.customer, t1.type, t1.score
  FROM yourTable t1
 WHERE EXISTS (SELECT 1
                 FROM yourTable t2
                WHERE t2.region = t1.region
                  AND t2.type = 'a');

谢谢

粘点

将表连接到执行前两个步骤的派生表。

SELECT t3.customer,
       x1.score
       FROM yourtable t3
            INNER JOIN (SELECT t1.region,
                               sum(score) score
                               FROM yourtable t1
                                    WHERE EXISTS (SELECT *
                                                         FROM yourtable t2
                                                         WHERE t2.region = t1.region
                                                               AND t2.type = 'a')
                               GROUP BY t1.region) x1
                       ON x1.region = t3.region
       WHERE t2.type = 'a';

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。