if not object_id('tb') is null
drop table tb
GoCreate table tb(id int,pid varchar(20),bid varchar(30))
insert into tbselect 1,'单位一','豫勘测评备字[2010]2号' union all
select 2,'单位二','豫勘测评备字[2010]12号' union all
select 3,'单位三','豫勘测评不备字[2010]3号' union all
select 4,'单位四','豫勘测评备字[2010]1号' union all
select 5,'单位五','豫勘测评不备字[2010]21号' union all
select 6,'单位六','豫勘测评备字[2010]4号' union allselect 7,'单位一','豫勘测评备字[2010]12号' union all
select 8,'单位二','豫勘测评备字[2010]63号' union all
select 9,'单位三','豫勘测评备字[2010]259号' union all
select 10,'单位四','豫勘测评不备字[2010]23号' union all
select 11,'单位五','豫勘测评不备字[2010]211号' union allselect 12,'单位一','豫勘测评备字[2011]1号' union all
select 13,'单位二','豫勘测评备字[2011]2号' union all
select 14,'单位三','豫勘测评备字[2011]3号' union all
select 15,'单位四','豫勘测评不备字[2011]1号' union allselect 16,'单位一','豫勘测评备字[2011]16号' union all
select 17,'单位二','豫勘测评不备字[2011]2号' union all
select 18,'单位三','豫勘测评备字[2011]29号' union all
select 19,'单位四','豫勘测评备字[2011]123号' union all
select 20,'单位五','豫勘测评不备字[2011]12号' union all
select 21,'单位六','豫勘测评备字[2011]235号' union all
select 22,'单位三','豫勘测评不备字[2011]36号' union all
select 23,'单位二','豫勘测评不备字[2011]121号' union all
select 24,'单位一','豫勘测评不备字[2011]222号' union all
select 25,'单位四','豫勘测评备字[2011]369号'go需要的结果是:各单位的 项目总数,通过备案的,未通过备案的,通过率是多少单位名称 总数 通过 未通过 通过率
-----------------------------------------------
单位一 5 4 1 80%
....
drop table tb
GoCreate table tb(id int,pid varchar(20),bid varchar(30))
insert into tbselect 1,'单位一','豫勘测评备字[2010]2号' union all
select 2,'单位二','豫勘测评备字[2010]12号' union all
select 3,'单位三','豫勘测评不备字[2010]3号' union all
select 4,'单位四','豫勘测评备字[2010]1号' union all
select 5,'单位五','豫勘测评不备字[2010]21号' union all
select 6,'单位六','豫勘测评备字[2010]4号' union allselect 7,'单位一','豫勘测评备字[2010]12号' union all
select 8,'单位二','豫勘测评备字[2010]63号' union all
select 9,'单位三','豫勘测评备字[2010]259号' union all
select 10,'单位四','豫勘测评不备字[2010]23号' union all
select 11,'单位五','豫勘测评不备字[2010]211号' union allselect 12,'单位一','豫勘测评备字[2011]1号' union all
select 13,'单位二','豫勘测评备字[2011]2号' union all
select 14,'单位三','豫勘测评备字[2011]3号' union all
select 15,'单位四','豫勘测评不备字[2011]1号' union allselect 16,'单位一','豫勘测评备字[2011]16号' union all
select 17,'单位二','豫勘测评不备字[2011]2号' union all
select 18,'单位三','豫勘测评备字[2011]29号' union all
select 19,'单位四','豫勘测评备字[2011]123号' union all
select 20,'单位五','豫勘测评不备字[2011]12号' union all
select 21,'单位六','豫勘测评备字[2011]235号' union all
select 22,'单位三','豫勘测评不备字[2011]36号' union all
select 23,'单位二','豫勘测评不备字[2011]121号' union all
select 24,'单位一','豫勘测评不备字[2011]222号' union all
select 25,'单位四','豫勘测评备字[2011]369号'go需要的结果是:各单位的 项目总数,通过备案的,未通过备案的,通过率是多少单位名称 总数 通过 未通过 通过率
-----------------------------------------------
单位一 5 4 1 80%
....
;with t as(
select pid,
case
when charindex('不',bid)<1 then 1 else 0 end as pass
from tb)
select pid,count(pass) as '总数',sum(pass) as '合格',
count(pass) - sum(pass) as '不合格',
sum(pass)*1.00/count(pass) as '合格率'
from t
group by pid
COUNT(*) as 项目总数,
SUM(case when bid not like '%不%' then 1 else 0 end)as 通过备案的,
SUM(case when bid not like '%不%' then 1 else 0 end)as 未通过备案的,
ltrim(cast(SUM(case when bid not like '%不%' then 1 else 0 end)*100.0/count(*) as decimal(6,2)))+'%' as 通过率
from tb
group by pid
pid 项目总数 通过备案的 未通过备案的 通过率
-------------------- ----------- ----------- ----------- ------------------------------------------
单位二 5 3 3 60.00%
单位六 2 2 2 100.00%
单位三 5 3 3 60.00%
单位四 5 3 3 60.00%
单位五 3 0 0 0.00%
单位一 5 4 4 80.00%(6 row(s) affected)
COUNT(*) as 项目总数,
SUM(case when bid not like '%不%' then 1 else 0 end)as 通过备案的,
SUM(case when bid like '%不%' then 1 else 0 end)as 未通过备案的,
ltrim(cast(SUM(case when bid not like '%不%' then 1 else 0 end)*100.0/count(*) as decimal(6,2)))+'%' as 通过率
from tb
group by pid pid 项目总数 通过备案的 未通过备案的 通过率
-------------------- ----------- ----------- ----------- ------------------------------------------
单位二 5 3 2 60.00%
单位六 2 2 0 100.00%
单位三 5 3 2 60.00%
单位四 5 3 2 60.00%
单位五 3 0 3 0.00%
单位一 5 4 1 80.00%(6 row(s) affected)2楼多了个not
select a.pid as 单位名称,
COUNT(a.pid)as 总数,
通过 =(select COUNT(1) from dingpin where a.pid =pid and charindex('不',bid)=0 ),
未通过 =(select COUNT(1) from dingpin where a.pid =pid and charindex('不',bid)>0 )
from dingpin a group by a.pid
Create table tb(id int,pid varchar(20),bid varchar(30))
insert into tbselect 1,'单位一','豫勘测评备字[2010]2号' union all
select 2,'单位二','豫勘测评备字[2010]12号' union all
select 3,'单位三','豫勘测评不备字[2010]3号' union all
select 4,'单位四','豫勘测评备字[2010]1号' union all
select 5,'单位五','豫勘测评不备字[2010]21号' union all
select 6,'单位六','豫勘测评备字[2010]4号' union allselect 7,'单位一','豫勘测评备字[2010]12号' union all
select 8,'单位二','豫勘测评备字[2010]63号' union all
select 9,'单位三','豫勘测评备字[2010]259号' union all
select 10,'单位四','豫勘测评不备字[2010]23号' union all
select 11,'单位五','豫勘测评不备字[2010]211号' union allselect 12,'单位一','豫勘测评备字[2011]1号' union all
select 13,'单位二','豫勘测评备字[2011]2号' union all
select 14,'单位三','豫勘测评备字[2011]3号' union all
select 15,'单位四','豫勘测评不备字[2011]1号' union allselect 16,'单位一','豫勘测评备字[2011]16号' union all
select 17,'单位二','豫勘测评不备字[2011]2号' union all
select 18,'单位三','豫勘测评备字[2011]29号' union all
select 19,'单位四','豫勘测评备字[2011]123号' union all
select 20,'单位五','豫勘测评不备字[2011]12号' union all
select 21,'单位六','豫勘测评备字[2011]235号' union all
select 22,'单位三','豫勘测评不备字[2011]36号' union all
select 23,'单位二','豫勘测评不备字[2011]121号' union all
select 24,'单位一','豫勘测评不备字[2011]222号' union all
select 25,'单位四','豫勘测评备字[2011]369号'
goselect pid,count(*)cn,
count(*) - sum(case when charindex('不',bid) > 0 then 1 else 0 end)num,
sum(case when charindex('不',bid) > 0 then 1 else 0 end)nonum,
ltrim(cast(100*(1-sum(case when charindex('不',bid) > 0 then 1 else 0 end)*1./count(*)) as decimal(18,2)))+'%' as aj
from tb
group by piddrop table tb
/*
pid cn num nonum aj
-------------------- ----------- ----------- ----------- ------------------------------------------
单位二 5 3 2 60.00%
单位六 2 2 0 100.00%
单位三 5 3 2 60.00%
单位四 5 3 2 60.00%
单位五 3 0 3 0.00%
单位一 5 4 1 80.00%(6 行受影响)