求一存储过程---合格率的计算(希望'子陌红尘'再次光临)

Question

表1(Categorise)
CID   Descript
1     人为原因
2     非人为原因
表2:(Qreport)
declare @t table(ID int,WDate datetime,Dname varchar(10),MID int,Deduct int,CID int)
insert into @t select 1,'2005-9-10','一车间',5223,5 ,4
insert into @t select 2,'2005-9-10','一车间',5223,5 ,2
insert into @t select 3,'2005-9-10','一车间',5224,10,4
insert into @t select 4,'2005-9-14','一车间',6221,5 ,4
insert into @t select 5,'2005-9-14','一车间',6222,10,4
insert into @t select 6,'2005-9-20','一车间',1236,10,2
insert into @t select 7,'2005-9-20','一车间',1236,5 ,2
insert into @t select 8,'2005-10-20','二车间',3062187,2 ,4
insert into @t select 9,'2005-10-20','二车间',3062187,3 ,4
insert into @t select 10,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 11,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 12,'2005-10-20','二车间',3062187,2 ,4
insert into @t select 13,'2005-10-20','二车间',3062187,1 ,4
insert into @t select 14,'2005-10-20','二车间',3062187,1 ,4
insert into @t select 15,'2005-10-20','二车间',3062187,1 ,1
insert into @t select 16,'2005-10-20','二车间',3062187,1 ,4
insert into @t select 17,'2005-10-20','二车间',3062187,2 ,4
insert into @t select 18,'2005-10-20','二车间',3062187,1 ,3
insert into @t select 19,'2005-10-20','二车间',3062187,2 ,4
......(省略更多的月份和车间)得到的结果如下:
车间名     年份   月份    总数量     不合格数量    合格率
一车间     2005   9       5          2             60%
二车间     2005   10      1          1             0%
...
说明:
1.为什么数量是5台就不用说了,大家看一下就知道了
2.不合格数量为2台,MID(产品号)的CID为1(人为原因)的扣分>=10为不合格,1236的扣分虽然>=10但CID为2,不在统计范围之内
3.合格率的公式:(总数量-不合格数量)/总数量*100%
4.说白了,就是统计"人为原因"造成的质量问题的合格率,非人为原因的因素不在统计范围之内

Answer 1

下面是'子陌红尘'的答案,但结果不对
declare @t table(ID int,WDate datetime,Dname varchar(10),MID int,Deduct int,CID int)
insert into @t select 1,'2005-9-10','一车间',5223,5 ,4
insert into @t select 2,'2005-9-10','一车间',5223,5 ,2
insert into @t select 3,'2005-9-10','一车间',5224,10,4
insert into @t select 4,'2005-9-14','一车间',6221,5 ,4
insert into @t select 5,'2005-9-14','一车间',6222,10,4
insert into @t select 6,'2005-9-20','一车间',1236,10,2
insert into @t select 7,'2005-9-20','一车间',1236,5 ,2
insert into @t select 8,'2005-10-20','二车间',3062187,2 ,4
insert into @t select 9,'2005-10-20','二车间',3062187,3 ,4
insert into @t select 10,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 11,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 12,'2005-10-20','二车间',3062187,2 ,4
insert into @t select 13,'2005-10-20','二车间',3062187,1 ,4
insert into @t select 14,'2005-10-20','二车间',3062187,1 ,4
insert into @t select 15,'2005-10-20','二车间',3062187,1 ,1
insert into @t select 16,'2005-10-20','二车间',3062187,1 ,4
insert into @t select 17,'2005-10-20','二车间',3062187,2 ,4
insert into @t select 18,'2005-10-20','二车间',3062187,1 ,3
insert into @t select 19,'2005-10-20','二车间',3062187,2 ,4select
    车间名,
    年份,
    月份,
    count(distinct 产品号) as 总数量,
    count(distinct 不合格产品号) as 不合格数量,
    rtrim((count(distinct 产品号)-count(distinct 不合格产品号))*100/count(distinct 产品号))+'%' as 合格率
