SQL 计算瓶颈工序以及产能解决方案

--> 测试数据：[tbl]goif object_id('[tbl]') is not null drop table [tbl]gocreate table [tbl]([PCBCode] varchar(3),[LineCode] varchar(5),[ProcessCode] varchar(2),[OperTime] datetime,[Operator] varchar(7))goinsert [tbl]select 'A01','Line1','NO','2012-03-15 09:55:37','Person1' union allselect 'A01','Line1','CS','2012-03-15 10:02:54','Person2' union allselect 'A01','Line1','OP','2012-03-15 10:09:10','Person3' union allselect 'A01','Line1','AG','2012-03-15 10:15:19','Person4' union allselect 'A01','Line1','CP','2012-03-15 11:01:19','Person5' union allselect 'A02','Line1','NO','2012-03-15 10:00:25','Person1' union allselect 'A02','Line1','CS','2012-03-15 10:05:22','Person2' union allselect 'A02','Line1','OP','2012-03-15 10:12:19','Person3' union allselect 'A02','Line1','AG','2012-03-15 10:20:33','Person4' union allselect 'A02','Line1','CP','2012-03-15 11:30:55','Person5'select * from tbl/*上面为工序流程表，有5道工序，目前产出的两个产品A01和A02，上一道工序到下一道工序之间有间隔时间，我想要计算产线1，也就是Line1的工序瓶颈，也就是生产最慢的环节，以及该产线的产能（注：产能计算【小时】=3600/NO工序消耗时间S+...+AG工序消耗的时间S）*/;with tas(select ROW_NUMBER()over(partition by [PCBCode],[LineCode]order by [OperTime]) as id,*,[OperTime] as [endtime] from tbl),m as(select id,[PCBCode],[LineCode],[ProcessCode],[Operator],[OperTime],endtime,DATEDIFF(mi,[OperTime],[endtime]) as haoshifrom t where id=1union allselect a.id,a.[PCBCode],a.[LineCode],a.[ProcessCode],a.[Operator],a.[OperTime],a.endtime,DATEDIFF(mi,b.[OperTime],a.[endtime]) from t ainner join m b on a.id=b.id+1 and a.[PCBCode]=b.[PCBCode])select  [PCBCode],[LineCode],[ProcessCode],[OperTime],[Operator],haoshi from m order by [PCBCode]/*PCBCode    LineCode    ProcessCode    OperTime    Operator    haoshiA01    Line1    NO    2012-03-15 09:55:37.000    Person1    0A01    Line1    CS    2012-03-15 10:02:54.000    Person2    7A01    Line1    OP    2012-03-15 10:09:10.000    Person3    7A01    Line1    AG    2012-03-15 10:15:19.000    Person4    6A01    Line1    CP    2012-03-15 11:01:19.000    Person5    46A02    Line1    NO    2012-03-15 10:00:25.000    Person1    0A02    Line1    CS    2012-03-15 10:05:22.000    Person2    5A02    Line1    OP    2012-03-15 10:12:19.000    Person3    7A02    Line1    AG    2012-03-15 10:20:33.000    Person4    8A02    Line1    CP    2012-03-15 11:30:55.000    Person5    70*/我已经把每个产品的工序之间的时间间隔（haoshi）给计算出来了，要找最大的你自己会了吧，我不清楚你的最大的是怎么个取法，也不知道要取出哪些字段，另外产能计算你也可以在我的结果上计算就好了
------解决方案--------------------
create view myview
as

select * from T

就可以生成视图了