1.前题有这么一个资料表(pssl2):---建表语句: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
/*
显示资料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
*/
2.需要select 得出以下结果:id 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
3.规则为:
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
/*
显示资料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
*/
2.需要select 得出以下结果:id 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
3.规则为:
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
go
;with cte as (
select ID,bno,rno,width,width as bqty,1 as ium,id as flg from pssl2 where ID=1
union all
select b.id,b.bno,b.rno,b.width,
case when a.bqty<1550 then a.bqty+b.width else b.width end,
case when a.bqty<1550 then a.ium+1 else 1 end,
case when a.bqty<1550 then a.flg else b.id end
from cte a inner join pssl2 b on a.id=b.id-1
)
select a.id,a.bno,a.rno,a.width,a.bqty,LTRIM(a.ium)+'/'+LTRIM(b.ium) Frac
from cte a inner join(
select flg,id,ium from cte where bqty>=1550
)b on a.id>=b.flg and a.id<=b.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(12 行受影响)*/
go
drop 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
go
;with cte as (
select ID,bno,rno,width,width as bqty,1 as ium,id as flg from pssl2 where ID=1
union all
select b.id,b.bno,b.rno,b.width,
case when a.bqty<1550 then a.bqty+b.width else b.width end,
case when a.bqty<1550 then a.ium+1 else 1 end,
case when a.bqty<1550 then a.flg else b.id end
from cte a inner join pssl2 b on a.id=b.id-1
)
select a.id,a.bno,a.rno,a.width,a.bqty,a.ium,LTRIM(a.ium)+'/'+LTRIM(b.ium) Frac
from cte a inner join(
select flg,id,ium from cte where bqty>=1550
)b on a.id>=b.flg and a.id<=b.id
/*
id 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(12 行受影响)*/
go
drop 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',140
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
go
如果数据是这样呢?结果不对哦。呵呵。