现有一个表结构如下:Week Machine T_Qty J_Start J1 J2 Runtime
46 Storti 2075 10 5 NULL 440
46 Storti 2100 NULL NULL NULL 440
46 Storti 2465 10 NULL 50 NULL
46 Storti 2400 NULL NULL NULL 440
47 Storti 2446 NULL NULL 5 440
47 Storti 2018 24 NULL NULL 440
47 Storti 1950 NULL NULL NULL 440
47 Storti 1731 20 43 17 440现在要求生成一个新的表,比原来的表多一列 OEE新增列的计算公式的程序流图如下:
请教大家,这个语句用SQL怎么写呢? 结果如下:
Week Machine T_Qty J_Start J1 J2 Runtime OEE
46 Storti 2075 10 5 NULL 440 0.91
46 Storti 2100 NULL NULL NULL 440 0.80
46 Storti 2465 10 NULL 50 NULL 1.03
46 Storti 2400 NULL NULL NULL 440 0.91
47 Storti 2446 NULL NULL 5 440 0.99
47 Storti 2018 24 NULL NULL 440 0.82
47 Storti 1950 NULL NULL NULL 440 0.74
47 Storti 1731 20 43 17 440 0.82
46 Storti 2075 10 5 NULL 440
46 Storti 2100 NULL NULL NULL 440
46 Storti 2465 10 NULL 50 NULL
46 Storti 2400 NULL NULL NULL 440
47 Storti 2446 NULL NULL 5 440
47 Storti 2018 24 NULL NULL 440
47 Storti 1950 NULL NULL NULL 440
47 Storti 1731 20 43 17 440现在要求生成一个新的表,比原来的表多一列 OEE新增列的计算公式的程序流图如下:
请教大家,这个语句用SQL怎么写呢? 结果如下:
Week Machine T_Qty J_Start J1 J2 Runtime OEE
46 Storti 2075 10 5 NULL 440 0.91
46 Storti 2100 NULL NULL NULL 440 0.80
46 Storti 2465 10 NULL 50 NULL 1.03
46 Storti 2400 NULL NULL NULL 440 0.91
47 Storti 2446 NULL NULL 5 440 0.99
47 Storti 2018 24 NULL NULL 440 0.82
47 Storti 1950 NULL NULL NULL 440 0.74
47 Storti 1731 20 43 17 440 0.82
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-18 19:57:34
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Week] int,[Machine] varchar(6),[T_Qty] int,[J_Start] int,[J1] int,[J2] int,[Runtime] int)
insert [tb]
select 46,'Storti',2075,10,5,null,440 union all
select 46,'Storti',2100,null,null,null,440 union all
select 46,'Storti',2465,10,null,50,null union all
select 46,'Storti',2400,null,null,null,440 union all
select 47,'Storti',2446,null,null,5,440 union all
select 47,'Storti',2018,24,null,null,440 union all
select 47,'Storti',1950,null,null,null,440 union all
select 47,'Storti',1731,20,43,17,440
--------------开始查询--------------------------
select
*,oee=cast(T_Qty*1.0/(6*(480-isnull(J1,0)-isnull(J2,0)-30)) as dec(18,2))
from
tb ----------------结果----------------------------
/*Week Machine T_Qty J_Start J1 J2 Runtime oee
----------- ------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
46 Storti 2075 10 5 NULL 440 0.78
46 Storti 2100 NULL NULL NULL 440 0.78
46 Storti 2465 10 NULL 50 NULL 1.03
46 Storti 2400 NULL NULL NULL 440 0.89
47 Storti 2446 NULL NULL 5 440 0.92
47 Storti 2018 24 NULL NULL 440 0.75
47 Storti 1950 NULL NULL NULL 440 0.72
47 Storti 1731 20 43 17 440 0.74(8 行受影响)
*/
原始数据:
Week Machine T_Qty J_Start J1 J2 Runtime
46 Storti 2075 10 5 NULL 440
46 Storti 2100 NULL NULL NULL 440
46 Storti 2465 10 NULL 50 NULL
46 Storti 2400 NULL NULL NULL 440
47 Storti 2446 NULL NULL 5 440
47 Storti 2018 24 NULL NULL 440
47 Storti 1950 NULL NULL NULL 440
47 Storti 1731 20 43 17 440结果如下:
Week Machine T_Qty J_Start J1 J2 Runtime OEE
46 Storti 2075 10 5 NULL 440 0.91
46 Storti 2100 NULL NULL NULL 440 0.80
46 Storti 2465 10 NULL 50 NULL 1.03
46 Storti 2400 NULL NULL NULL 440 0.91
47 Storti 2446 NULL NULL 5 440 0.99
47 Storti 2018 24 NULL NULL 440 0.82
47 Storti 1950 NULL NULL NULL 440 0.74
47 Storti 1731 20 43 17 440 0.82
算法和程序流图不同现在放上表结构和结果图:
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Week] int,[Machine] varchar(6),[T_Qty] int,[J_Start] int,[J1] int,[J2] int,[Runtime] int)
insert [tb]
select 46,'Storti',2075,10,5,null,440 union all
select 46,'Storti',2100,null,null,null,440 union all
select 46,'Storti',2465,10,null,50,null union all
select 46,'Storti',2400,null,null,null,440 union all
select 47,'Storti',2446,null,null,5,440 union all
select 47,'Storti',2018,24,null,null,440 union all
select 47,'Storti',1950,null,null,null,440 union all
select 47,'Storti',1731,20,43,17,440
select * from tbWeek Machine T_Qty J_Start J1 J2 Runtime OEE
----------- ------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
46 Storti 2075 10 5 NULL 440 0.91
46 Storti 2100 NULL NULL NULL 440 0.80
46 Storti 2465 10 NULL 50 NULL 1.03
46 Storti 2400 NULL NULL NULL 440 0.91
47 Storti 2446 NULL NULL 5 440 0.99
47 Storti 2018 24 NULL NULL 440 0.82
47 Storti 1950 NULL NULL NULL 440 0.74
47 Storti 1731 20 43 17 440 0.82
CASE WHEN都累
go
create table [tb]([Week] int,[Machine] varchar(6),[T_Qty] int,[J_Start] int,[J1] int,[J2] int,[Runtime] int)
insert [tb]
select 46,'Storti',2075,10,5,null,440 union all
select 46,'Storti',2100,null,null,null,440 union all
select 46,'Storti',2465,10,null,50,null union all
select 46,'Storti',2400,null,null,null,440 union all
select 47,'Storti',2446,null,null,5,440 union all
select 47,'Storti',2018,24,null,null,440 union all
select 47,'Storti',1950,null,null,null,440 union all
select 47,'Storti',1731,20,43,17,440select *,oee=case when isnull([Runtime],0)>0 then
t_qty*1.0/(6*(runtime-30-(30*(case when isnull(J_Start,0)>0 then 1 else 0 end+case when isnull(j1,0)>0 then 1 else 0 end+case when isnull(j2,0)>0 then 1 else 0 end))))
else t_qty*1.0/(6*(480-isnull(j1,0)-isnull(j2,0)-30)) end from tb
--楼主的结果貌似有错:Week Machine T_Qty J_Start J1 J2 Runtime oee
----------- ------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
46 Storti 2075 10 5 NULL 440 0.988095238095
46 Storti 2100 NULL NULL NULL 440 0.853658536585
46 Storti 2465 10 NULL 50 NULL 1.027083333333
46 Storti 2400 NULL NULL NULL 440 0.975609756097
47 Storti 2446 NULL NULL 5 440 1.072807017543
47 Storti 2018 24 NULL NULL 440 0.885087719298
47 Storti 1950 NULL NULL NULL 440 0.792682926829
47 Storti 1731 20 43 17 440 0.901562500000