from
    (select
         a.Dname as 车间名,
         year(a.WDate) as 年份,
         month(a.WDate) as 月份,
         a.MID as 产品号,
         (case when exists(select 1 from @t where MID=a.MID and CID=4 and Deduct>=10) then MID end) as 不合格产品号
     from 
         @t a) b
group by
    车间名,年份,月份结果如下
车间名        年份          月份          总数量         不合格数量       合格率           
---------- ----------- ----------- ----------- ----------- ------------- 
二车间        2005        10          1           0           100%          
一车间        2005        9           5           2           60%   二车间的3062187(MID)的人为原因问题的扣分相加已经>=10了,已经不合格了,怎么会是0呢

Answer 2

insert into @t select 10,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 11,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 15,'2005-10-20','二车间',3062187,1 ,1
这三条相加才5而已吧?
不超过10啊?那应该就是合格才对啊?

Answer 3

对不起,数据有误,应该是:
insert into @t select 1,'2005-9-10','一车间',5223,5 ,1
insert into @t select 2,'2005-9-10','一车间',5223,5 ,2
insert into @t select 3,'2005-9-10','一车间',5224,10,1
insert into @t select 4,'2005-9-14','一车间',6221,5 ,1
insert into @t select 5,'2005-9-14','一车间',6222,10,1
insert into @t select 6,'2005-9-20','一车间',1236,10,2
insert into @t select 7,'2005-9-20','一车间',1236,5 ,2
insert into @t select 8,'2005-10-20','北线',3062187,2 ,1
insert into @t select 9,'2005-10-20','北线',3062187,3 ,1
insert into @t select 10,'2005-10-20','北线',3062187,2 ,2
insert into @t select 11,'2005-10-20','北线',3062187,2 ,2
insert into @t select 12,'2005-10-20','北线',3062187,2 ,1
insert into @t select 13,'2005-10-20','北线',3062187,1 ,1
insert into @t select 14,'2005-10-20','北线',3062187,1 ,1
insert into @t select 15,'2005-10-20','北线',3062187,1 ,2
insert into @t select 16,'2005-10-20','北线',3062187,1 ,1
insert into @t select 17,'2005-10-20','北线',3062187,2 ,1
insert into @t select 18,'2005-10-20','北线',3062187,1 ,2
insert into @t select 19,'2005-10-20','北线',3062187,2 ,1

Answer 4

车间名错了,应该是:insert into @t select 1,'2005-9-10','一车间',5223,5 ,1
insert into @t select 2,'2005-9-10','一车间',5223,5 ,2
insert into @t select 3,'2005-9-10','一车间',5224,10,1
insert into @t select 4,'2005-9-14','一车间',6221,5 ,1
insert into @t select 5,'2005-9-14','一车间',6222,10,1
insert into @t select 6,'2005-9-20','一车间',1236,10,2
insert into @t select 7,'2005-9-20','一车间',1236,5 ,2
insert into @t select 8,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 9,'2005-10-20','二车间',3062187,3 ,1
insert into @t select 10,'2005-10-20','二车间',3062187,2 ,2
insert into @t select 11,'2005-10-20','二车间',3062187,2 ,2
insert into @t select 12,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 13,'2005-10-20','二车间',3062187,1 ,1
insert into @t select 14,'2005-10-20','二车间',3062187,1 ,1
insert into @t select 15,'2005-10-20','二车间',3062187,1 ,2
insert into @t select 16,'2005-10-20','二车间',3062187,1 ,1
insert into @t select 17,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 18,'2005-10-20','二车间',3062187,1 ,2
insert into @t select 19,'2005-10-20','二车间',3062187,2 ,1

Answer 5

