1。先强调数据环境是SQL2000。
2。建表及插入数据SQL语句:CREATE TABLE pssl2(
id int,
BNO varchar(20),
RNO varchar(20),
WIDTH int)INSERT INTO pssl2(id,BNO,RNO,width)
select 1,'F11082803F1','110901001',850
UNION ALL
select 2,'F11082803F1','110901002',700
UNION ALL
select 3,'F11082803F1','110901003',850
UNION ALL
select 4,'F11082803F1','110901004',700
UNION ALL
select 5,'A11083114B5','110901601',440
UNION ALL
select 6,'A11083114B5','110901602',440
UNION ALL
select 7,'A11083114B5','110901603',440
UNION ALL
select 8,'A11083114B5','110901604',240
UNION ALL
select 9,'A11083114B5','110901605',440
UNION ALL
select 10,'A11083114B5','110901606',440
UNION ALL
select 11,'A11083114B5','110901607',440
UNION ALL
select 12,'A11083114B5','110901608',2403。原数据:/*原资料
id Bno Rno width
------------------------------------------------
1 F11082803F1 110901001 850
2 F11082803F1 110901002 700
3 F11082803F1 110901003 850
4 F11082803F1 110901004 700
5 A11083114B5 110901601 440
6 A11083114B5 110901602 440
7 A11083114B5 110901603 440
8 A11083114B5 110901604 240
9 A11083114B5 110901605 440
10 A11083114B5 110901606 440
11 A11083114B5 110901607 440
12 A11083114B5 110901608 240
*/4。需得到以下结果:/*
id bno rno width bqty Frac
----------- -------------------- -------------------- ----------- ----------- -------------------------
1 F11082803F1 110901001 850 850 1/2
2 F11082803F1 110901002 700 1550 2/2
3 F11082803F1 110901003 850 850 1/2
4 F11082803F1 110901004 700 1550 2/2
5 A11083114B5 110901601 440 440 1/4
6 A11083114B5 110901602 440 880 2/4
7 A11083114B5 110901603 440 1320 3/4
8 A11083114B5 110901604 240 1560 4/4
9 A11083114B5 110901605 440 440 1/4
10 A11083114B5 110901606 440 880 2/4
11 A11083114B5 110901607 440 1320 3/4
12 A11083114B5 110901608 240 1560 4/4
对比规则是:
1.当前记录中的 BNO字段如果与上条记录的如果相同
2.BQty显示当前记录中的WIDTH字段加上上条记录的 WIDTH
3.ium 显示操作次数,如果当前记录中的WIDTH字段加上上条记录的 WIDTH >=1550则下条记录从1开始*/
2。建表及插入数据SQL语句:CREATE TABLE pssl2(
id int,
BNO varchar(20),
RNO varchar(20),
WIDTH int)INSERT INTO pssl2(id,BNO,RNO,width)
select 1,'F11082803F1','110901001',850
UNION ALL
select 2,'F11082803F1','110901002',700
UNION ALL
select 3,'F11082803F1','110901003',850
UNION ALL
select 4,'F11082803F1','110901004',700
UNION ALL
select 5,'A11083114B5','110901601',440
UNION ALL
select 6,'A11083114B5','110901602',440
UNION ALL
select 7,'A11083114B5','110901603',440
UNION ALL
select 8,'A11083114B5','110901604',240
UNION ALL
select 9,'A11083114B5','110901605',440
UNION ALL
select 10,'A11083114B5','110901606',440
UNION ALL
select 11,'A11083114B5','110901607',440
UNION ALL
select 12,'A11083114B5','110901608',2403。原数据:/*原资料
id Bno Rno width
------------------------------------------------
1 F11082803F1 110901001 850
2 F11082803F1 110901002 700
3 F11082803F1 110901003 850
4 F11082803F1 110901004 700
5 A11083114B5 110901601 440
6 A11083114B5 110901602 440
7 A11083114B5 110901603 440
8 A11083114B5 110901604 240
9 A11083114B5 110901605 440
10 A11083114B5 110901606 440
11 A11083114B5 110901607 440
12 A11083114B5 110901608 240
*/4。需得到以下结果:/*
id bno rno width bqty Frac
----------- -------------------- -------------------- ----------- ----------- -------------------------
1 F11082803F1 110901001 850 850 1/2
2 F11082803F1 110901002 700 1550 2/2
3 F11082803F1 110901003 850 850 1/2
4 F11082803F1 110901004 700 1550 2/2
5 A11083114B5 110901601 440 440 1/4
6 A11083114B5 110901602 440 880 2/4
7 A11083114B5 110901603 440 1320 3/4
8 A11083114B5 110901604 240 1560 4/4
9 A11083114B5 110901605 440 440 1/4
10 A11083114B5 110901606 440 880 2/4
11 A11083114B5 110901607 440 1320 3/4
12 A11083114B5 110901608 240 1560 4/4
对比规则是:
1.当前记录中的 BNO字段如果与上条记录的如果相同
2.BQty显示当前记录中的WIDTH字段加上上条记录的 WIDTH
3.ium 显示操作次数,如果当前记录中的WIDTH字段加上上条记录的 WIDTH >=1550则下条记录从1开始*/
id int,
BNO varchar(20),
RNO varchar(20),
WIDTH int)INSERT INTO pssl2(id,BNO,RNO,width)
select 1,'F11082803F1','110901001',850
UNION ALL
select 2,'F11082803F1','110901002',700
UNION ALL
select 3,'F11082803F1','110901003',850
UNION ALL
select 4,'F11082803F1','110901004',700
UNION ALL
select 5,'A11083114B5','110901601',440
UNION ALL
select 6,'A11083114B5','110901602',440
UNION ALL
select 7,'A11083114B5','110901603',440
UNION ALL
select 8,'A11083114B5','110901604',240
UNION ALL
select 9,'A11083114B5','110901605',440
UNION ALL
select 10,'A11083114B5','110901606',440
UNION ALL
select 11,'A11083114B5','110901607',440
UNION ALL
select 12,'A11083114B5','110901608',240--DROP TABLE #
SELECT *,Frac=0,bqty=0,TotalCount=0 INTO # FROM pssl2 ORDER BY BNO desc,RNOgoDECLARE @i INT,@bno varchar(20),@j INT,@bqty INTUPDATE #
SET @bqty=bqty=CASE WHEN BNO=@bno AND (@bqty-width)<1550 THEN @bqty+width ELSE width END,
@i=Frac=CASE WHEN BNO=@bno AND (@bqty-width)<1550 THEN @i+1 ELSE 1 end,
@bno=BNO UPDATE a
SET TotalCount=(SELECT COUNT(*) FROM # WHERE Bno=a.Bno AND Frac=1 AND Rno<=a.Rno)
FROM # AS aSELECT
t1.id,
t1.bno,
t1.rno,
t1.width
bqty,
Frac=RTRIM(Frac)+'/'+RTRIM(groupCol)
FROM
# AS t1
INNER JOIN
(SELECT Bno,TotalCount,COUNT(*) AS groupCol FROM # GROUP BY Bno,TotalCount)t2 ON t1.Bno=t2.Bno AND t1.TotalCount=t2.TotalCount
ORDER BY t1.ID/*
id bno rno bqty Frac
1 F11082803F1 110901001 850 1/2
2 F11082803F1 110901002 700 2/2
3 F11082803F1 110901003 850 1/2
4 F11082803F1 110901004 700 2/2
5 A11083114B5 110901601 440 1/4
6 A11083114B5 110901602 440 2/4
7 A11083114B5 110901603 440 3/4
8 A11083114B5 110901604 240 4/4
9 A11083114B5 110901605 440 1/4
10 A11083114B5 110901606 440 2/4
11 A11083114B5 110901607 440 3/4
12 A11083114B5 110901608 240 4/4
*/
select 13,'A11083114B5','110901609',850
/*
id bno rno bqty frac
-------------------------------------------------------
1 F11082803F1 110901001 850 1/2
2 F11082803F1 110901002 700 2/23 F11082803F1 110901003 850 1/2
4 F11082803F1 110901004 700 2/25 A11083114B5 110901601 440 1/4
6 A11083114B5 110901602 440 2/4
7 A11083114B5 110901603 440 3/4
8 A11083114B5 110901604 240 4/49 A11083114B5 110901605 440 1/4
10 A11083114B5 110901606 440 2/4
11 A11083114B5 110901607 440 3/4
12 A11083114B5 110901608 240 4/413 A11083114B5 110901609 850 1/2本行width加上行width>=1550,frac要重新计算
*/
正确的应为:/*
id bno rno bqty frac
-------------------------------------------------------
1 F11082803F1 110901001 850 1/2
2 F11082803F1 110901002 700 2/23 F11082803F1 110901003 850 1/2
4 F11082803F1 110901004 700 2/25 A11083114B5 110901601 440 1/4
6 A11083114B5 110901602 440 2/4
7 A11083114B5 110901603 440 3/4
8 A11083114B5 110901604 240 4/49 A11083114B5 110901605 440 1/4
10 A11083114B5 110901606 440 2/4
11 A11083114B5 110901607 440 3/4
12 A11083114B5 110901608 240 4/413 A11083114B5 110901609 850 1/2
*/
Create TABLE pssl2(
id int,
BNO varchar(20),
RNO varchar(20),
WIDTH int)INSERT INTO pssl2(id,BNO,RNO,width)
select 1,'F11082803F1','110901001',850
UNION ALL
select 2,'F11082803F1','110901002',700
UNION ALL
select 3,'F11082803F1','110901003',850
UNION ALL
select 4,'F11082803F1','110901004',700
UNION ALL
select 5,'A11083114B5','110901601',440
UNION ALL
select 6,'A11083114B5','110901602',440
UNION ALL
select 7,'A11083114B5','110901603',440
UNION ALL
select 8,'A11083114B5','110901604',240
UNION ALL
select 9,'A11083114B5','110901605',440
UNION ALL
select 10,'A11083114B5','110901606',440
UNION ALL
select 11,'A11083114B5','110901607',440
UNION ALL
select 12,'A11083114B5','110901608',240
UNION ALL
SELECT 13,'A11083114B5','110901609',850
go
--DROP TABLE #,pssl2
SELECT *,Frac=0,bqty=0,TotalCount=0 INTO # FROM pssl2 ORDER BY BNO desc,RNOgoDECLARE @i INT,@bno varchar(20),@bqty INTUPDATE #
SET bqty=@bqty,
@i=Frac=CASE WHEN BNO=@bno AND @bqty<1550 THEN @i+1 ELSE 1 end,
@bqty=CASE WHEN BNO=@bno AND @bqty<1550 THEN @bqty+width ELSE width END,
@bno=BNO UPDATE a
SET TotalCount=(SELECT COUNT(*) FROM # WHERE Bno=a.Bno AND Frac=1 AND Rno<=a.Rno)
FROM # AS a
SELECT
t1.id,
t1.bno,
t1.rno,
t1.width,
bqty,
Frac=RTRIM(Frac)+'/'+RTRIM(groupCol)
FROM
# AS t1
INNER JOIN
(SELECT Bno,TotalCount,COUNT(*) AS groupCol FROM # GROUP BY Bno,TotalCount)t2 ON t1.Bno=t2.Bno AND t1.TotalCount=t2.TotalCount
ORDER BY t1.ID/*
id bno rno width bqty Frac
1 F11082803F1 110901001 850 850 1/2
2 F11082803F1 110901002 700 1550 2/2
3 F11082803F1 110901003 850 850 1/2
4 F11082803F1 110901004 700 1550 2/2
5 A11083114B5 110901601 440 440 1/4
6 A11083114B5 110901602 440 880 2/4
7 A11083114B5 110901603 440 1320 3/4
8 A11083114B5 110901604 240 1560 4/4
9 A11083114B5 110901605 440 440 1/4
10 A11083114B5 110901606 440 880 2/4
11 A11083114B5 110901607 440 1320 3/4
12 A11083114B5 110901608 240 1560 4/4
13 A11083114B5 110901609 850 850 1/1
*/
id int,
BNO varchar(20),
RNO varchar(20),
WIDTH int)INSERT INTO pssl2(id,BNO,RNO,width)
select 1,'F11082803F1','110901001',850
UNION ALL
select 2,'F11082803F1','110901002',700
UNION ALL
select 3,'F11082803F1','110901003',850
UNION ALL
select 4,'F11082803F1','110901004',700
UNION ALL
select 5,'A11083114B5','110901601',440
UNION ALL
select 6,'A11083114B5','110901602',440
UNION ALL
select 7,'A11083114B5','110901603',440
UNION ALL
select 8,'A11083114B5','110901604',240
UNION ALL
select 9,'A11083114B5','110901605',440
UNION ALL
select 10,'A11083114B5','110901606',440
UNION ALL
select 11,'A11083114B5','110901607',440
UNION ALL
select 12,'A11083114B5','110901608',240create table #t
(id int,BNO varchar(20),RNO varchar(20),WIDTH int,bqty int,ium int,Frac varchar(10))declare @id int,@BNO varchar(20),@RNO varchar(20),@WIDTH int,@t int,@tBNO varchar(20),@i int
declare ap scroll cursor for select * from pssl2
open apfetch first from ap into @id,@BNO,@RNO,@WIDTH
while(@@FETCH_STATUS <> -1)
begin
if @id=1
insert into #t select @id,@BNO,@RNO,@WIDTH,@WIDTH,1,'1/'
else
select @tBNO=BNO from pssl2 where id=@id-1
if @tBNO<>@BNO
insert into #t select @id,@BNO,@RNO,@WIDTH,@WIDTH,1,'1/'
else
begin
select @t=bqty from #t where id=@id-1
if @t>=1550
insert into #t select @id,@BNO,@RNO,@WIDTH,@WIDTH,1,'1/'
else
insert into #t select @id,@BNO,@RNO,@WIDTH,@t+@WIDTH,ium+1,cast(ium+1 as varchar)+'/' from #t where id=@id-1
end
fetch next from ap into @id,@BNO,@RNO,@WIDTH
endclose ap
deallocate apupdate a
set Frac=Frac+cast(mium as varchar)
from #t a
inner join
(select BNO,max(ium) mium
from #t
group by BNO) b on a.BNO=b.BNOselect * from #tid BNO RNO WIDTH bqty ium Frac
----------- -------------------- -------------------- ----------- ----------- ----------- ----------
1 F11082803F1 110901001 850 850 1 1/2
2 F11082803F1 110901002 700 1550 2 2/2
3 F11082803F1 110901003 850 850 1 1/2
4 F11082803F1 110901004 700 1550 2 2/2
5 A11083114B5 110901601 440 440 1 1/4
6 A11083114B5 110901602 440 880 2 2/4
7 A11083114B5 110901603 440 1320 3 3/4
8 A11083114B5 110901604 240 1560 4 4/4
9 A11083114B5 110901605 440 440 1 1/4
10 A11083114B5 110901606 440 880 2 2/4
11 A11083114B5 110901607 440 1320 3 3/4
12 A11083114B5 110901608 240 1560 4 4/4
create table pssl2(
id int,
BNO varchar(20),
RNO varchar(20),
WIDTH int)INSERT INTO pssl2(id,BNO,RNO,width)
select 1,'F11082803F1','110901001',850
UNION ALL
select 2,'F11082803F1','110901002',700
UNION ALL
select 3,'F11082803F1','110901003',850
UNION ALL
select 4,'F11082803F1','110901004',700
UNION ALL
select 5,'A11083114B5','110901601',440
UNION ALL
select 6,'A11083114B5','110901602',440
UNION ALL
select 7,'A11083114B5','110901603',440
UNION ALL
select 8,'A11083114B5','110901604',240
UNION ALL
select 9,'A11083114B5','110901605',440
UNION ALL
select 10,'A11083114B5','110901606',440
UNION ALL
select 11,'A11083114B5','110901607',440
UNION ALL
select 12,'A11083114B5','110901608',240create table #t
(id int,BNO varchar(20),RNO varchar(20),WIDTH int,bqty int,ium int,Frac varchar(10))declare @id int,@BNO varchar(20),@RNO varchar(20),@WIDTH int,@t int,@tBNO varchar(20),@i int,@b int
declare ap scroll cursor for select * from pssl2
open apfetch first from ap into @id,@BNO,@RNO,@WIDTH
while(@@FETCH_STATUS <> -1)
begin
if @id=1
begin
insert into #t select @id,@BNO,@RNO,@WIDTH,@WIDTH,1,'1/'
select @i=1,@b=@id
end
else
select @tBNO=BNO from pssl2 where id=@id-1
if @tBNO<>@BNO
begin
insert into #t select @id,@BNO,@RNO,@WIDTH,@WIDTH,1,'1/'
update #t set Frac=Frac+cast(@i as varchar) where id<@id and id>=@b
select @i=1,@b=@id
end
else
begin
select @t=bqty from #t where id=@id-1
if @t>=1550
begin
insert into #t select @id,@BNO,@RNO,@WIDTH,@WIDTH,1,'1/'
update #t set Frac=Frac+cast(@i as varchar) where id<@id and id>=@b
select @i=1,@b=@id
end
else
begin
insert into #t select @id,@BNO,@RNO,@WIDTH,@t+@WIDTH,ium+1,cast(ium+1 as varchar)+'/' from #t where id=@id-1
select @i=ium+1 from #t where id=@id-1
end
end
fetch next from ap into @id,@BNO,@RNO,@WIDTH
endclose ap
deallocate apupdate #t set Frac=Frac+cast(@i as varchar) where id>=@bselect * from #tid BNO RNO WIDTH bqty ium Frac
----------- -------------------- -------------------- ----------- ----------- ----------- ----------
1 F11082803F1 110901001 850 850 1 1/2
2 F11082803F1 110901002 700 1550 2 2/2
3 F11082803F1 110901003 850 850 1 1/2
4 F11082803F1 110901004 700 1550 2 2/2
5 A11083114B5 110901601 440 440 1 1/4
6 A11083114B5 110901602 440 880 2 2/4
7 A11083114B5 110901603 440 1320 3 3/4
8 A11083114B5 110901604 240 1560 4 4/4
9 A11083114B5 110901605 440 440 1 1/4
10 A11083114B5 110901606 440 880 2 2/4
11 A11083114B5 110901607 440 1320 3 3/4
12 A11083114B5 110901608 240 1560 4 4/4
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[Bno] nvarchar(11),[Rno] int,[width] int)
Insert #A
select 1,N'F11082803F1',110901001,850 union all
select 2,N'F11082803F1',110901002,700 union all
select 3,N'F11082803F1',110901003,850 union all
select 4,N'F11082803F1',110901004,700 union all
select 5,N'A11083114B5',110901601,440 union all
select 6,N'A11083114B5',110901602,440 union all
select 7,N'A11083114B5',110901603,440 union all
select 8,N'A11083114B5',110901604,240 union all
select 9,N'A11083114B5',110901605,440 union all
select 10,N'A11083114B5',110901606,440 union all
select 11,N'A11083114B5',110901607,440
Go
SELECT *,(SELECT SUM(WIDTH) FROM
(SELECT *,
((SELECT SUM(width) FROM #A WHERE Bno=A.Bno AND Rno<=A.Rno )-WIDTH)/1550 as grp
FROM #A A) T WHERE BNO=A.BNO AND GRP=A.GRP AND RNO<=A.RNO) AS BNQY,
(SELECT COUNT(1) FROM
(SELECT *,
((SELECT SUM(width) FROM #A WHERE Bno=A.Bno AND Rno<=A.Rno )-WIDTH)/1550 as grp
FROM #A A) T WHERE BNO=A.BNO AND GRP=A.GRP AND RNO<=A.RNO) AS RN,
(SELECT COUNT(1) FROM
(SELECT *,
((SELECT SUM(width) FROM #A WHERE Bno=A.Bno AND Rno<=A.Rno )-WIDTH)/1550 as grp
FROM #A A) T WHERE BNO=A.BNO AND GRP=A.GRP ) AS maxRN
FROM
(
SELECT *,
((SELECT SUM(width) FROM #A WHERE Bno=A.Bno AND Rno<=A.Rno )-WIDTH)/1550 as grp
FROM #A A) A/*id Bno Rno width grp BNQY RN maxRN
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 F11082803F1 110901001 850 0 850 1 2
2 F11082803F1 110901002 700 0 1550 2 2
3 F11082803F1 110901003 850 1 850 1 2
4 F11082803F1 110901004 700 1 1550 2 2
5 A11083114B5 110901601 440 0 440 1 4
6 A11083114B5 110901602 440 0 880 2 4
7 A11083114B5 110901603 440 0 1320 3 4
8 A11083114B5 110901604 240 0 1560 4 4
9 A11083114B5 110901605 440 1 440 1 3
10 A11083114B5 110901606 440 1 880 2 3
11 A11083114B5 110901607 440 1 1320 3 3(所影响的行数为 11 行)*/
ap0405140
的有重复值出现。roy_88
的再多插数条记录,就不正确了,违反了1550后重新计算的原则
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[Bno] nvarchar(11),[Rno] int,[width] int)
Insert #A
select 1,N'F11082803F1',110901001,850 union all
select 2,N'F11082803F1',110901002,700 union all
select 3,N'F11082803F1',110901003,850 union all
select 4,N'F11082803F1',110901004,700 union all
select 5,N'A11083114B5',110901601,440 union all
select 6,N'A11083114B5',110901602,440 union all
select 7,N'A11083114B5',110901603,440 union all
select 8,N'A11083114B5',110901604,240 union all
select 9,N'A11083114B5',110901605,440 union all
select 10,N'A11083114B5',110901606,440 union all
select 11,N'A11083114B5',110901607,440 UNION ALL
select 11,N'A11083114B5',110901608,440 UNION ALL
select 13,'A11083114B5','110901609',850Go
SELECT *,(SELECT SUM(WIDTH) FROM
(SELECT *,
((SELECT SUM(width) FROM #A WHERE Bno=A.Bno AND Rno<=A.Rno )-WIDTH)/1550 as grp
FROM #A A) T WHERE BNO=A.BNO AND GRP=A.GRP AND RNO<=A.RNO) AS BNQY,
LTRIM((SELECT COUNT(1) FROM
(SELECT *,
((SELECT SUM(width) FROM #A WHERE Bno=A.Bno AND Rno<=A.Rno )-WIDTH)/1550 as grp
FROM #A A) T WHERE BNO=A.BNO AND GRP=A.GRP AND RNO<=A.RNO) )+'\'+LTRIM(
(SELECT COUNT(1) FROM
(SELECT *,
((SELECT SUM(width) FROM #A WHERE Bno=A.Bno AND Rno<=A.Rno )-WIDTH)/1550 as grp
FROM #A A) T WHERE BNO=A.BNO AND GRP=A.GRP )) AS RN
FROM
(
SELECT *,
((SELECT SUM(width) FROM #A WHERE Bno=A.Bno AND Rno<=A.Rno )-WIDTH)/1550 as grp
FROM #A A) A/*
(所影响的行数为 13 行)id Bno Rno width grp BNQY RN
----------- ----------- ----------- ----------- ----------- ----------- -------------------------
1 F11082803F1 110901001 850 0 850 1\2
2 F11082803F1 110901002 700 0 1550 2\2
3 F11082803F1 110901003 850 1 850 1\2
4 F11082803F1 110901004 700 1 1550 2\2
5 A11083114B5 110901601 440 0 440 1\4
6 A11083114B5 110901602 440 0 880 2\4
7 A11083114B5 110901603 440 0 1320 3\4
8 A11083114B5 110901604 240 0 1560 4\4
9 A11083114B5 110901605 440 1 440 1\4
10 A11083114B5 110901606 440 1 880 2\4
11 A11083114B5 110901607 440 1 1320 3\4
11 A11083114B5 110901608 440 1 1760 4\4
13 A11083114B5 110901609 850 2 850 1\1(所影响的行数为 13 行)
--16楼的结果:
1 F11082803F1 110901001 850 850 1 1/2
1 F11082803F1 110901001 850 850 1 1/2
2 F11082803F1 110901002 700 1550 2 2/2
2 F11082803F1 110901002 700 1550 2 2/2
3 F11082803F1 110901003 850 850 1 1/2
4 F11082803F1 110901004 700 1550 2 2/2
5 A11083114B5 110901601 440 440 1 1/4
.......
-- 16楼的最后一句改为
select distinct * from #t
你的是正确的,是我看错了,我还而再测试一下,请你耐心
不是原数据重复,你用了你的语句后重复了
create table pssl2(
id int,
BNO varchar(20),
RNO varchar(20),
WIDTH int)INSERT INTO pssl2(id,BNO,RNO,width)
select 1,'F11082803F1','110901001',850
UNION ALL
select 2,'F11082803F1','110901002',700
UNION ALL
select 3,'F11082803F1','110901003',850
UNION ALL
select 4,'F11082803F1','110901004',700
UNION ALL
select 5,'A11083114B5','110901601',440
UNION ALL
select 6,'A11083114B5','110901602',440
UNION ALL
select 7,'A11083114B5','110901603',440
UNION ALL
select 8,'A11083114B5','110901604',240
UNION ALL
select 9,'A11083114B5','110901605',440
UNION ALL
select 10,'A11083114B5','110901606',440
UNION ALL
select 11,'A11083114B5','110901607',440
UNION ALL
select 12,'A11083114B5','110901608',240create table #t
(id int,BNO varchar(20),RNO varchar(20),WIDTH int,bqty int,ium int,Frac varchar(10))declare @id int,@BNO varchar(20),@RNO varchar(20),@WIDTH int,@t int,@tBNO varchar(20),@i int,@b int
declare ap scroll cursor for select * from pssl2
open apfetch first from ap into @id,@BNO,@RNO,@WIDTH
while(@@FETCH_STATUS <> -1)
begin
if @id=1
begin
insert into #t select @id,@BNO,@RNO,@WIDTH,@WIDTH,1,'1/'
select @i=1,@b=@id
end
else
begin
select @tBNO=BNO from pssl2 where id=@id-1
if @tBNO<>@BNO
begin
insert into #t select @id,@BNO,@RNO,@WIDTH,@WIDTH,1,'1/'
update #t set Frac=Frac+cast(@i as varchar) where id<@id and id>=@b
select @i=1,@b=@id
end
else
begin
select @t=bqty from #t where id=@id-1
if @t>=1550
begin
insert into #t select @id,@BNO,@RNO,@WIDTH,@WIDTH,1,'1/'
update #t set Frac=Frac+cast(@i as varchar) where id<@id and id>=@b
select @i=1,@b=@id
end
else
begin
insert into #t select @id,@BNO,@RNO,@WIDTH,@t+@WIDTH,ium+1,cast(ium+1 as varchar)+'/' from #t where id=@id-1
select @i=ium+1 from #t where id=@id-1
end
end
end
fetch next from ap into @id,@BNO,@RNO,@WIDTH
endclose ap
deallocate apupdate #t set Frac=Frac+cast(@i as varchar) where id>=@bselect * from #tid BNO RNO WIDTH bqty ium Frac
----------- -------------------- -------------------- ----------- ----------- ----------- ----------
1 F11082803F1 110901001 850 850 1 1/2
2 F11082803F1 110901002 700 1550 2 2/2
3 F11082803F1 110901003 850 850 1 1/2
4 F11082803F1 110901004 700 1550 2 2/2
5 A11083114B5 110901601 440 440 1 1/4
6 A11083114B5 110901602 440 880 2 2/4
7 A11083114B5 110901603 440 1320 3 3/4
8 A11083114B5 110901604 240 1560 4 4/4
9 A11083114B5 110901605 440 440 1 1/4
10 A11083114B5 110901606 440 880 2 2/4
11 A11083114B5 110901607 440 1320 3 3/4
12 A11083114B5 110901608 240 1560 4 4/4