表Table中函有字段name\statdate等字段,statdata是日期型的
要求统计一段时间内每个人的记录总数及所有记录数
如:表中字段记录name statdate flag
a 2007-11-01 1
b 2007-11-01 1
c 2007-11-01 1
a 2007-10-01 1
b 2007-10-01 1
b 2007-10-05 0
c 2007-11-09 0
a 2007-11-09 0
b 2007-11-09 1
a 2007-11-20 1
统计2007-11月的数据以后的显示如下:
name 当月记录条数 当月状态为1的 总共记录条数 状态为1的总计
a 3 2 4 3
b 2 2 4 3
c 2 1 2 1
统计 7 5 10 7请高人给出一条上面结果的SQL语句
要求统计一段时间内每个人的记录总数及所有记录数
如:表中字段记录name statdate flag
a 2007-11-01 1
b 2007-11-01 1
c 2007-11-01 1
a 2007-10-01 1
b 2007-10-01 1
b 2007-10-05 0
c 2007-11-09 0
a 2007-11-09 0
b 2007-11-09 1
a 2007-11-20 1
统计2007-11月的数据以后的显示如下:
name 当月记录条数 当月状态为1的 总共记录条数 状态为1的总计
a 3 2 4 3
b 2 2 4 3
c 2 1 2 1
统计 7 5 10 7请高人给出一条上面结果的SQL语句
sum(case convert(varchar(7),statdate,120) = '2007-11' then 1 end) as 当月记录条数,
sum(case convert(varchar(7),statdate,120) = '2007-11' and flag = 1 then 1 end) as 当月状态为1的,
count(1) as 总共记录条数, sum(case flag = 1 then 1 end) as 状态为1的总计
from tablename
group by name with rollup
insert into tb values('a','2007-11-01', 1 )
insert into tb values('b','2007-11-01', 1 )
insert into tb values('c','2007-11-01', 1 )
insert into tb values('a','2007-10-01', 1 )
insert into tb values('b','2007-10-01', 1 )
insert into tb values('b','2007-10-05', 0 )
insert into tb values('c','2007-11-09', 0 )
insert into tb values('a','2007-11-09', 0 )
insert into tb values('b','2007-11-09', 1 )
insert into tb values('a','2007-11-20', 1 )
goselect isnull(name,'统计') name,
当月记录条数 =sum(case when convert(varchar(7),statdate,120) = convert(varchar(7),getdate(),120) then 1 else 0 end),
当月状态为1的 =sum(case when convert(varchar(7),statdate,120) = convert(varchar(7),getdate(),120) and flag = 1 then 1 else 0 end),
总共记录条数 = count(*),
状态为1的总计 =sum(case when flag = 1 then 1 else 0 end)
from tb
group by name
with rollupdrop table tb
/*
name 当月记录条数 当月状态为1的 总共记录条数 状态为1的总计
---------- ----------- ----------- ----------- -----------
a 3 2 4 3
b 2 2 4 3
c 2 1 2 1
统计 7 5 10 7(所影响的行数为 4 行)
*/