declare @t table (ID int,WDate datetime,Dname varchar(10),MID int,Deduct int,CID int)
insert into @t select 1,'2005-9-10','一车间',5223,5 ,1
insert into @t select 2,'2005-9-10','一车间',5223,5 ,2
insert into @t select 3,'2005-9-10','一车间',5224,10,1
insert into @t select 4,'2005-9-14','一车间',6221,5 ,1
insert into @t select 5,'2005-9-14','一车间',6222,10,1
insert into @t select 6,'2005-9-20','一车间',1236,10,2
insert into @t select 7,'2005-9-20','一车间',1236,5 ,2
insert into @t select 8,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 9,'2005-10-20','二车间',3062187,3 ,1
insert into @t select 10,'2005-10-20','二车间',3062187,2 ,2
insert into @t select 11,'2005-10-20','二车间',3062187,2 ,2
insert into @t select 12,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 13,'2005-10-20','二车间',3062187,1 ,1
insert into @t select 14,'2005-10-20','二车间',3062187,1 ,1
insert into @t select 15,'2005-10-20','二车间',3062187,1 ,2
insert into @t select 16,'2005-10-20','二车间',3062187,1 ,1
insert into @t select 17,'2005-10-20','二车间',3062187,2 ,1
insert into @t select 18,'2005-10-20','二车间',3062187,1 ,2
insert into @t select 19,'2005-10-20','二车间',3062187,2 ,1
 
select  车间名,年,月,不合格数量,CC.总数量,((CC.总数量-不合格数量)*1.0)/CC.总数量 AS 合格率 from 
(select tt.Dname 车间名,年,月,BBB.不合格数量
from (select Dname,year(a.wdate)as 年,month(a.wdate)月 from @t a group by Dname,year(a.wdate),month(a.wdate))tt  left join
(select count(*)不合格数量,Dname from( select sum(Deduct)总分,MID,Dname from @t where CID=1 group by MID,Dname)BB where BB.总分>=10 group by Dname)BBB on tt.Dname=BBB.Dname)BT ,
(select count(distinct MID) as 总数量 from @t )CC   那你在试试看对不对!!!结果如下
一车间 2005 9 2 6 .666666666666
二车间 2005 10 1 6 .833333333333

Answer 6

楼上的,你好,首先谢谢你的帮助
你的结果是
车间名     年        月        不合格数量       总数量         合格率              
---------- ----------- ----------- ----------- ----------- ---------------- 
二车间      2005      10          1           6           .833333333333    
一车间      2005      9           2           6           .666666666666    
结果不对,为什么
第一,二车间10月份总数量才1个（就是MID号为3062187的),为什么跑出来6个呢
第二,一车间9月份总数量共是5个,(MID分别是5223/5224/6221/6222/1236),为什么也多出一个呢
我要的结果是:车间名     年份   月份    总数量     不合格数量    合格率
一车间     2005   9       5          2             60%
二车间     2005   10      1          1             0%因为二车间10月份的号码为3062187的产品的人为问题造成的扣分为
2+3+2+1+1+1+2+2=14,已经大于等于10分,所以判定为不合格,
二车间10月份总数量共1个,它又是不合格的,所以合格率为0

Answer 7

我要的是某车间某月份CID为1(即人为原因造成的质量问题)的产品号(该产品的扣分可能全是由人为原因造成的,也可能不是人为原因造成的,也可能既有人为原因又有非人为原因,但非人为原因不计算在内)扣分累加值>=10的数量,这个数量就是不合格数,
然后用当月总数量减去不合格数再除以当月总数量,最后乘以100%,即为该车间该月份的合格率,我的描述是不是太啰嗦了,但我想我已经描述清楚了

Answer 8

对不起,昨晚没看清楚,错漏了一个环节!!应该把昨天的CC在改一下,如下:select  BT.车间名,BT.年,BT.月,不合格数量,CC.总数量,((CC.总数量-不合格数量)*1.0)/CC.总数量 AS 合格率 from 
(select tt.Dname 车间名,年,月,BBB.不合格数量
from (select Dname,year(a.wdate)as 年,month(a.wdate)月 from @t a group by Dname,year(a.wdate),month(a.wdate))tt  left join(select count(*)不合格数量,Dname from( select sum(Deduct)总分,MID,Dname from @t where CID=1 group by MID,Dname)BB where BB.总分>=10 group by Dname)BBB on tt.Dname=BBB.Dname)BT left join
 
