幫忙看看這SQL語句。能不用遊標實現嗎？

Question

   是做生產排程的，先說說思路。
   1、根據公司的工作時間制度、較機時間等，可以算出設備每天有效的時間（單位：分鐘），這做為基礎資料保存下來，由用戶維護來維護。
   2、根據工藝流程可以算出完成某工序需要佔用某設備的時間。
   3、可以把1步驟的數據看成是庫存數，而2步驟的則是出庫數。安排生產計劃時就是去扣設備的有效時間。我現在隻想到用遊標來實現，想請教更簡單的方法。不方便說的，給個思路就行，謝謝啦！--設備A每天可利用的有效時間(單位：分鐘)、已經佔用的時間
create table #t(Machine char(1) ,d datetime,Minutes int,UsedMinute int)
               --設備        日期        總時間     已佔用時間  
insert into #t
select 'A','2009-06-29',600,300 union all--6月29號總時間600，已經佔用300
select 'A','2009-06-30',600,null union all
select 'A','2009-07-01',600,100 union all
select 'A','2009-07-02',400,null union all
select 'A','2009-07-03',600,null union all
select 'A','2009-07-04',700,null--生產計劃    設備           日期      佔用時間
create table #v(Machine char(1) ,d datetime,UsedMinute int)--計劃需要佔用設備A共2000分鐘
declare @Minutes int
set @Minutes=2000--從6月29號開始生產
declare @BeginDate datetime
set @BeginDate='2009-06-29'--每次循環後還需要的時間
declare @NeedMinute int
set @NeedMinute=@Minutesdeclare @curMachine char(1)
declare @curd datetime
declare @curMinutes int
declare @curLeftMinute int--設備當天的剩余時間declare psCursor cursor for
select Machine ,d,Minutes,(Minutes-isnull(UsedMinute,0)) as LeftMinutes
from #t
where d>=@BeginDate --從6月29號開始生產
and Minutes>isnull(UsedMinute,0)--設備還有空閒時間
order by dopen psCursor
fetch  from psCursor into @curMachine,@curd,@curMinutes,@curLeftMinutewhile (@@fetch_status=0
       and @NeedMinute>0)--如果計劃還需要時間
begin      
    if(@NeedMinute>@curLeftMinute--計劃完成的時間 大於 設備剩余時間
       and @curLeftMinute>0)
     begin    
 
       insert into #v  --插入一條計劃
       select @curMachine,@curd,@curLeftMinute        update #t      --更新設備的已佔用時間
        set UsedMinute=isnull(UsedMinute,0)+@curLeftMinute--原來的 已佔用時間 加上 剩余時間。
        where Machine=@curMachine                         --也可以 佔用時間 等於 總時間
         and  d=@curd
       
        --求完成計劃尚需要多長時間
        set @NeedMinute=@NeedMinute-@curLeftMinute
       
     end
    else if(@NeedMinute<=@curLeftMinute)  --計劃完成需要的時間 小於等於 設備剩余時間
     begin
  
        insert into #v  --插入一條計劃
        select @curMachine,@curd,@NeedMinute
 
       update #t       --更新設備的已佔用時間
       set UsedMinute=isnull(UsedMinute,0)+@NeedMinute--原來的 已佔用時間 加上 本計劃完成需要的時間
       where Machine=@curMachine
       and  d=@curd          ----計劃已完成，直接修改需要時間為0
       set @NeedMinute=0     endfetch  from psCursor into @curMachine,@curd,@curMinutes,@curLeftMinute
endclose psCursor
DEALLOCATE psCursorselect * from #t
select * from #vdrop table #t
drop table #v--結果
--設備佔用時間表
--Machine    d    Minutes   UsedMinute
A 2009-06-29  600 600
A 2009-06-30  600 600
A 2009-07-01  600 600
A 2009-07-02  400 400
A 2009-07-03  600 200
A 2009-07-04  700 NULL  --生產計劃表
--Machine   d     UsedMinute
A 2009-06-29  300
A 2009-06-30  600
A 2009-07-01  500
A 2009-07-02  400
A 2009-07-03  200

Answer 1

分两种情况插入和删除
--計劃完成的時間 大於 設備剩余時間
insert into #v  --插入一條計劃
       select 加条件语句找到計劃完成的時間 大於 設備剩余時間的记录
update #t      --更新設備的已佔用時間
        set UsedMinute=isnull(UsedMinute,0)+@curLeftMinute--原來的 已佔用時間 加上 剩余時間。
from #t a, #v b where....................
--計劃完成的時間 小於等於 設備剩余時間
insert into #v  --插入一條計劃
       select 加条件语句找到計劃完成的時間 小於等於 設備剩余時間的记录
update #t      --更新設備的已佔用時間
        set UsedMinute=isnull(UsedMinute,0)+@curLeftMinute
from #t a, #v b where....................

Answer 2

