求教一个数据库问题，有张表，里面有每天存入的数据，怎么取出每个月数据的条数。-php教程-PHP中文网

求教一个数据库问题，有张表，里面有每天存入的数据，怎么取出每个月数据的条数。

php中文网

发布： 2016-06-20 12:45:00

原创

1151人浏览过

这是ec_ip_article表，iptime是每天的时间戳。有没有简便一点的方法，不想每个月一条条sql取出来，然后再count得出数目，那样sql太多了。

阿里云-虚拟数字人

阿里云-虚拟数字人是什么？ ...

查看详情

回复讨论(解决方案)

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

登录后复制

里面的%Y-%m是时间戳吗？

这是ec_ip_article表，iptime是每天的时间戳。有没有简便一点的方法，不想每个月一条条sql取出来，然后再count得出数目，那样sql太多了。

$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);

登录后复制

为什么取不出来

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);

登录后复制

为什么取不出来

不可能！你是怎么写的？

测试例

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

登录后复制

测试例

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;

登录后复制