mysql - 计算表中的实例

Question

我有一个包含数百万条记录的表。记录看起来像这样:

   name    group+--------+------+|  aaa   |   1  ||  bbb   |   2  ||  ccc   |   1  ||  aaa   |   1  ||  aaa   |   2  |+--------+------+

• Each name can be repeated multiple times in one group.
• Each name can be in multiple groups.
• There are a lot of groups

I need to display a "report" with following information:

1. How many times each name occure in the table (sorted from highest to lowest).
2. How many times these names occure in each group.

Of course, I won't display information for all names, so I want to display only first 100 names (with the most occurences).

Example of desired output:

  name   count   group1  group2+------+-------+-------+-------+| aaa  |   3   |   2   |   1   || bbb  |   1   |   0   |   1   || ccc  |   1   |   1   |   0   |+------+-------+-------+-------+

So far, I counted names using this query:

select * from 
    (select name, count(name) as count from Names s 
     group by name order by count desc) r 
limit 100

我不知道如何计算每个组中上述查询返回的名称。

Answer 1

在mysql中，可以使用如下简写方式:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name    VARCHAR(12) NOT NULL
,category INT NOT NULL
);

INSERT INTO my_table (name,category) VALUES
('aaa',1),
('bbb',2),
('ccc',1),
('aaa',1),
('aaa',2);

SELECT * FROM my_table;
+----+------+----------+
| id | name | category |
+----+------+----------+
|  1 | aaa  |        1 |
|  2 | bbb  |        2 |
|  3 | ccc  |        1 |
|  4 | aaa  |        1 |
|  5 | aaa  |        2 |
+----+------+----------+


SELECT name
     , COUNT(*) total
     , SUM(category = 1) cat1
     , SUM(category = 2) cat2 
  FROM my_table 
 GROUP 
    BY name;
+------+-------+------+------+
| name | total | cat1 | cat2 |
+------+-------+------+------+
| aaa  |     3 |    2 |    1 |
| bbb  |     1 |    0 |    1 |
| ccc  |     1 |    1 |    0 |
+------+-------+------+------+