create table #(date varchar(10), tel varchar(20),jige int)insert into # select select '2010-05-01','11111',1 union all select '2010-05-01','11111',1 union all select '2010-05-01','11111',1 union all select '2010-05-01','11111',1 union all select '2010-05-01','11111',1 union all select '2010-05-01','122',1 union all select '2010-05-01','222',1 union all select '2010-05-01','233',1 union all select '2010-05-01','122',1 union all select '2010-05-01','122',1 with cte as (select tel,count(1) cout from # where date='2010-05-01'group by tel)select rtrim(cout) 短信条数,count(1) 用户数 ,rtrim(100.0*count(1)/(select count(1) from cte))+'%' 用户数在总数中的比例 from cte group by cout union all select '总数:',count(1),'0' from cte 短信条数 用户数 用户数在总数中的比例 ------------ ----------- ------------------------------------------ 1 2 50.000000000000% 3 1 25.000000000000% 5 1 25.000000000000% 总数: 4 0
select '2010-05-01','11111',1 union all
select '2010-05-01','11111',1 union all
select '2010-05-01','11111',1 union all
select '2010-05-01','11111',1 union all
select '2010-05-01','11111',1 union all
select '2010-05-01','122',1 union all
select '2010-05-01','222',1 union all
select '2010-05-01','233',1 union all
select '2010-05-01','122',1 union all
select '2010-05-01','122',1
with cte as
(select tel,count(1) cout from # where date='2010-05-01'group by tel)select rtrim(cout) 短信条数,count(1) 用户数 ,rtrim(100.0*count(1)/(select count(1) from cte))+'%' 用户数在总数中的比例
from cte group by cout
union all
select '总数:',count(1),'0' from cte
短信条数 用户数 用户数在总数中的比例
------------ ----------- ------------------------------------------
1 2 50.000000000000%
3 1 25.000000000000%
5 1 25.000000000000%
总数: 4 0