if not exists(select * from TEST.sys.objects where type=N'U' and name=N'article')
begin
create table article(
[id] int,
[date] date)
end
使用这些数据:
insert into article(id,date) values(1,convert(date,'15/01/2015',103));
insert into article(id,date) values(1,convert(date,'15/02/2015',103));
insert into article(id,date) values(2,convert(date,'15/03/2015',103));
insert into article(id,date) values(2,convert(date,'15/01/2015',103));
insert into article(id,date) values(3,convert(date,'15/02/2015',103));
insert into article(id,date) values(4,convert(date,'15/03/2015',103));
insert into article(id,date) values(5,convert(date,'15/01/2015',103));
insert into article(id,date) values(5,convert(date,'15/02/2015',103));
insert into article(id,date) values(1,convert(date,'15/03/2015',103));
insert into article(id,date) values(2,convert(date,'15/01/2015',103));
insert into article(id,date) values(3,convert(date,'15/02/2015',103));
insert into article(id,date) values(4,convert(date,'15/03/2015',103));
insert into article(id,date) values(5,convert(date,'15/01/2015',103));
insert into article(id,date) values(1,convert(date,'15/02/2015',103));
insert into article(id,date) values(2,convert(date,'15/03/2015',103));
insert into article(id,date) values(3,convert(date,'15/01/2015',103));
insert into article(id,date) values(4,convert(date,'15/03/2015',103));
select id,[date], count(id) [count] from article
group by [date],[id]
the result:
id date count
1 2015-01-15 1
1 2015-02-15 2
1 2015-03-15 1
2 2015-01-15 2
2 2015-03-15 2
3 2015-01-15 1
3 2015-02-15 2
4 2015-03-15 3
5 2015-01-15 2
5 2015-02-15 1