计数和分组

Question

我想分组和计数来自数据库的信息。

我有这样的数据：

column_a | column_b 
------------------- 
A  | 1 
A  | 2 
A  | 2 
B  | 3 
B  | 3 
A  | 3 
B  | 3 

我想数据以创建一个列计数匹配的column_a值存入a_total的行数。然后，我想将column_b值组合为A的种类。然后数据应该按a_total降序输出，然后按column_b递减输出。

我想这样的输出：

column_a | column_b | a_total | b_total 
--------------------------------------- 
A  | 3  | 4  | 1 
A  | 2  | 4  | 2 
A  | 1  | 4  | 1 
B  | 3  | 3  | 3 

我有这么远...

到目前为止，我有SELECT column_a, column_b, COUNT(*) AS b_total FROM this_table GROUP BY column_a, column_b ORDER BY column_b DESC, b_total DESC这使column_b到为了与正确b_total但我不确定如何在相同的查询中将column_a的总数变为a_total。

任何帮助将是伟大的！

Answer 1

CREATE DATABASE test; 
USE test; 
CREATE TABLE t (a VARCHAR(5), b INT); 
INSERT t VALUE ('A', 1),('A', 2),('A', 2),('B', 3),('B', 3),('A', 3),('B', 3); 

/*查询*/

SELECT xx.a , xx.b , yy.a_total , zz.b_total 
FROM 
    (SELECT DISTINCT a, b FROM t) xx 
    INNER JOIN (SELECT COUNT(a) AS a_total, a FROM t GROUP BY a) yy 
    ON xx.a = yy.a 
    INNER JOIN (SELECT COUNT(a) AS b_total , a , b FROM t GROUP BY a, b) zz 
    ON (zz.a = xx.a AND zz.b = xx.b)