日期:2014-05-18 浏览次数:20489 次
select convert(varchar(10),addtime , 120) addtime, sum(case when len(username) > 1 then 1 else 0 end) type1, sum(case when len(username) = 1 then 1 else 0 end) type2 from tb group by convert(varchar(10),addtime , 120)
------解决方案--------------------
select convert(varchar(10),addtime , 120) addtime, count(case when len(username) > 1 then 1 end) type1, count(case when len(username) = 1 then 1 end) type2 from tb group by convert(varchar(10),addtime , 120)
------解决方案--------------------
create table tb(userid int, username varchar(10) , addtime datetime) insert into tb values(11 , 'abc', '2008-8-8 11:20:30') insert into tb values(12 , 'a' , '2008-8-8 11:21:30') insert into tb values(13 , 'a' , '2008-8-8 11:31:30') insert into tb values(14 , 'a' , '2008-8-9 11:50:30') go select convert(varchar(10),addtime , 120) addtime, sum(case when len(username) > 1 then 1 else 0 end) 'type1(长度大于1)', sum(case when len(username) = 1 then 1 else 0 end) 'type2(长度等于1)' from tb group by convert(varchar(10),addtime , 120) drop table tb /* addtime type1(长度大于1) type2(长度等于1) ---------- ------------ ------------ 2008-08-08 1 2 2008-08-09 0 1 (所影响的行数为 2 行) */