嗯,对不起,我叙述有误,情况是这样的,我在一个表中有三个字段: id state date ---------------------------------- 1 1 2006-1-2 2 0 2006-1-2 3 0 2006-1-3...........id 是自增字段,state 为1时表示收入,0为付出,date是发生日期,我现在想统计全年各月份 收入及付出各是多少笔(不是金额,是笔数).非常感谢大家!
SELECT count(*),state FROM tablename WHERE date = '2006-01-02' GROUP BY state
首先,如果你想统计全年各个月份, 那么加个冗余字段记录年月比较好,month smallint(6) unsigned not null, key month(month) 比如2005年2月就存200502。至于sql可以如下写: select count(*) as account, month, state from tab_name where month>=200501 and month<=200512 group by month,state [order by month] 按照你上面的写是这样 select count(*) as account, substring(date, 1, 7) as month, state from tab_name where date>='2005-01-01' and month<='2005-12-31' group by substring(date, 1, 7),state [order by substring(date, 1, 7)]
----------------------------------
1 1 2006-1-2
2 0 2006-1-2
3 0 2006-1-3...........id 是自增字段,state 为1时表示收入,0为付出,date是发生日期,我现在想统计全年各月份
收入及付出各是多少笔(不是金额,是笔数).非常感谢大家!
那么加个冗余字段记录年月比较好,month smallint(6) unsigned not null, key month(month)
比如2005年2月就存200502。至于sql可以如下写:
select count(*) as account, month, state from tab_name where month>=200501 and month<=200512 group by month,state [order by month]
按照你上面的写是这样
select count(*) as account, substring(date, 1, 7) as month, state from tab_name where date>='2005-01-01' and month<='2005-12-31' group by substring(date, 1, 7),state [order by substring(date, 1, 7)]