select month(日期)月份,count(*)单据数 from 表 group by month(日期)
如果还要年的话,请加上: select month(日期)月份,count(*)单据数 from 表 where year(日期)=2011 group by month(日期)
select 一月=sum(case when month([date])=1 then 1 else 0 end), 二月=sum(case when month([date])=1 then 1 else 0 end), 三月=sum(case when month([date])=1 then 1 else 0 end), ........... from tb
select convert(varchar(6),date,112) as date,count(*) as cnt from tb group by convert(varchar(6),date,112)
select sum(case 月份字段 when '1月' then 数量 else 0 end ) as '1月' , sum(case 月份字段 when '2月' then 数量 else 0 end ) as '2月' , ..... from table
select 一月=sum(case when month([date])=1 then 1 else 0 end), 二月=sum(case when month([date])=2 then 1 else 0 end), 三月=sum(case when month([date])=3 then 1 else 0 end), ...........--类推到12月 from tb
select [1月]=sum(case when 日期字段 between '2011-01-01' and '2011-01-31' then 单据 else 0 end), [2月]=sum(case when 日期字段 between '2011-02-01' and '2011-02-28' then 单据 else 0 end), [3月]=sum(case when 日期字段 between '2011-03-01' and '2011-03-31' then 单据 else 0 end), [4月]=sum(case when 日期字段 between '2011-04-01' and '2011-04-30' then 单据 else 0 end), [5月]=sum(case when 日期字段 between '2011-05-01' and '2011-05-31' then 单据 else 0 end), [6月]=sum(case when 日期字段 between '2011-06-01' and '2011-06-30' then 单据 else 0 end), [7月]=sum(case when 日期字段 between '2011-07-01' and '2011-07-31' then 单据 else 0 end), [8月]=sum(case when 日期字段 between '2011-08-01' and '2011-08-31' then 单据 else 0 end), [9月]=sum(case when 日期字段 between '2011-09-01' and '2011-09-30' then 单据 else 0 end), [10月]=sum(case when 日期字段 between '2011-10-01' and '2011-10-31' then 单据 else 0 end), [11月]=sum(case when 日期字段 between '2011-11-01' and '2011-11-30' then 单据 else 0 end), [12月]=sum(case when 日期字段 between '2011-12-01' and '2011-12-31' then 单据 else 0 end) from tablename
select [1月]=sum(case when month(date) = 1 then 1 else 0 end), [2月]=sum(case when month(date) = 2 then 1 else 0 end), [3月]=sum(case when month(date) = 3 then 1 else 0 end), [4月]=sum(case when month(date) = 4 then 1 else 0 end), [5月]=sum(case when month(date) = 5 then 1 else 0 end), [6月]=sum(case when month(date) = 6 then 1 else 0 end), [7月]=sum(case when month(date) = 7 then 1 else 0 end), [8月]=sum(case when month(date) = 8 then 1 else 0 end), [9月]=sum(case when month(date) = 9 then 1 else 0 end), [10月]=sum(case when month(date) = 10 then 1 else 0 end), [11月]=sum(case when month(date) = 11 then 1 else 0 end), [12月]=sum(case when month(date) = 12 then 1 else 0 end) from tablename where date between '2010-01-01' and '2011-06-20'
select month(日期)月份,count(*)单据数 from 表 where year(日期)=2011 group by month(日期)
二月=sum(case when month([date])=1 then 1 else 0 end),
三月=sum(case when month([date])=1 then 1 else 0 end),
...........
from tb
select convert(varchar(6),date,112) as date,count(*) as cnt
from tb
group by convert(varchar(6),date,112)
sum(case 月份字段 when '2月' then 数量 else 0 end ) as '2月' ,
.....
from table
二月=sum(case when month([date])=2 then 1 else 0 end),
三月=sum(case when month([date])=3 then 1 else 0 end),
...........--类推到12月
from tb
[1月]=sum(case when 日期字段 between '2011-01-01' and '2011-01-31' then 单据 else 0 end),
[2月]=sum(case when 日期字段 between '2011-02-01' and '2011-02-28' then 单据 else 0 end),
[3月]=sum(case when 日期字段 between '2011-03-01' and '2011-03-31' then 单据 else 0 end),
[4月]=sum(case when 日期字段 between '2011-04-01' and '2011-04-30' then 单据 else 0 end),
[5月]=sum(case when 日期字段 between '2011-05-01' and '2011-05-31' then 单据 else 0 end),
[6月]=sum(case when 日期字段 between '2011-06-01' and '2011-06-30' then 单据 else 0 end),
[7月]=sum(case when 日期字段 between '2011-07-01' and '2011-07-31' then 单据 else 0 end),
[8月]=sum(case when 日期字段 between '2011-08-01' and '2011-08-31' then 单据 else 0 end),
[9月]=sum(case when 日期字段 between '2011-09-01' and '2011-09-30' then 单据 else 0 end),
[10月]=sum(case when 日期字段 between '2011-10-01' and '2011-10-31' then 单据 else 0 end),
[11月]=sum(case when 日期字段 between '2011-11-01' and '2011-11-30' then 单据 else 0 end),
[12月]=sum(case when 日期字段 between '2011-12-01' and '2011-12-31' then 单据 else 0 end)
from tablename
select
[1月]=sum(case when month(date) = 1 then 1 else 0 end),
[2月]=sum(case when month(date) = 2 then 1 else 0 end),
[3月]=sum(case when month(date) = 3 then 1 else 0 end),
[4月]=sum(case when month(date) = 4 then 1 else 0 end),
[5月]=sum(case when month(date) = 5 then 1 else 0 end),
[6月]=sum(case when month(date) = 6 then 1 else 0 end),
[7月]=sum(case when month(date) = 7 then 1 else 0 end),
[8月]=sum(case when month(date) = 8 then 1 else 0 end),
[9月]=sum(case when month(date) = 9 then 1 else 0 end),
[10月]=sum(case when month(date) = 10 then 1 else 0 end),
[11月]=sum(case when month(date) = 11 then 1 else 0 end),
[12月]=sum(case when month(date) = 12 then 1 else 0 end)
from tablename
where date between '2010-01-01' and '2011-06-20'