a b
1 2011-07-19 17:21:32.327
2 2011-07-23 17:32:44.156
1 2011-09-24 17:44:22.247
2 2011-07-23 15:35:14.246
3 2011-08-12 14:56:25.256
1 2011-07-19 10:24:23.327先查询以b,a分组的数据,但是b是以日期为准,不用管时分秒
查询结果如下a b count
1 2011-07-19 2
1 2011-09-24 1
2 2011-07-23 2
3 2011-08-12 1
1 2011-07-19 17:21:32.327
2 2011-07-23 17:32:44.156
1 2011-09-24 17:44:22.247
2 2011-07-23 15:35:14.246
3 2011-08-12 14:56:25.256
1 2011-07-19 10:24:23.327先查询以b,a分组的数据,但是b是以日期为准,不用管时分秒
查询结果如下a b count
1 2011-07-19 2
1 2011-09-24 1
2 2011-07-23 2
3 2011-08-12 1
FROM TAB
GROUP BY A,CONVERT(VARCHAR(10),B,120)
group by id,convert(varchar(10),ddate,120)[code]
a,
convert(varchar(10),b,120) as b,
count(1) as [count]
from
tb
group by
a,
convert(varchar(10),b,120)
group by id,convert(varchar(10),ddate,120)
(
a int,
b datetime
)insert into tb values(1,'2011-07-19 17:21:32.327')
insert into tb values(2,'2011-07-23 17:32:44.156')
insert into tb values(1,'2011-09-24 17:44:22.247')
insert into tb values(2,'2011-07-23 15:35:14.246')
insert into tb values(3,'2011-08-12 14:56:25.256')
insert into tb values(1,'2011-07-19 10:24:23.327')select a,convert(varchar(10),b,120) as b ,count(*) as [count]
from tb
group by a,convert(varchar(10),b,120)
/*
a,b,count
1,2011-07-19,2
2,2011-07-23,2
3,2011-08-12,1
1,2011-09-24,1(4 行受影响)
insert into tb values(1 ,'2011-07-19 17:21:32.327')
insert into tb values(2 ,'2011-07-23 17:32:44.156')
insert into tb values(1 ,'2011-09-24 17:44:22.247')
insert into tb values(2 ,'2011-07-23 15:35:14.246')
insert into tb values(3 ,'2011-08-12 14:56:25.256')
insert into tb values(1 ,'2011-07-19 10:24:23.327')
goselect a ,convert(varchar(10),b,120) b ,[count] = count(1) from tb group by a ,convert(varchar(10),b,120) order by a , bdrop table tb/*
a b count
----------- ---------- -----------
1 2011-07-19 2
1 2011-09-24 1
2 2011-07-23 2
3 2011-08-12 1(所影响的行数为 4 行)
*/
count(*)主要针对的是行 ,count(字段) 主要针对的是你这一列
打个比方:你有一个表tb (id int, name varchar(20))
id ,name
1 sa
2 root
3 nullselect count(*) from tb --3
select count(1) from tb --3 效率要高些
select count(name) from tb --2