56,679
社区成员
发帖
与我相关
我的任务
分享
mysql> create table test(
-> id int not null primary key auto_increment,
-> ddate date unique,
-> dnum int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into test values(null,'2009-11-01',39),(null,'2009-11-05',80),
-> (null,now(),14);
Query OK, 3 rows affected, 1 warning (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 1
mysql> insert into test values(null,now(),1)
-> on duplicate key update dnum=dnum+1;
Query OK, 2 rows affected, 1 warning (0.42 sec)
mysql> select * from test;
+----+------------+------+
| id | ddate | dnum |
+----+------------+------+
| 1 | 2009-11-01 | 39 |
| 2 | 2009-11-05 | 80 |
| 3 | 2009-11-09 | 15 |
+----+------------+------+
3 rows in set (0.00 sec)
---是指计算下载量,对吧?
---加个日期字段(download_date) 就可以了啊,
---然后根据日期字段出查询数据啊
select id, 下载地址, 名称,
count(case when (TO_DAYS(NOW())-TO_DAYS(download_date))=0 then 1 else 0) as 今日下载,
count(case when (TO_DAYS(NOW())-TO_DAYS(download_date))=1 then 1 else 0) as 昨日下载,
count(case when (TO_DAYS(NOW())-TO_DAYS(download_date))=7 then 1 else 0) as 本周下载
from tb1
group by id, 下载地址, 名称;