sqlserver 练习题目(存储过程：循环erp数据表，生成另一个表数据)

来源：互联网发布：拾柒网络编辑：程序博客网时间：2024/06/10 20:33

– ##表结构 ###
use test_db;

CREATE TABLE [dbo].[GYGDFT](
[任务单号] varchar NOT NULL,
[任务序号] [int] NULL,
[品号] varchar NULL,
[生产数量] [int] NULL,
[备注] varchar NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[GYJHD](
[计划单号] varchar NOT NULL,
[计划序号] [int] NULL,
[品号] varchar NULL,
[计划出货数量] [int] NULL,
[计划日期] [date] NULL
) ON [PRIMARY]

drop table [TEST_REPORT];
CREATE TABLE [dbo].[TEST_REPORT](
[任务单号] varchar NULL,
[任务序号] [int] NULL,
[品号] varchar NULL,
[生产数量] [int] NULL,
[计划出货数量] [int] NULL,
[分配数量] [int] NULL,
[生产剩余数量] [int] NULL,
[计划单号] varchar NULL,
[计划序号] [int] NULL,
[计划日期] [date] NULL
) ON [PRIMARY]

– =========学习阶段：查询任务单号并分页开始================
select * from
(
select row_number()over(order by 任务序号 )temprownumber,*
from (select top 2 tempcolumn=0,*
from GYGDFT
)t
)tt where temprownumber>1

select top 1 *
from
(
select ROW_NUMBER() over (order by 任务序号) as rownumber, * from GYGDFT
) a
where a.rownumber>=2

–10代表分页的大小
select top 2 *
from GYGDFT
where 任务序号 not in
(
–40是这么计算出来的：10*(5-1)
select top 1 任务序号 from GYGDFT order by 任务序号
)
order by 任务序号
– =学习阶段：查询任务单号并分页结束=

– ## 开始愉快的旅程开始 ##
go
create procedure sp_test_report
as
begin
declare @rwdh varchar(50),
@rwxh int,
@ph varchar(50),
@scsl int,
@jhchsl int,
@fpsl int,
@jhdh varchar(50),
@jhxh int,
@scsysl int,
@jhrq date;

declare @qscsl int, --前一个生产数量@qjhchsl int, --前一个计划出货数量@qfpsl int,--前一个分配数量@qscsysl int,   --前一个生产剩余数量@qrwxh int; --前一个任务序号set @qscsl=0;set @qjhchsl=0;set @qfpsl=0;set @qscsysl=0;set @qrwxh=-1;declare @rw_i int;  set @rw_i=0; -- 任务循环变量declare @rw_count int;  --任务总记录数select @rw_count=COUNT(任务单号)  from GYGDFT;declare @jh_i int;  set @jh_i=0; -- 计划循环变量declare @jh_count int;  --任务总记录数select @jh_count=COUNT(计划单号)  from GYJHD;declare @only_task int; set @only_task=0; --只有任务没有计划while (@rw_i<@rw_count)begin    select top 1 @rwdh=任务单号, @rwxh=任务序号, @ph=品号, @scsl=生产数量      from GYGDFT    where 任务序号 not in (        select top (@rw_i) 任务序号 from GYGDFT order by 任务序号    )     order by 任务序号;    while(@jh_i<@jh_count)         begin -- 计划表循环开始            select top 1 @jhchsl=计划出货数量,@jhdh=计划单号, @jhxh=计划序号, @jhrq=计划日期              from GYJHD            where 计划序号 not in                 (                select top (@jh_i) 计划序号 from GYJHD order by 计划序号                )             order by 计划序号;            --分配数量            set @fpsl = CASE                  WHEN @qscsysl=0 and @qrwxh = -1 THEN @jhchsl                    WHEN @qscsysl > 0 and @jhchsl > abs(@qscsysl) THEN @qscsysl                     WHEN @qscsysl > 0 and @jhchsl < abs(@qscsysl)  THEN @jhchsl                                ELSE -@qscsysl END;            if(@rw_i=0)             begin                set @qrwxh = @rwxh;                             end                                     if(@qrwxh = @rwxh)                 -- 同一个任务序号                begin                    select @qrwxh,@rwxh,1;                    set @scsysl = case                        -- 前生产剩余数量                         when @qscsysl > 0 then @qscsysl-@jhchsl                                      else @scsl-@fpsl end;                    select @scsysl as '生产剩余数量1';                end                 else                begin -- 任务序号不同                    select @qrwxh,@rwxh,2;                    set @scsysl = CASE                                                    WHEN @qscsysl < 0 THEN @scsl-@fpsl                     else @fpsl-@jhchsl end;                                             select @scsysl as '生产剩余数量2';                            end                 select @rwdh,@rwxh,@ph,@scsl,@jhchsl,@fpsl,@scsysl,@jhdh,@jhxh,@jhrq;            insert into TEST_REPORT(任务单号,任务序号,品号,生产数量,计划出货数量,分配数量,生产剩余数量,计划单号,计划序号,计划日期)             values(@rwdh,@rwxh,@ph,@scsl,@jhchsl,@fpsl,@scsysl,@jhdh,@jhxh,@jhrq);            -- 生产数量 计划出货数量  分配数量    生产剩余数量赋值给前一个变量            set @qscsl=@scsl;            set @qjhchsl=@jhchsl;            set @qfpsl=@fpsl;            set @qscsysl=@scsysl;            if(@qscsysl>0)                begin                    set @qrwxh=@rwxh;                    set @jh_i=@jh_i+1;                end            else            begin                break; -- 任务已经完成，进行下一个任务            end        end; -- 计划表循环结束        set @rw_i=@rw_i+1;          if(@only_task=1)         begin            set @qscsysl=@scsl + @qscsysl;            insert into TEST_REPORT(任务单号,任务序号,品号,生产数量,计划出货数量,分配数量,生产剩余数量,计划单号,计划序号,计划日期)             values(@rwdh,@rwxh,@ph,@scsl,0,0,@qscsysl,null,null,null);        end        if(@jh_i=@jh_count)         begin            set @only_task=1;        endend

end
– ## 结束愉快的旅程 ##

– 执行存储过程，并查询
exec dbo.sp_test_report;
select * from TEST_REPORT;

– 删除存储过程，并删除数据
drop procedure sp_test_report;
truncate table test_report;

测试数据
GYJHD（计划表）
计划单号计划序号品号计划出货数量计划日期
2016092701 1 BM280B-1.0301 400 20160927
2016092701 2 BM280B-1.0301 600 20160928
2016092701 3 BM280B-1.0301 300 20160929
2016092701 4 BM280B-1.0301 700 20160930

GYGDFT（生产任务表）
任务单号任务序号品号生产数量备注
20160720001 1 BM280B-1.0301 900
20160720002 2 BM280B-1.0301 700
20160720003 3 BM280B-1.0301 900
20160720004 4 BM280B-1.0301 100
20160720005 5 BM280B-1.0301 300
SQL语句得出
任务单号任务序号品号生产数量计划出货数量分配数量生产剩余数量计划单号计划序号计划日期
20160720001 1 BM280B-1.0301 900 400 400 500 2016092701 1 20160927
20160720001 1 BM280B-1.0301 900 600 500 -100 2016092701 2 20160928
20160720002 2 BM280B-1.0301 700 600 100 600 2016092701 2 20160928
20160720002 2 BM280B-1.0301 700 300 300 300 2016092701 3 20160929
20160720002 2 BM280B-1.0301 700 700 300 -400 2016092701 4 20160930
20160720003 3 BM280B-1.0301 900 700 400 500 2016092701 4 20160930
20160720004 4 BM280B-1.0301 100 0 0 600
20160720005 5 BM280B-1.0301 300 0 0 900
下载地址：
http://download.csdn.net/detail/u010503822/9644796

0 1