Ask for a database problem, there is a table, there are daily data deposited, how to take out the number of data per month.

This is the Ec_ip_article table, Iptime is the time stamp of the day. There is no easy way to do, do not want to every month of SQL out, and then count the number, that SQL too much.

Reply to discussion (solution)

Select From_unixtime (iptime, '%y-%m ') as M, Count (*) as CNT from Ec_ip_article GROUP by 1

Select From_unixtime (iptime, '%y%m%d '), COUNT (0) from Ec_ip_article Group by From_unixtime (Iptime, '%y%m%d ')

Select From_unixtime (iptime, '%y%m%d '), COUNT (0) from Ec_ip_article Group by From_unixtime (Iptime, '%y%m%d ')

Select From_unixtime (iptime, '%y%m '), COUNT (0) from Ec_ip_article Group by From_unixtime (Iptime, '%y%m ')

SELECT Ip_id,count (ip_id) count,from_unixtime (iptime, '%y%m%d ') date from Test GROUP by From_unixtime (iptime, '%c ') ORDER by Iptime

Select From_unixtime (iptime, '%y-%m ') as M, Count (*) as CNT from Ec_ip_article GROUP by 1

Is the%y-%m inside a time stamp?

This is the Ec_ip_article table, Iptime is the time stamp of the day. There is no easy way to do, do not want to every month of SQL out, and then count the number, that SQL too much.

$sql = "Select From_unixtime (iptime, '%y-%m ') as M, Count (*) as CNT from Ec_ip_article GROUP by 1  where  user_id=392" , $result =mysql_query ($sql), $row =mysql_fetch_row ($result);d UMP ($row);

Why can't I get it?

Select From_unixtime (iptime, '%y-%m ') as M, Count (*) as CNT from Ec_ip_article GROUP by 1

$sql = "Select From_unixtime (iptime, '%y-%m ') as M, Count (*) as CNT from Ec_ip_article GROUP by 1  where  user_id=392" , $result =mysql_query ($sql), $row =mysql_fetch_row ($result);d UMP ($row);

Why can't I get it?

No way! How did you write it?

Test example

Mysql_connect (); mysql_select_db (' Test '), mysql_query ("Create temporary table ec_ip_article (iptime int)"), $a = Array (' 2015-02-10 ', ' 2015-02-20 ', ' 2015-03-10 ', ' 2015-04-10 ', ' 2015-04-21 ', '; foreach ($a as $t) mysql_query (sprintf ("Insert into ec_ip_article values (%d) ", Strtotime ($t))) $sql =" Select From_unixtime (iptime, '%y-%m ') as M, Count (*) as CNT from E C_ip_article GROUP by 1 "; $rs = mysql_query ($sql) and while ($r = Mysql_fetch_assoc ($rs))  Echo Join (', $r), Php_eol;

2015-02 22015-03 12015-04 2

Test example

Mysql_connect (); mysql_select_db (' Test '), mysql_query ("Create temporary table ec_ip_article (iptime int)"), $a = Array (' 2015-02-10 ', ' 2015-02-20 ', ' 2015-03-10 ', ' 2015-04-10 ', ' 2015-04-21 ', '; foreach ($a as $t) mysql_query (sprintf ("Insert into ec_ip_article values (%d) ", Strtotime ($t))) $sql =" Select From_unixtime (iptime, '%y-%m ') as M, Count (*) as CNT from E C_ip_article GROUP by 1 "; $rs = mysql_query ($sql) and while ($r = Mysql_fetch_assoc ($rs))  Echo Join (', $r), Php_eol;

2015-02 22015-03 12015-04 2

Ec_ip_article table If there is a user ID field behind it, and then count the number of times per user per month, just add where.

