现在有如下表一张:
说明:
p_id: 产品型号
gx_name:工序名称
bl:不良数量
hg:生产数量
gxhgl:工序合格率 计算方法为:(hg-bl)/hg一个产品(p_id)对应多道工序(gx_name)现在要计算的事每个产品(p_id)的一次合格率。
一次合格率:一个产品(p_id)的六个工序合格率(gxhgl)的乘积.
求大侠们帮帮忙。谢谢。
说明:
p_id: 产品型号
gx_name:工序名称
bl:不良数量
hg:生产数量
gxhgl:工序合格率 计算方法为:(hg-bl)/hg一个产品(p_id)对应多道工序(gx_name)现在要计算的事每个产品(p_id)的一次合格率。
一次合格率:一个产品(p_id)的六个工序合格率(gxhgl)的乘积.
求大侠们帮帮忙。谢谢。
解决方案 »
- WWW.MYSQL.COM上怎么下不了MYSQL
- 表的关联
- 怎么定时备份sql 数据库
- Java.sql.SQLException:[Microsoft ][SQLServer 2000 Driver for JDBC][Error establish hing socket异常
- 初学问题,高分求教!!!!
- 急急!!
- (100分)关于利用Sql2000分析服务的多维数据集做统计指标的同期比较!高手指教
- 使用自动增长ID,如何在插入记录后快速高效的或许所插入记录的ID值?
- 怎样删除SQLServer的事务日志
- AND 和 OR一起用的方法
- 临时表在函数里的写法
- 像csdn里系统消息,它的数据库是如何设计的?
FROM (
SELECT P_ID,MAX(gxhgl) 'GXHGL' FROM TB WHERE gx_name='A' GROUP BY P_ID
) A
INNER JOIN (
SELECT P_ID,MAX(gxhgl) 'GXHGL' FROM TB WHERE gx_name='B' GROUP BY P_ID
) B ON A.P_ID=B.P_ID
INNER JOIN (
SELECT P_ID,MAX(gxhgl) 'GXHGL' FROM TB WHERE gx_name='C' GROUP BY P_ID
) C ON A.P_ID=C.P_ID
INNER JOIN (
SELECT P_ID,MAX(gxhgl) 'GXHGL' FROM TB WHERE gx_name='D' GROUP BY P_ID
) D ON A.P_ID=D.P_ID
INNER JOIN (
SELECT P_ID,MAX(gxhgl) 'GXHGL' FROM TB WHERE gx_name='E' GROUP BY P_ID
) E ON A.P_ID=E.P_ID
INNER JOIN (
SELECT P_ID,MAX(gxhgl) 'GXHGL' FROM TB WHERE gx_name='F' GROUP BY P_ID
) F ON A.P_ID=F.P_ID
XHD65 ?
XHD39 ?
"?"表示这个产品的每道工序合格率的乘积,
if object_id('tb') is not null drop table tb
go
create table tb([p_id] varchar(50),[gx_name] varchar(50),[hgl] float)
insert into tb
select '1','a',0.99 union all
select '1','b',0.87 union all
select '2','a',0.87 union all
select '2','b',0.97
go
select * from tb
/*
p_id gx_name hgl
-------------------- -------------------- ----------------------
1 a 0.99
1 b 0.87
2 a 0.87
2 b 0.97
*/--drop function getV
create function getV(@p_id varchar(10))
returns float
as
begin
declare @s float;
set @s = 1;
select @s = @s * hgl from tb where p_id = @p_id
return @s
endselect p_id,dbo.getV(p_id) as ghl from tb
group by p_id/*
p_id ghl
-------------------- ----------------------
1 0.8613
2 0.8439*/
??
--自己该该试试
select DISTINCT x.aid, x.cc*y.cc*z.cc From #B x left join #B y on x.aid = y.aid left join #B z on x.aid = z.aid
where x.cc<>y.cc and x.cc <>z.cc and y.cc<> z.cc
create table test
(p_id varchar(50),
gx_name varchar(50),
bl int,
hg int,
gxhgl float
)select * from test
insert into test
select 'XHG39R','焊接',5,100,0.950000
union
select 'XHG39R','割弹簧',1,300,0.996666
union
select 'XHG39R','冲气',3,200,0.985000
union
select 'XHG39R','调试',12,700,0.982857
union
select 'XHG39R','组装',4,400,0.990000
union
select 'XHG39R','包装',1,100,0.990000select distinct t1.p_id,t1.gxhgl*t2.gxhgl*t3.gxhgl*t4.gxhgl*t5.gxhgl*t6.gxhgl
from test t1,test t2,test t3,test t4,test t5,test t6
where t1.p_id=t2.p_id
and t1.p_id=t3.p_id and t1.p_id=t4.p_id and t1.p_id=t5.p_id and t1.p_id=t6.p_id
and t1.gx_name='焊接'
and t2.gx_name='割弹簧'
and t3.gx_name='冲气'
and t4.gx_name='调试'
and t5.gx_name='组装'
and t6.gx_name='包装'
select p_id,power(10.00,Sum(Log10(gxhgl)))from qq group by p_id