mysql group by多个字段

Question

SELECT 
    用户ID,
    FROM_UNIXTIME(时间戳,'%Y%m') month 
FROM 
    table 
GROUP BY 
    month,用户ID

以上语句想在table表中查询出用户ID和月份，然后使用月份和用户ID（表中同一个用户ID可能出现多次）进行分组，如何在SELECT后添加一个数量字段再按月份统计出用户的数量？

Answer 1

Can you please stop writing the problem in the code? . . Think about the user experience. . .

Answer 2

Yes, group by can be followed by multiple fields, just separate them with commas.

Answer 3

Can I understand what the poster said （表中同一个用户ID可能出现多次） to mean that he wants to get the number of unique users per month?

If so, the Sql statement would be like this

SELECT COUNT(DISTINCT 用户ID) count,FROM_UNIXTIME(时间戳,'%Y%m') month FROM table GROUP BY month

If not, that’s it

SELECT COUNT(用户ID) count,FROM_UNIXTIME(时间戳,'%Y%m') month FROM table GROUP BY month

Answer 4

Use the form count(xxxx) as xxxx_num

Answer 5

It would be unrealistic to display all user IDs in a single column as requested by the poster.
If the number of users is counted by month, it should be
SELECT COUNT(DISTINCT user ID) count,FROM_UNIXTIME(timestamp,' %Y%m') month FROM table GROUP BY month