不需要用游标：
declare @t table(Machine char(1) ,d datetime,Minutes int,UsedMinute int)
insert into @t
select 'A','2009-06-29',600,300  union all--6月29號總時間600，已經佔用300
select 'A','2009-06-30',600,null union all
select 'A','2009-07-01',600,100  union all
select 'A','2009-07-02',400,null union all
select 'A','2009-07-03',600,null union all
select 'A','2009-07-04',700,nulldeclare @Minutes int
set @Minutes=2000select
    a.Machine,a.d,a.Minutes,
    case 
       when sum(b.Minutes-isnull(b.UsedMinute,0))<=@Minutes then a.Minutes
       when sum(b.Minutes-isnull(b.UsedMinute,0))-a.Minutes>=@Minutes then null
       else a.Minutes-(sum(b.Minutes-isnull(b.UsedMinute,0))-@Minutes) 
    end as UsedMinute
from
    @t a,@t b
where
    a.Machine=b.Machine and a.d>=b.d
    and
    a.Machine='A'
group by
    a.Machine,a.d,a.Minutes,a.UsedMinute
/*
Machine d                                                      Minutes     UsedMinute  
------- ------------------------------------------------------ ----------- ----------- 
A       2009-06-29 00:00:00.000                                600         600
A       2009-06-30 00:00:00.000                                600         600
A       2009-07-01 00:00:00.000                                600         600
A       2009-07-02 00:00:00.000                                400         400
A       2009-07-03 00:00:00.000                                600         200
A       2009-07-04 00:00:00.000                                700         NULL
*/select
    a.Machine,a.d,a.Minutes,
    case 
       when sum(b.Minutes-isnull(b.UsedMinute,0))<=@Minutes then a.Minutes
       else a.Minutes-(sum(b.Minutes-isnull(b.UsedMinute,0))-@Minutes) 
    end -isnull(a.UsedMinute,0) as UsedMinute
from
    @t a,@t b
where
    a.Machine=b.Machine and a.d>=b.d
    and
    a.Machine='A'
group by
    a.Machine,a.d,a.Minutes,a.UsedMinute
having
    sum(b.Minutes-isnull(b.UsedMinute,0))-a.Minutes<@Minutes
/*
Machine d                                                      Minutes     UsedMinute  
------- ------------------------------------------------------ ----------- ----------- 
A       2009-06-29 00:00:00.000                                600         300
A       2009-06-30 00:00:00.000                                600         600
A       2009-07-01 00:00:00.000                                600         500
A       2009-07-02 00:00:00.000                                400         400
A       2009-07-03 00:00:00.000                                600         200
*/

Answer 3

如果下面這個表的數據很多，比如一次性維護設備一年每天的有效時間（象公司日歷一樣），而某個計劃幾天就完成了，自連接 也要用整個表的數據嗎？declare @t table(Machine char(1) ,d datetime,Minutes int,UsedMinute int)
insert into @t
select 'A','2009-06-29',600,300  union all--6月29號總時間600，已經佔用300
select 'A','2009-06-30',600,null union all
select 'A','2009-07-01',600,100  union all
select 'A','2009-07-02',400,null union all
select 'A','2009-07-03',600,null union all
select 'A','2009-07-04',700,null

Answer 4

在 Where 条件后限制所需要提取数据的日期范围即可。

Answer 5

if object_id('t')is not null 
   drop table t
if object_id('v')is not null 
   drop table v
go
create table v (marchine char(1),curd datetime,usedminute int)
create table t(Machine char(1) ,d datetime,Minutes int,UsedMinute int)
               --設備        日期        總時間     已佔用時間  
insert into t
select 'A','2009-06-29',600,300 union all--6月29號總時間600，已經佔用300
select 'A','2009-06-30',600,null union all
select 'A','2009-07-01',600,100 union all
select 'A','2009-07-02',400,null union all
select 'A','2009-07-03',600,null union all
select 'A','2009-07-04',700,null
go
--select * from t
declare @Minutes int
set @Minutes=2000
--------生产主划表
insert into v  select * from 
(select machine,d,h=(case when 剩余分数>0 then 当天可用分数
                  when 剩余分数<0 and -剩余分数<Minutes then Minutes+剩余分数  end)   from 
       (select *,当天可用分数=Minutes-ISNULL(UsedMinute,0),剩余分数=@Minutes-(select sum(Minutes)-sum(ISNULL(UsedMinute,0)) from t where d<=a.d) from t a) t ) TB WHERE h is not null
--------设备时间表
update t set t.usedminute = case when 剩余分数>0 then h.Minutes
                  when 剩余分数<0 and -剩余分数<h.Minutes then h.Minutes+剩余分数  end from t join 
                    (select *,当天可用分数=Minutes-ISNULL(UsedMinute,0),剩余分数=@Minutes-(select sum(Minutes)-sum(ISNULL(UsedMinute,0)) from t where d<=a.d) from t a) h on t.machine=h.machine and  t.d=h.d
go
 select * from v
 select * from t 