(select count(distinct MID)as 总数量,Dname,year(a.wdate)年,month(a.wdate)月 from @t as a group by Dname,year(a.wdate),month(a.wdate))CC on CC.Dname=BT.车间名结果为一车间 2005 9 2 5 .600000000000
二车间 2005 10 1 1 .000000000000呵呵麻烦楼主在看看对不对!!!

Answer 9

不对呀,下面是实际运行结果警告: 聚合或其它 SET 操作消除了空值。
车间名    年           月           不合格数量       总数量         合格率              
------ ----------- ----------- ----------- ----------- ---------------- 
一车间      2006        2           <NULL>      1           <NULL>           
一车间      2006        2           <NULL>      5           <NULL>           
一车间      2006        3           <NULL>      1           <NULL>           
一车间      2006        3           <NULL>      5           <NULL>           
二车间      2006        3           <NULL>      2           <NULL>           
三车间      2006        2           <NULL>      6           <NULL>           
三车间      2006        2           <NULL>      5           <NULL>           
三车间      2006        3           <NULL>      6           <NULL>           
三车间      2006        3           <NULL>      5           <NULL>           
          
没有行受影响。
(返回 38 行)

Answer 10

to yuweiwei(YWW(杨思)) 
你的存储过程我正在测试,刚才看错了,呵呵...

Answer 11

declare @Qreport table
(
ID int,
WDate datetime,
Dname varchar(10),
MID int,
Deduct int,
CID int
)
insert into @Qreport select 1,'2005-9-10','一車間',5223,5 ,4
insert into @Qreport select 2,'2005-9-10','一車間',5223,5 ,2
insert into @Qreport select 3,'2005-9-10','一車間',5224,10,4
insert into @Qreport select 4,'2005-9-14','一車間',6221,5 ,4
insert into @Qreport select 5,'2005-9-14','一車間',6222,10,4
insert into @Qreport select 6,'2005-9-20','一車間',1236,10,2
insert into @Qreport select 7,'2005-9-20','一車間',1236,5 ,2
insert into @Qreport select 8,'2005-10-20','二車間',3062187,2 ,4
insert into @Qreport select 9,'2005-10-20','二車間',3062187,3 ,4
insert into @Qreport select 10,'2005-10-20','二車間',3062187,2 ,1
insert into @Qreport select 11,'2005-10-20','二車間',3062187,2 ,1
insert into @Qreport select 12,'2005-10-20','二車間',3062187,2 ,4
insert into @Qreport select 13,'2005-10-20','二車間',3062187,1 ,4
insert into @Qreport select 14,'2005-10-20','二車間',3062187,1 ,4
insert into @Qreport select 15,'2005-10-20','二車間',3062187,1 ,1
insert into @Qreport select 16,'2005-10-20','二車間',3062187,1 ,4
insert into @Qreport select 17,'2005-10-20','二車間',3062187,2 ,4
insert into @Qreport select 18,'2005-10-20','二車間',3062187,1 ,3
insert into @Qreport select 19,'2005-10-20','二車間',3062187,2 ,4select 
Dname,
YY,
MM,
sum(Sumof) as Sumof,
sum(Bad) as Bad,
convert(char(5),convert( real,(sum(Sumof)-sum(Bad)) )/sum(Sumof)*100)+'%'  as Pct
from
(
select 
distinct *
from
(
select 
MID 
,Dname
,datepart(yy,WDate) as YY
,datepart(mm,WDate) as MM
,(select count(distinct MID) 
from @Qreport as B
where convert(varchar(7),A.WDate,111)=convert(varchar(7),B.WDate,111)
and A.Dname=B.Dname 
and A.MID=B.MID
) as Sumof
,isnull((select count(distinct MID)
from @Qreport as C
where convert(varchar(7),A.WDate,111)=convert(varchar(7),C.WDate,111)
and A.Dname=C.Dname 
and A.MID=C.MID
and C.CID<>2 
group by MID
having sum(Deduct)>=10
),0) as Bad
from @Qreport as A
) as tp
) as tp2
group by Dname,YY,MM以下为程序执行结果：Dname      YY          MM          Sumof       Bad         Pct    
---------- ----------- ----------- ----------- ----------- ------ 
一車間        2005        9           5           2           60   %
二車間        2005        10          1           1           0    %
由于时间仓猝，来不及优化了。以后有更好的再来写吧 ：）

