求教一个数据库问题,有张表,里面有每天存入的数据,怎么取出每个月数据的条数。
Jun 20, 2016 pm 12:45 PM 这是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
这是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;
2015-02 22015-03 12015-04 2

Heißer Artikel

Hot-Tools-Tags

Heißer Artikel

Heiße Artikel -Tags

Notepad++7.3.1
Einfach zu bedienender und kostenloser Code-Editor

SublimeText3 chinesische Version
Chinesische Version, sehr einfach zu bedienen

Senden Sie Studio 13.0.1
Leistungsstarke integrierte PHP-Entwicklungsumgebung

Dreamweaver CS6
Visuelle Webentwicklungstools

SublimeText3 Mac-Version
Codebearbeitungssoftware auf Gottesniveau (SublimeText3)

Heiße Themen

11 beste PHP -URL -Shortener -Skripte (kostenlos und Premium)

Arbeiten mit Flash -Sitzungsdaten in Laravel

Vereinfachte HTTP -Reaktion verspottet in Laravel -Tests

Curl in PHP: So verwenden Sie die PHP -Curl -Erweiterung in REST -APIs

Erstellen Sie eine React -App mit einem Laravel -Back -Ende: Teil 2, reagieren

12 Beste PHP -Chat -Skripte auf Codecanyon
