Ask a database question. there is a table containing the data stored every day and how to retrieve the number of data entries per month.

Source: Internet
Author: User
Ask a database question. there is a table containing the data stored every day and how to retrieve the number of data entries per month. This is the ec_ip_article table, and iptime is the daily timestamp. Is there a simple way to get an SQL statement every month, and then count the number, that is, there are too many SQL statements.


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 % Y-% m in the timestamp?

This is the ec_ip_article table, and iptime is the daily timestamp. Is there a simple way to get an SQL statement every month, and then count the number, that is, there are too many SQL statements.

$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);dump($row);
Why can't I retrieve 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);dump($row);
Why can't I retrieve it?

Impossible! 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 ec_ip_article group by 1";$rs = mysql_query($sql);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 ec_ip_article group by 1";$rs = mysql_query($sql);while($r = mysql_fetch_assoc($rs))  echo join(' ', $r), PHP_EOL;
2015-02 22015-03 12015-04 2

In the ec_ip_article table, if there is a user ID field next to it, it is not feasible to add the where field directly when calculating the number of times of each user per month.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.