产品ID 产品名 存放日期
1112 3333 2009-3-3 22:33:33
1113 4444 2009-4-3 22:33:33
1114 5555 2009-4-3 22:33:33例如3月有一条记录,4月有两条....如何统计一年内每月的总数。
1112 3333 2009-3-3 22:33:33
1113 4444 2009-4-3 22:33:33
1114 5555 2009-4-3 22:33:33例如3月有一条记录,4月有两条....如何统计一年内每月的总数。
from table1
group by year(存放日期)*100+month(存放日期)
insert into tb values('1112', '3333' , '2009-3-3 22:33:33')
insert into tb values('1113', '4444' , '2009-4-3 22:33:33')
insert into tb values('1114', '5555' , '2009-4-3 22:33:33')
goselect convert(varchar(7),存放日期,120) yf , count(1) cnt from tb group by convert(varchar(7),存放日期,120)
drop table tb/*
yf cnt
------- -----------
2009-03 1
2009-04 2(所影响的行数为 2 行)
*/
2> go
产品ID |产品名 |存放日期
----------|----------|-----------------------
1112 |3333 |2009-03-03 22:33:33.000
1113 |4444 |2009-04-03 22:33:33.000
1114 |5555 |2009-04-03 22:33:33.000(3 rows affected)
1> select year(存放日期)*100+month(存放日期),count(*)
2> from table1
3> group by year(存放日期)*100+month(存放日期)
4> go
|
-----------|-----------
200903| 1
200904| 2(2 rows affected)
1>
count(1) as 总数
from [tb]
group by convert(varchar(10),存放日期,120)
create table tb(产品ID varchar(10), 产品名 varchar(10), 存放日期 datetime)
insert into tb values('1112', '3333' , '2009-3-3 22:33:33')
insert into tb values('1113', '4444' , '2009-4-3 22:33:33')
insert into tb values('1114', '5555' , '2009-4-3 22:33:33')
goselect convert(varchar(7),存放日期,120) yf , count(1) cnt from tb group by convert(varchar(7),存放日期,120)
/*
yf cnt
------- -----------
2009-03 1
2009-04 2(所影响的行数为 2 行)
*/
select convert(varchar(6),存放日期,112) yf , count(1) cnt from tb group by convert(varchar(6),存放日期,112)
/*
yf cnt
------ -----------
200903 1
200904 2(所影响的行数为 2 行)
*/drop table tb
select convert(varchar(10),存放日期,120) as 月份,
count(1) as 总数
from [tb]
group by convert(varchar(6),存放日期,112)