Mysql跨组聚合

Question

我正在尝试按多列进行分组，但还需要跨组聚合

我有下表

并使用以下查询

SELECT A.col1, 
       A.col2, 
       A.subgrouptotal, 
       B.grouptotal 
FROM   (SELECT col1, 
               col2, 
               Count(col1) AS SubgroupTotal 
        FROM   `mytemptbl` 
        GROUP  BY col1, 
                  col2) AS A 
       INNER JOIN (SELECT col1, 
                          Count(col1) AS GroupTotal 
                   FROM   `mytemptbl` 
                   GROUP  BY col1) AS B 
               ON B.col1 = A.col1 

我有以下结果

有没有一种更简洁的方法可以做到这一点，而无需在两个单独的选择上进行内部联接？

Answer 1

您可以避免在查询中使用两个派生表。在基本查询中，您可以获得 SubgroupTotal。然后，您可以将其连接到另一个获取 GroupTotal 的子选择(派生表)。

尝试:

SELECT A.col1, 
       A.col2, 
       COUNT(*) AS SubgroupTotal, 
       B.GroupTotal 
FROM   mytemptbl AS A 
INNER JOIN (SELECT col1, 
                   Count(*) AS GroupTotal 
            FROM   `mytemptbl` 
            GROUP  BY col1) AS B ON B.col1 = A.col1 
GROUP BY A.col1, A.col2