select count(bbb) bbb_cnt,
count(case when bbb=1 then 1 else null end) bbb_1_cnt,
count(case when bbb=0 then 1 else null end) bbb_0_cnt,
rtrim(count(case when bbb=1 then 1 else null end)/isnull(nullif(count(bbb)*100),0),1) + '%' bbb_1_percent
from tb
或
select count(bbb) bbb_cnt,
sum(case when bbb=1 then 1 else 0 end) bbb_1_cnt,
sum(case when bbb=0 then 1 else 0 end) bbb_0_cnt,
rtrim(sum(case when bbb=1 then 1 else 0 end)/isnull(nullif(count(bbb)*100),0),1) + '%' bbb_1_percent
from tb
count(case when bbb=1 then 1 else null end) bbb_1_cnt,
count(case when bbb=0 then 1 else null end) bbb_0_cnt,
rtrim(count(case when bbb=1 then 1 else null end)/isnull(nullif(count(bbb)*100),0),1) + '%' bbb_1_percent
from tb
或
select count(bbb) bbb_cnt,
sum(case when bbb=1 then 1 else 0 end) bbb_1_cnt,
sum(case when bbb=0 then 1 else 0 end) bbb_0_cnt,
rtrim(sum(case when bbb=1 then 1 else 0 end)/isnull(nullif(count(bbb)*100),0),1) + '%' bbb_1_percent
from tb
insert into aaa values(0)
insert into aaa values(0)
insert into aaa values(0)
insert into aaa values(1)
insert into aaa values(1)
insert into aaa values(0)
goselect bbb等于1的总数 = count(*) , bbb等于1所占的比例 = cast(count(*)*1.0/(select count(*) from aaa) as decimal(18,2)) from aaa where bbb = 1drop table aaa/*
bbb等于1的总数 bbb等于1所占的比例
----------- --------------------
2 .33(所影响的行数为 1 行)
*/
或
所有bbb均为null的情况需要考滤
insert into aaa values(0)
insert into aaa values(0)
insert into aaa values(0)
insert into aaa values(1)
insert into aaa values(1)
insert into aaa values(0)
goselect bbb等于1的总数 = count(*) , bbb等于1所占的比例 = cast(cast(count(*)*100.00/(select count(*) from aaa) as decimal(18,2)) as varchar) + '%' from aaa where bbb = 1drop table aaa/*
bbb等于1的总数 bbb等于1所占的比例
------------- -------------------------------
2 33.33%(所影响的行数为 1 行)*/
insert into aaa values(0)
insert into aaa values(0)
insert into aaa values(0)
insert into aaa values(1)
insert into aaa values(1)
insert into aaa values(0)
goselect bbb的总数 = (select count(*) from aaa),
bbb等于1的总数 = count(*) ,
bbb等于1所占的比例 = cast(cast(count(*)*100.00/(select count(*) from aaa) as decimal(18,2)) as varchar) + '%'
from aaa where bbb = 1drop table aaa/*
bbb的总数 bbb等于1的总数 bbb等于1所占的比例
----------- ----------- -------------------------------
6 2 33.33%(所影响的行数为 1 行)*/
create table aaa( bbb int)
insert into aaa values(0)
insert into aaa values(0)
insert into aaa values(0)
insert into aaa values(1)
insert into aaa values(1)
insert into aaa values(0)
goselect bbb的总数 = (select count(*) from aaa where bbb is not null),
bbb等于1的总数 = count(*) ,
bbb等于1所占的比例 = cast(cast(count(*)*100.00/(select count(*) from aaa where bbb is not null) as decimal(18,2)) as varchar) + '%'
from aaa where bbb = 1 and bbb is not nulldrop table aaa/*
bbb的总数 bbb等于1的总数 bbb等于1所占的比例
----------- ----------- -------------------------------
6 2 33.33%(所影响的行数为 1 行)*/
sum(case bbb when 1 then 1 else 0 end)*1.0/count(*)比例 from sss
insert into aaa values(0)
insert into aaa values(0)
insert into aaa values(0)
insert into aaa values(1)
insert into aaa values(1)
insert into aaa values(0)
goselect count(bbb) bbb_cnt,
sum(case when bbb=0 then 1 else null end) bbb_0_cnt,
sum(isnull(bbb,0)) bbb_1_cnt,
rtrim(cast(sum(isnull(bbb,0))* 100.0/count(*) as numeric(12,2)) ) + '%' bbb_1_percent
from aaa
/*
bbb_cnt bbb_0_cnt bbb_1_cnt bbb_1_percent
----------- ----------- ----------- -----------------------------------------
6 4 2 33.33%(所影响的行数为 1 行)警告: 聚合或其它 SET 操作消除了空值。
*/drop table aaa
另外如果可能的话我想追问一下:如果我还要同时取到另一个表的数据如
表ccc
number cc
1 s
1 a
2 d
3 y
在
表aaa
id bbb
1 0
2 0
3 1
的情况下,如果只对表aaa进行count操作,当然结果是正确的。但是如果我还要连接表ccc来取到其cc字段,这个时候就
出问题了,在这里的话,总数的count显然会是4,而原来应该是3。我用了两个嵌套的select解决问题:select a.cc,a.总数,b.等于1的数 from
(select aaa.id,ccc.cc,总数=count(*)
from aaa left join ccc on aaa.id=ccc.number
group by aaa.id,ccc.cc) as a
left join
(select 等于1的数=count(*),aaa.id
from aaa
where aaa.bbb=1 group by aaa.id) as b
on a.id=b.id但是我始终觉得这么做不太好,嵌了两个select!有谁有好办法吗??
我会加分的
你如id=1的在ccc表中对应俩个cc。该取哪个?
在ccc表number不是主键