Mysql如何获取group by语句中分组的行数

Question

我正在尝试获取每个结果行中分组在一起的行数。例如:

SELECT * FROM table GROUP BY transid，其他id如下表

id      transid  other id
------------------------------
|1      |1        |1        |
|2      |1        |1        |
|3      |1        |1        |
|4      |1        |2        |
|5      |1        |2        |
|6      |1        |2        |
|7      |2        |1        |
|8      |2        |1        |
|9      |2        |2        |
|10     |3        |1        |
|11     |3        |1        |

结果:

 id      transid  other id
------------------------------
|1      |1        |1        |
|4      |1        |2        |
|7      |2        |1        |
|10     |2        |2        |
|11     |3        |1        |

我怎样才能得到:

 id      transid  other id   count
-------------------------------------
|1      |1        |1     |  3
|4      |1        |2     |  3
|7      |2        |1     |  2
|9      |2        |2     |  1
|10     |3        |1     |  2

谢谢。

Answer 1

选择分组集合的min(id)并使用count()获取集合计数

Select min(id), transid, [other id], count(*) count
from table
Group by transid, [other id]