create table z1
(
id int,
content varchar(20),
accepttime datetime,
)insert into z1
values(1,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'b.gif','2009-07-10 11:00:0')
insert into z1
values(3,'c.gif','2009-06-10 11:00:0')
insert into z1
values(3,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'a.gif','2009-06-10 11:00:0')select * from z1
两个问题,麻烦解答:
1.求每个月的纪录总数?
结果应显示如下:
month cnt
6 2
7 1
8 2
2.求每个月的每个id的纪录总数?
(
id int,
content varchar(20),
accepttime datetime,
)insert into z1
values(1,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'b.gif','2009-07-10 11:00:0')
insert into z1
values(3,'c.gif','2009-06-10 11:00:0')
insert into z1
values(3,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'a.gif','2009-06-10 11:00:0')select * from z1
两个问题,麻烦解答:
1.求每个月的纪录总数?
结果应显示如下:
month cnt
6 2
7 1
8 2
2.求每个月的每个id的纪录总数?
from z1 group by month(accepttime)
select
month(accepttime) as [month],
count(1) as cnt
from
tb
group by
month(accepttime)
group by convert(varchar(7),accepttime,120)select convert(varchar(7),accepttime,120),id ,count(*) from z1
group by convert(varchar(7),accepttime,120),id
(
id int,
content varchar(20),
accepttime datetime,
) insert into z1
values(1,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'b.gif','2009-07-10 11:00:0')
insert into z1
values(3,'c.gif','2009-06-10 11:00:0')
insert into z1
values(3,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'a.gif','2009-06-10 11:00:0')
select month(accepttime)[month],
count(*)cnt
from z1 group by month(accepttime)
drop table z1
/*
month cnt
----------- -----------
6 2
7 1
8 2(3 個資料列受到影響)*/
select
id,
convert(varchar(7),accepttime,120) as [month],
count(1) as cnt
from
tb
group by
id,
convert(varchar(7),accepttime,120)
(
id int,
content varchar(20),
accepttime datetime,
) insert into z1
values(1,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'b.gif','2009-07-10 11:00:0')
insert into z1
values(3,'c.gif','2009-06-10 11:00:0')
insert into z1
values(3,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'a.gif','2009-06-10 11:00:0') --select * from z1select datepart(mm,accepttime) as [month],count(1) as cnt from Z1 group by datepart(mm,accepttime)
drop table Z1
/*month cnt
----------- -----------
6 2
7 1
8 2(3 行受影响)*/
(
id int,
content varchar(20),
accepttime datetime,
) insert into z1
values(1,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'b.gif','2009-07-10 11:00:0')
insert into z1
values(3,'c.gif','2009-06-10 11:00:0')
insert into z1
values(3,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'a.gif','2009-06-10 11:00:0') select
id,
CONVERT(varchar(10),accepttime,120) as 月份,
counts=COUNT(*)
from z1
group by id,CONVERT(varchar(10),accepttime,120)id 月份 counts
----------- ---------- -----------
2 2009-06-10 1
3 2009-06-10 1
2 2009-07-10 1
1 2009-08-10 1
3 2009-08-10 1
select month(accepttime) month,count(*) cnt from z1 group by month(accepttime)
/*
month cnt
6 2
7 1
8 2
*/
--查询2
select month(accepttime) month,id,count(*) cnt from z1 group by month(accepttime),id
/*
month id cnt
8 1 1
6 2 1
7 2 1
6 3 1
8 3 1
*/
(
id int,
content varchar(20),
accepttime datetime,
) insert into z1
values(1,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'b.gif','2009-07-10 11:00:0')
insert into z1
values(3,'c.gif','2009-06-10 11:00:0')
insert into z1
values(3,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'a.gif','2009-06-10 11:00:0') --select * from z1
select
id,
convert(varchar(7),accepttime,120) as [month],
count(1) as cnt
from
Z1
group by
id,
convert(varchar(7),accepttime,120)
drop table Z1
/*id month cnt
----------- ------- -----------
2 2009-06 1
3 2009-06 1
2 2009-07 1
1 2009-08 1
3 2009-08 1(5 行受影响)*/
if object_id('z1')is not null drop table z1
go
create table z1
(
id int,
content varchar(20),
accepttime datetime,
) insert into z1
values(1,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'b.gif','2009-07-10 11:00:0')
insert into z1
values(3,'c.gif','2009-06-10 11:00:0')
insert into z1
values(3,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'a.gif','2009-06-10 11:00:0') select month(accepttime)as 月份, count(*)as 记录数 from z1
group by month(accepttime)
/*
月份 记录数
----------- -----------
6 2
7 1
8 2(3 行受影响)
*/select month(accepttime)as 月份,id, count(*)as 记录数 from z1
group by month(accepttime),id
order by month(accepttime),id
/*
月份 id 记录数
----------- ----------- -----------
6 2 1
6 3 1
7 2 1
8 1 1
8 3 1(5 行受影响)
*/
create table z1
(
id int,
content varchar(20),
accepttime datetime,
) insert into z1
values(1,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'b.gif','2009-07-10 11:00:0')
insert into z1
values(3,'c.gif','2009-06-10 11:00:0')
insert into z1
values(3,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'a.gif','2009-06-10 11:00:0') select
u.id,
CONVERT(varchar(7),k.p,120) as 月份,
个数=(select COUNT(*) from z1 where id=u.id and CONVERT(varchar(7),k.p,120)=CONVERT(varchar(7),accepttime,120))
from (select DATEadd(MONTH ,number-1,'2009-01-10 11:00:0') as p
from (select distinct number from master..spt_values where number between 1 and 12) p) k
left join z1 on CONVERT(varchar(7),k.p,120)=CONVERT(varchar(7),accepttime,120) ,
(select distinct id from z1) u
group by u.id,CONVERT(varchar(7),k.p,120)
order by CONVERT(varchar(7),k.p,120)
/*
id 月份 个数
----------- ------- -----------
1 2009-01 0
2 2009-01 0
3 2009-01 0
1 2009-02 0
2 2009-02 0
3 2009-02 0
1 2009-03 0
2 2009-03 0
3 2009-03 0
1 2009-04 0
2 2009-04 0
3 2009-04 0
1 2009-05 0
2 2009-05 0
3 2009-05 0
1 2009-06 0
2 2009-06 1
3 2009-06 1
1 2009-07 0
2 2009-07 1
3 2009-07 0
1 2009-08 1
2 2009-08 0
3 2009-08 1
1 2009-09 0
2 2009-09 0
3 2009-09 0
1 2009-10 0
2 2009-10 0
3 2009-10 0
1 2009-11 0
2 2009-11 0
3 2009-11 0
1 2009-12 0
2 2009-12 0
3 2009-12 0(36 行受影响)
*/
--1
select month(accepttime) [month],count(*) [count] from aaa group by month(accepttime)
--2
select id,count(*) from aaa group by id
--1
select month(accepttime) as [month], count(*) as cnt from tbl
group by month(accepttime)--2
select id , month(accepttime) as [month], count(*) as cnt from tbl
group by id, month(accepttime)
from z1
group by month(accepttime)
select id,month(accepttime) [month],count(*) cnt
from z1
group by id,month(accepttime)
(
id int,
content varchar(20),
accepttime datetime,
) insert into z1
values(1,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'b.gif','2009-07-10 11:00:0')
insert into z1
values(3,'c.gif','2009-06-10 11:00:0')
insert into z1
values(3,'a.gif','2009-08-10 11:00:0')
insert into z1
values(2,'a.gif','2009-06-10 11:00:0') select * from z1select count(id) as aa, datepart(month,accepttime) as bb from z1 group by (datepart(month,accepttime))
测试结果:
2 6
1 7
2 8
month(accepttime) as [month],
count(1) as cnt
from
tb
group by
month(accepttime)
select MONTH(CONVERT(varchar(10), accepttime,120)) ,count(*) as [count] from z1
group by CONVERT(varchar(10), accepttime,120)--第二个
select MONTH(CONVERT(varchar(10), accepttime,120)) ,count( distinct id) as [count] from z1
group by CONVERT(varchar(10), accepttime,120)
month(accepttime) as [month],
count(1) as cnt
from
tb
group by
month(accepttime)