select name,convert(varchar(7),[date],120) as [date],count(*) xht_deliver_ycj group by name,convert(varchar(7),[date],120)
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( id int, name varchar(10), [date] datetime )insert into tb(id,name,[date]) values(1 ,'name1', '2006-1-1') insert into tb(id,name,[date]) values(2 ,'name1', '2006-1-11') insert into tb(id,name,[date]) values(3 ,'name2', '2006-2-5') insert into tb(id,name,[date]) values(4 ,'name2', '2006-3-4')select name , convert(varchar(7),date,120) as 年月 , count(*) as 数量 from tb group by name , convert(varchar(7),date,120)drop table tbname 年月 数量 ---------- ------- ----------- name1 2006-01 2 name2 2006-02 1 name2 2006-03 1(所影响的行数为 3 行)
select name,year(date) 年,month(date) 月,count(*) 记录数 from xht_deliver_ycj group by name,year(date),month(date)我发觉这样也可以,呵呵
xht_deliver_ycj
group by name,convert(varchar(7),[date],120)
drop table tb
gocreate table tb
(
id int,
name varchar(10),
[date] datetime
)insert into tb(id,name,[date]) values(1 ,'name1', '2006-1-1')
insert into tb(id,name,[date]) values(2 ,'name1', '2006-1-11')
insert into tb(id,name,[date]) values(3 ,'name2', '2006-2-5')
insert into tb(id,name,[date]) values(4 ,'name2', '2006-3-4')select name , convert(varchar(7),date,120) as 年月 , count(*) as 数量
from tb
group by name , convert(varchar(7),date,120)drop table tbname 年月 数量
---------- ------- -----------
name1 2006-01 2
name2 2006-02 1
name2 2006-03 1(所影响的行数为 3 行)
from xht_deliver_ycj
group by name,year(date),month(date)我发觉这样也可以,呵呵