SQL脚本根据条件将单个数据行拆分为多个数据行 [英] SQL Script to split single data rows into multiple data rows based on a condition
本文介绍了SQL脚本根据条件将单个数据行拆分为多个数据行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想根据拍品数量拆分客户订单。
这是客户订单表。
I want to split a customer order depending on Lot qty.
Here is Customer Order table.
Product Size Purch. Qty Lot Qty
-----------------------------------------------
Addidas Shoe 8 30 25
Addidas Shoe 9 15 25
Addidas Shoe 10 50 25
Puma Shoe 7 40 30
Puma Shoe 8 60 30
我有2件产品Addidas鞋子要分成多排,这取决于它的Lot数量25和Puma鞋子要分成多排,具体取决于它批次数量30如下所示。
I have 2 products Addidas shoe to be splitted in to multiple rows depending on its Lot qty 25 and Puma shoe to be splitted in to multiple rows depending on its Lot qty 30 as shown below.
Lot No Sl. No Product Size Qty
1000 1 Addidas Shoe 8 25
1001 1 Addidas Shoe 8 5
1001 2 Addidas Shoe 9 15
1001 3 Addidas Shoe 10 5
1002 1 Addidas Shoe 10 25
1003 1 Addidas Shoe 10 20
1004 1 Puma Shoe 7 30
1005 1 Puma Shoe 7 10
1005 2 Puma Shoe 8 20
1006 1 Puma Shoe 8 30
1007 1 Puma Shoe 8 10
请帮我解决这个问题。
Please help me to get this.
推荐答案
我得到了结果
DECLARE @CustOrder TABLE(
ItemSize int,
产品varchar(20),
Qty INT,lotQty int)
INSERT @CustOrder
VALUES
(
'8', 'Addidas Shoe',30,25
)
INSERT @CustOrder
VALUES
(
'9','Addidas Shoe',15,25
)
INSERT @CustOrder
VALUES
(
'10','Addidas鞋',50,25
)
INSERT @C ustOrder
VALUES
(
'7','Puma Shoe',40,30
)
INSERT @CustOrder
VALUES
(
'8','Puma Shoe',60,30 >
)
声明@Lot表(LotNo varchar(20),Seq int,ItemSize int,Product varchar(20),Qty int)
声明@RemQty int,@ curQty int,@ curLotQty INT
SET @ RemQty = 0;
声明@ ItemSize int,@ Product varchar(20),@ Qty int,@ slotQty int,@ oldProduct varchar(20)
声明@lno int
设置@lno = 0
声明@seq int
设置@seq = 1
声明table_cursor光标本地fast_forward为
选择
ItemSize,Product,Qty,lotQty
来自@CustOrder的
按产品订购,Itemsize
open table_cursor
而1 = 1
begin
if @RemQty< = 0 >
开始
将table_cursor提取到@ItemSize,@ Product,@ Qty,@ hubQty
if @@ fetch_status<> 0 break
如果@oldProduct<> @Product AND @oldProduct IS NOT NULL
设置@lno = @lno + 1
如果@oldProduct<> @Product或@oldProduct为空
开始
选择@oldProduct = @Product
选择@RemQty = 0设置@seq = 1
结束
结束
如果@RemQty< 0和abs(@RemQty)< @lotQty
BEGIN
选择@curLotQty = abs(@RemQty)
设置@seq = @seq + 1
结束
ELSE
选择@curLotQty = @lotQty
如果@RemQty> 0和@RemQty< @Qty
BEGIN
select @curQty = @RemQty
set @lno = @lno + 1
set @seq = 1
END
ELSE
select @curQty = @Qty
选择@RemQty = @curQty - @curLotQty
插入@Lot
SELECT'100'+ CAST(@lno as varchar ),@ seq,@ ItemSize,@ Product,@curQty> @curLotQty然后@curLotQty其他@curQty结束
结束
关闭table_cursor
deallocate table_cursor
SELECT LotNo [批号],Seq [Sl。 No],Product,ItemSize [Size],来自产品的@Lot订单数量,Itemsize
I got the result
DECLARE @CustOrder TABLE(
ItemSize int,
Product varchar(20),
Qty INT, lotQty int)
INSERT @CustOrder
VALUES
(
'8','Addidas Shoe',30,25
)
INSERT @CustOrder
VALUES
(
'9','Addidas Shoe',15,25
)
INSERT @CustOrder
VALUES
(
'10','Addidas Shoe',50,25
)
INSERT @CustOrder
VALUES
(
'7','Puma Shoe',40,30
)
INSERT @CustOrder
VALUES
(
'8','Puma Shoe',60,30
)
declare @Lot table (LotNo varchar(20),Seq int,ItemSize int, Product varchar(20), Qty int)
declare @RemQty int , @curQty int, @curLotQty INT
SET @RemQty=0;
declare @ItemSize int, @Product varchar(20), @Qty int, @lotQty int, @oldProduct varchar(20)
declare @lno int
set @lno = 0
declare @seq int
set @seq = 1
declare table_cursor cursor local fast_forward for
select
ItemSize, Product, Qty, lotQty
from @CustOrder
order by Product, Itemsize
open table_cursor
while 1 = 1
begin
if @RemQty <= 0
begin
fetch table_cursor into @ItemSize, @Product, @Qty, @lotQty
if @@fetch_status <> 0 break
if @oldProduct <> @Product AND @oldProduct IS NOT NULL
set @lno = @lno + 1
if @oldProduct <> @Product or @oldProduct is null
begin
select @oldProduct = @Product
select @RemQty = 0 set @seq = 1
END
end
if @RemQty < 0 and abs(@RemQty) < @lotQty
BEGIN
select @curLotQty = abs(@RemQty)
set @seq = @seq + 1
END
ELSE
select @curLotQty = @lotQty
if @RemQty > 0 and @RemQty < @Qty
BEGIN
select @curQty = @RemQty
set @lno = @lno + 1
set @seq = 1
END
ELSE
select @curQty = @Qty
select @RemQty = @curQty - @curLotQty
insert into @Lot
SELECT '100'+CAST(@lno as varchar), @seq, @ItemSize, @Product, case when @curQty > @curLotQty then @curLotQty else @curQty END
end
close table_cursor
deallocate table_cursor
SELECT LotNo [Lot No.], Seq [Sl. No], Product, ItemSize [Size], Qty from @Lot order by Product, Itemsize
这篇关于SQL脚本根据条件将单个数据行拆分为多个数据行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文