SQL排程新算法

Question

按时间计算，每班8个小时
生产产品所有的时间是   、  要生产的产品是
2                                A
9                                B
8                                C
10                               D
15                               E
通过计算，要得到这样的结果
1班    A   2
1班    B   6
2班    B   3
2班    C   5
3班    C   3
3      D   5
4      D   5
4      E   3
5      E   8
6      E   4
...........
请问怎么实现？

Answer 1

/*建立測試環境*/
set nocount on 
GO
if exists(select 1 from dbo.sysobjects where name='T' and xtype='U')
  drop table TGO
create table T (part varchar(03), value int)
insert into T select  'A',2
insert into T select  'B',9
insert into T select  'C',8
insert into T select  'D',10
insert into T select  'E',15
insert into T select  'F',36GO/*建立臨時表，方便debug*/
select id=identity(int,1,1), *  into #T1  from Tdeclare @sum int
select @sum=sum(value) from Tselect distinct number, rtrim(number)+'班' as type, 8 as hours
into #T2
  from master..spt_values
where number between 1 and ceiling(@sum/8.0)select *
into #T3
from #T2 a
left join #T1 b
on  ceiling((select sum(value) from #T1 where id<=b.id)/8.0) >=a.number
where  ceiling(isnull((select sum(value) from #T1 where id<b.id),0)/8.0) <=a.number/*取得結果*/
select X.type, X.part, case when X.tmp>=8 then 8 else X.tmp end as real_value
from 
(
select *,     case when ( select sum(value) from #T1 where id<=A.id ) <= number * hours
                then   8 - (number * hours- (select sum(value) from #T1 where id<=A.id) ) 
                else  value-  ( (select sum(value) from #T1 where id<=A.id)  -number * hours)
                end    as tmp
from #T3 as A
) as X
/*
type    part    real_value
--------------------------------------------
1班 A 2
1班 B 6
2班 B 3
2班 C 5
3班 C 3
3班 D 5
4班 D 5
4班 E 3
5班 E 8
6班 E 4
6班 F 4
7班 F 8
8班 F 8
9班 F 8
10班 F 8*/
/*刪除測試環境*/drop table T,#T1,#T2,#T3set nocount off

Answer 2

或者用cursor 做也很方便

Answer 3

DECLARE @T1 TABLE([H] INT, [P] VARCHAR(1))
INSERT @T1 
SELECT 2, 'A' UNION ALL 
SELECT 9, 'B' UNION ALL 
SELECT 8, 'C' UNION ALL 
SELECT 10, 'D' UNION ALL 
SELECT 15, 'E'
DECLARE @T2 TABLE(H INT,P VARCHAR(10), H2 INT, GRP INT)
DECLARE @H INT, @GRP INTINSERT @T2
SELECT TOP 1 *,CASE WHEN H>8 THEN 8 ELSE H END AS H2,0 AS GRP
FROM @T1 ORDER BY PWHILE @@ROWCOUNT>0
BEGIN
  SET @H=(SELECT SUM(H2)%8 FROM @T2)
  SET @GRP=(SELECT SUM(H2)/8 FROM @T2)  INSERT @T2
  SELECT TOP 1 T.*,CASE WHEN H-ISNULL(H2,0)>8-@H THEN 8-@H ELSE H-ISNULL(H2,0) END,@GRP
  FROM @T1 AS T LEFT JOIN (SELECT P,SUM(H2) AS H2 FROM @T2 GROUP BY P) AS T2       
    ON T.P=T2.P
  WHERE T.H>ISNULL(H2,0)
  ORDER BY T.P
ENDSELECT RTRIM(GRP+1)+N'班' AS GRP,P AS PRD,H2 AS HOUR
FROM @T2
/*
GRP           PRD        HOUR
------------- ---------- -----------
1班            A          2
1班            B          6
2班            B          3
2班            C          5
3班            C          3
3班            D          5
4班            D          5
4班            E          3
5班            E          8
6班            E          4(10 行受影响)
*/

Answer 4

playwarcraft
用我的数据好像要出问题啊
1811       0.25 930 0 1 17 68
1811st     0.175 930 0 2 23 80
1811st1    0.175 930 0 3 24 82
1811st2    0.175 930 0 4 24 83
1811st21   0.25 930 0 5 21 83
1811st212  0.25 930 0 6 1 2
1st21      0.25 930 0 7 1 5
811        0.25 930 0 8 17 68
811        0.175 930 0 9 15 50

Answer 5

我套上后的数据是：
1班 1811       0.25 930 0 8
2班 1811       0.25 930 0 8
3班 1811       0.25 930 0 1
3班 1811st     0.175 930 0 7
4班 1811st     0.175 930 0 8
5班 1811st     0.175 930 0 8
5班 1811st1    0.175 930 0 0
6班 1811st1    0.175 930 0 8
7班 1811st1    0.175 930 0 8
8班 1811st1    0.175 930 0 8
8班 1811st2    0.175 930 0 0
9班 1811st2    0.175 930 0 8
10班 1811st2    0.175 930 0 8
11班 1811st2    0.175 930 0 8
11班 1811st21   0.25 930 0 0
12班 1811st21   0.25 930 0 8
13班 1811st21   0.25 930 0 8
14班 1811st21   0.25 930 0 5
14班 1811st212  0.25 930 0 6
14班 1st21      0.25 930 0 7
14班 811        0.25 930 0 1
15班 811        0.25 930 0 8
16班 811        0.25 930 0 8
16班 811        0.175 930 0 0
17班 811        0.175 930 0 8
18班 811        0.175 930 0 7

Answer 6

产品名称       厚度     宽度   长度    顺序号   生产时间    生产数量
1811       0.25 930 0 1 17      68
1811st     0.175 930 0 2 23      80
1811st1    0.175 930 0 3 24      82
1811st2    0.175 930 0 4 24           83
1811st21   0.25 930 0 5 21      83
1811st212  0.25 930 0 6 1       2
1st21      0.25 930 0 7 1       5
811        0.25 930 0 8 17      68
811        0.175 930 0 9 15      50
这就是原始数据

Answer 7

1811       0.25 930 0 1 17 68
1811st     0.175 930 0 2 23 80
1811st1    0.175 930 0 3 24 82
1811st2    0.175 930 0 4 24 83
1811st21   0.25 930 0 5 21 83
1811st212  0.25 930 0 6 1 2
1st21      0.25 930 0 7 1 5
811        0.25 930 0 8 17 68
811        0.175 930 0 9 15 50

Answer 8

最后出结果的改一下
select X.type, X.part, case when X.tmp>=8 then 8 else X.tmp end as real_value
from 
(
select *,     case when ( select sum(value) from #T1 where id<=A.id ) <= number * hours
                then   8 -  case when  (number * hours- (select sum(value) from #T1 where id<=A.id) ) >=value then  (8-value) else  (number * hours- (select sum(value) from #T1 where id<=A.id) ) end
                else  value-  ( (select sum(value) from #T1 where id<=A.id)  -number * hours)
                end    as tmp
from #T3 as A
) as X
where X.tmp>0

Answer 9

好像还有点bug，如果改数据就有一点问题了。

Answer 10

建议用cursor,效率应该比复杂的SQL要好.