MySQL如何从GROUP BY中计数

Question

date    | userid | companyid  
12.8.14 |   1    |    1  
12.8.14 |   2    |    2  
12.8.14 |   3    |    1

我有一张像上面这样的 table 。通过正常查询，很容易从表中统计出有多少家公司1。

我的问题是:如果我的查询是select * from table where companyid = '1' group by date，我怎样才能让公司的mysql_num_row等于21 和 userid 是 1 和 3？

select * from table where companyid = '1' group by date 只会返回我mysql_num_row 等于 1 且结果 12.8.14 | 1 | 1

Answer 1

您可以嵌套查询来获取第一家公司条目的总和，然后将该嵌套查询连接到外部查询:

SELECT ABB2.*, ABB1.mysql_num_row
FROM `table` AS ABB2
  JOIN 
      (SELECT companyid, COUNT(userid) AS mysql_num_row
      FROM `table`
      GROUP BY companyid) AS ABB1 ON ABB1.companyid = ABB2.companyid
WHERE ABB2.companyid = 1;

Example