求助 mysql数据统计

wc1217 2011-06-20 11:56:12

mm_info表结构如下:

SELECT id,userid,uptime,type,material FROM mm_info m;

533675, 11902, 2011-06-17 18:12:45, 0, 'HRB400'

532829, 11896, 2011-06-17 10:18:35, 0, 'SPHC'

532790, 11896, 2011-06-17 10:14:11, 0, 'SPHE'

532564, 11895, 2011-06-17 09:16:09, 0, '321/2B'

530886, 11893, 2011-06-16 16:07:08, 0, 'DC01'

530976, 11892, 2011-06-16 16:09:49, 0, 'Q245R'

533357, 11888, 2011-06-17 12:12:32, 0, '441/2D'

533332, 11888, 2011-06-17 12:11:53, 0, '441/2D'

533307, 11888, 2011-06-17 12:10:04, 0, '441/2D'

532291, 11887, 2011-06-17 08:42:50, 0, '321/N01'

533576, 11886, 2011-06-17 14:51:46, 0, 'Q235B'

533049, 11886, 2011-06-17 11:08:16, 0, 'Q235B'

533037, 11886, 2011-06-17 11:08:15, 0, 'Q235B'

532765, 11886, 2011-06-17 10:04:12, 0, 'Q235B'

532680, 11886, 2011-06-17 09:49:21, 0, 'Q235B'

532678, 11886, 2011-06-17 09:49:20, 0, 'Q235B'

532208, 11886, 2011-06-17 08:38:47, 0, 'Q235B'

531002, 11886, 2011-06-16 16:11:24, 0, 'Q235B'

530833, 11885, 2011-06-16 15:14:02, 0, 'Q235B'

530801, 11885, 2011-06-16 15:14:01, 0, 'Q235B'

530719, 11883, 2011-06-16 14:53:53, 0, 'Q235B'

532421, 11881, 2011-06-17 08:45:36, 0, '409L'

mm_tj表结构如下:

userid   titime     sun

10331    2011-06-20  2

现要将mm_info的一天一个用户id的次数统计进mm_tj的表里.
日前我只能筛选出每天用户上传的明细.要怎么count(userid)确定这个值(一天一次.一共有多少次)呢?
我的方法如下:



insert into mm_tj 

SELECT userid,DATE_FORMAT(uptime,"%Y-%m-%d") as uptime,count(userid) 

FROM mm_info m group by DATE_FORMAT(uptime,"%Y-%m-%d") 

order by userid desc,

uptime desc;

我期望的结果如下:



userid  titime    sun

11902, 2011-06-17, 1

11896, 2011-06-17, 2

11895, 2011-06-17, 1

11893, 2011-06-16, 1

11892, 2011-06-16, 1

11888, 2011-06-17, 3

11887, 2011-06-17, 1

11886, 2011-06-16, 8

11885, 2011-06-16, 2

11883, 2011-06-16, 1

11881, 2011-06-17, 1

...全文