＋设为首页 ＋添加到收藏夹
我举一个例子表,我只例出用到的字段
表名: Table1
id user_name status ctime
1 user_A 1 2005-9-2 19:23:32
2 user_B 1 2005-7-2 19:20:32
3 user_C 0 2005-8-2 3:32:32
4 user_B 0 2005-9-2 3:32:32
5 user_A 1 2005-6-2 19:23:32
6 user_A 0 2005-8-8 19:23:32
7 user_C 1 2005-4-2 19:23:32
8 user_A 0 2005-4-2 19:23:32
9 user_A 1 2005-8-2 2:32:32
10 user_A 1 2005-8-2 4:32:32
11 user_A 1 2005-8-3 5:32:22
11 user_A 0 2005-8-2 5:32:22
我现在这样：
给出时间T1:2005-08-01 T2: 2005-08-30
给出用户名: user_A
我现在要求出两个时间段内，用户user_A 的每天的 [总记录条数] [状态为0的条数] [状态为1的条数]
语句运行后，结果应该如下
日期 总记录条数 状态为1的条数 状态为0的条数
2005-8-2 3 2 1
2005-8-3 1 1 0
2005-8-8 1 0 1
用一条语句可以实现上面的结果吗？？
我自己做了一下，只能实现输出日期和总记录数，后面的状态我不会做,语句如下
SELECT CONVERT(char(10), ctime, 120) AS t, COUNT(*) AS cn
FROM Table1
WHERE (Ad_uid = user_A) AND (CONVERT(char(10), ctime, 120)
>= 2005-08-01) AND (CONVERT(char(10), ctime, 120) <= 2005-08-30)
GROUP BY CONVERT(char(10), ctime, 120)
如果还要求出状态数，怎么做呢？
create table table1
(
id int identity,
user_name varchar(10),
status int,
ctime datetime
)
insert into table1 values(user_A,1,2005-9-2 19:23:32)
insert into table1 values(user_B,1,2005-7-2 19:20:32)
insert into table1 values(user_C,0,2005-8-2 3:32:32)
insert into table1 values(user_B,0,2005-9-2 3:32:32)
insert into table1 values(user_A,1,2005-6-2 19:23:32)
insert into table1 values(user_A,0,2005-8-8 19:23:32)
insert into table1 values(user_C,1,2005-4-2 19:23:32)
insert into table1 values(user_A,0,2005-4-2 19:23:32)
insert into table1 values(user_A,1,2005-8-2 2:32:32)
insert into table1 values(user_A,1,2005-8-2 4:32:32)
insert into table1 values(user_A,1,2005-8-3 5:32:22)
insert into table1 values(user_A,0,2005-8-2 5:32:22)
select user_name,
count(user_name) as 总记录条数,
sum(case when status=1 then 1 else 0 end) as 状态为1的条数,
sum(case when status=0 then 1 else 0 end) as 状态为0的条数
from table1
where user_name=user_A and ctime>2005-08-01 and ctime<2005-08-30
group by user_name,convert(char(10), ctime, 120)
create table table1(
id int,
user_name varchar(20),
status bit,
ctime datetime)
insert into table1
select
1, user_A, 1, 2005-9-2 19:23:32
union select
2, user_B, 1 , 2005-7-2 19:20:32
union select
3, user_C, 0, 2005-8-2 3:32:32
union select
4, user_B, 0 , 2005-9-2 3:32:32
union select
5, user_A, 1 , 2005-6-2 19:23:32
union select
6, user_A, 0 , 2005-8-8 19:23:32
union select
7, user_C, 1 , 2005-4-2 19:23:32
union select
8, user_A, 0 , 2005-4-2 19:23:32
union select
9, user_A, 1 , 2005-8-2 2:32:32
union select
10, user_A, 1, 2005-8-2 4:32:32
union select
11, user_A, 1, 2005-8-3 5:32:22
union select
11, user_A, 0 , 2005-8-2 5:32:22
select t,sum(cn)cn,sum(st1)st1,sum(st0)st0 from(select t,case status when 0 then cn else 0 end st0,
case status when 1 then cn else 0 end st1,cn
from(SELECT CONVERT(char(10), ctime, 120) AS t, COUNT(*) AS cn,status
FROM Table1
WHERE (user_name = user_A) AND (CONVERT(char(10), ctime, 120)
>= 2005-08-01) AND (CONVERT(char(10), ctime, 120) <= 2005-08-30)
GROUP BY CONVERT(char(10), ctime, 120),status)kk)pp group by t
换种方式
如果求共有几条这样的记录呢？
比如上面3条，应该怎么写？？
－－比如得到什么结果呢？