marchine curd                    usedminute
-------- ----------------------- -----------
A        2009-06-29 00:00:00.000 300
A        2009-06-30 00:00:00.000 600
A        2009-07-01 00:00:00.000 500
A        2009-07-02 00:00:00.000 400
A        2009-07-03 00:00:00.000 200(5 行受影响)Machine d                       Minutes     UsedMinute
------- ----------------------- ----------- -----------
A       2009-06-29 00:00:00.000 600         600
A       2009-06-30 00:00:00.000 600         600
A       2009-07-01 00:00:00.000 600         600
A       2009-07-02 00:00:00.000 400         400
A       2009-07-03 00:00:00.000 600         200
A       2009-07-04 00:00:00.000 700         NULL(6 行受影响)

Answer 6

获取加上下面的条件，和您写的第二个SQL语句一样
having
    sum(b.Minutes-isnull(b.UsedMinute,0))-a.Minutes<@Minutes

Answer 7

基本上看明白 子陌红尘 的大作了。
知识下面这句一开始没办法直接理解
 else a.Minutes-(sum(b.Minutes-isnull(b.UsedMinute,0))-@Minutes) 
    end as UsedMinute最后写成这样终于明白了
  else @Minutes-(sum(b.Minutes-isnull(b.UsedMinute,0))-a.Minutes)            
    end as UsedMinute

Answer 8

改了一下，每个物料需要用多种设备declare @t table(Machine char(1) ,d datetime,Minutes int,UsedMinute int)
insert into @t --两种设备A、B
select 'A','2009-06-29',600,300  union all
select 'A','2009-06-30',600,null union all
select 'A','2009-07-01',600,100  union all
select 'A','2009-07-02',400,null union all
select 'A','2009-07-03',600,null union all
select 'A','2009-07-04',700,null union all
select 'B','2009-06-29',600,null union all
select 'B','2009-06-30',600,300 union all
select 'B','2009-07-01',600,null union all
select 'B','2009-07-02',400,null union all
select 'B','2009-07-03',600,null declare @v table(Item char(1),Machine char(1) ,d datetime,Minutes int)
insert into @v --物料K，需要用设备A、B.其中A从29号开始使用、B从30号开始使用
select 'K','A','2009-06-29',2000 union all
select 'K','B','2009-06-30',1300
select
    a.Machine,a.d,a.Minutes,
    case 
       when sum(b.Minutes-isnull(b.UsedMinute,0))<=v.Minutes then a.Minutes
       when sum(b.Minutes-isnull(b.UsedMinute,0))-a.Minutes>=v.Minutes then null
       else a.Minutes-(sum(b.Minutes-isnull(b.UsedMinute,0))-v.Minutes) 
    end as UsedMinute
from
    @t a,@t b,@v v
where
    a.Machine=b.Machine and a.d>=b.d
    and
    a.Machine=v.Machine
    and
    b.d>=v.d
group by
    a.Machine,a.d,a.Minutes,a.UsedMinute,v.Minutes
having
    sum(b.Minutes-isnull(b.UsedMinute,0))-a.Minutes <v.Minutes
select
    a.Machine,a.d,a.Minutes,
    case 
       when sum(b.Minutes-isnull(b.UsedMinute,0))<=v.Minutes then a.Minutes
       else a.Minutes-(sum(b.Minutes-isnull(b.UsedMinute,0))-v.Minutes) 
    end -isnull(a.UsedMinute,0) as UsedMinute
from
    @t a,@t b,@v v
where
    a.Machine=b.Machine and a.d>=b.d
    and
    a.Machine=v.Machine
    and
    b.d>=v.d
group by
    a.Machine,a.d,a.Minutes,a.UsedMinute,v.Minutes
having
    sum(b.Minutes-isnull(b.UsedMinute,0))-a.Minutes<v.MinutesMachine d                                                      Minutes     UsedMinute  
------- ------------------------------------------------------ ----------- ----------- 
A       2009-06-29 00:00:00.000                                600         600
A       2009-06-30 00:00:00.000                                600         600
A       2009-07-01 00:00:00.000                                600         600
A       2009-07-02 00:00:00.000                                400         400
A       2009-07-03 00:00:00.000                                600         200
B       2009-06-30 00:00:00.000                                600         600
B       2009-07-01 00:00:00.000                                600         600
B       2009-07-02 00:00:00.000                                400         400（所影响的行数为 8 行）Machine d                                                      Minutes     UsedMinute  
------- ------------------------------------------------------ ----------- ----------- 
A       2009-06-29 00:00:00.000                                600         300
A       2009-06-30 00:00:00.000                                600         600
A       2009-07-01 00:00:00.000                                600         500
A       2009-07-02 00:00:00.000                                400         400
A       2009-07-03 00:00:00.000                                600         200
B       2009-06-30 00:00:00.000                                600         300
B       2009-07-01 00:00:00.000                                600         600
B       2009-07-02 00:00:00.000                                400         400（所影响的行数为 8 行）

Answer 9

上面的是同物料不同設備，另一種情況還沒解決：不同物料，相同設備。即：declare @v table(Item char(1),Machine char(1) ,d datetime,Minutes int)
insert into @v --物料K，需要用设备A、B.其中A从29号开始使用、B从30号开始使用
select 'K','A','2009-06-29',2000 union all
select 'K','B','2009-06-30',1300 union all
select 'M','A','2009-07-01',300  --M物料也要用設備A我先好好想想，做出來再貼上來