Answer 12

declare @t table(ID int,WDate datetime,Dname varchar(10),MID int,Deduct int,CID int)
insert into @t select 1,'2005-9-10','一車間',5223,5 ,1
insert into @t select 2,'2005-9-10','一車間',5223,5 ,2
insert into @t select 3,'2005-9-10','一車間',5224,10,1
insert into @t select 4,'2005-9-14','一車間',6221,5 ,1
insert into @t select 5,'2005-9-14','一車間',6222,10,1
insert into @t select 6,'2005-9-20','一車間',1236,10,2
insert into @t select 7,'2005-9-20','一車間',1236,5 ,2
insert into @t select 8,'2005-10-20','二車間',3062187,2 ,1
insert into @t select 9,'2005-10-20','二車間',3062187,3 ,1
insert into @t select 10,'2005-10-20','二車間',3062187,2 ,2
insert into @t select 11,'2005-10-20','二車間',3062187,2 ,2
insert into @t select 12,'2005-10-20','二車間',3062187,2 ,1
insert into @t select 13,'2005-10-20','二車間',3062187,1 ,1
insert into @t select 14,'2005-10-20','二車間',3062187,1 ,1
insert into @t select 15,'2005-10-20','二車間',3062187,1 ,2
insert into @t select 16,'2005-10-20','二車間',3062187,1 ,1
insert into @t select 17,'2005-10-20','二車間',3062187,2 ,1
insert into @t select 18,'2005-10-20','二車間',3062187,1 ,2
insert into @t select 19,'2005-10-20','二車間',3062187,2 ,1---------得到不及格数------------------------------------------------
select 
Dname
,年份
,月份
 ,count(个数) as 不合格数
into 
T1
from(
select 
Dname,count(distinct MID ) as 个数
,datepart(yyyy, WDate)  as 年份
,datepart(mm, WDate) as 月份
from 
(select * from @t where cid = 1) T 
group by 
Dname,MID,WDate
 having 
sum(Deduct ) >=10
) Tbl1
group by
Dname
,年份
,月份
---------------得到总数量--------------------------------------------------------------------
select
Dname
,年份
,月份
,count(总数量) as 总数量
into T2
from(
select  
Dname 
,datepart(yyyy, WDate)  as 年份
,datepart(mm, WDate) as 月份
,count( distinct  MID) as 总数量
from
@t
group by 
Dname
,WDate
,WDate
,MID
) Tbl2
group by 
Dname
,年份
,月份---------------自己根据这两个表合计吧-----------------select 
T1.Dname,T1.年份,T1.月份,T2.总数量,T1.不合格数 
from 
T1 join T2
on 
T1.Dname = T2.Dname
and T1.年份 = T2.年份
and T1.月份 = T2.月份
------------------------------------------------
select * from T1    --得到不及格数 （按年月车间分组）
select * from T2 --得到总数量 （按年月车间分组）drop table T1
drop table T2

Answer 13

--得到不及格数 （按年月车间分组）
Dname      年份          月份          不合格数        
---------- ----------- ----------- ----------- 
一車間        2005        9           2
二車間        2005        10          1
--得到总数量 （按年月车间分组）
Dname      年份          月份          总数量         
---------- ----------- ----------- ----------- 
一車間        2005        9           5
二車間        2005        10          1---------------------自己根据这连个表爱算什么就算什么
Dname      年份          月份          总数量         不合格数        
---------- ----------- ----------- ----------- ----------- 
一車間        2005        9           5           2
二車間        2005        10          1           1

Answer 14

vovo2000(没人要的猫) 的方法不错,简单明了