/*原资料
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
*/希望得到以下 结果/*
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开始
*/数据环境为 SQL2000
用存储过程我就帮你写写。呵呵。
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 #A WHERE [Bno]=a.[Bno] AND [Rno]<=a.[Rno]),RTRIM(ROW_NUMBER()OVER(PARTITION BY [Bno] ORDER BY [Rno]))+'/'+RTRIM(COUNT(*)OVER(PARTITION BY [Bno]))
from #A AS a
Select *,(SELECT SUM([width]) FROM pssl2 WHERE [Bno]=a.[Bno] AND [Rno]<=a.[Rno]),RTRIM(ROW_NUMBER()OVER(PARTITION BY [Bno] ORDER BY [Rno]))+'/'+RTRIM(COUNT(*)OVER(PARTITION BY [Bno]))-----'ROW_NUMBER' 不是可以识别的 函数名。from pssl2 AS a
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 12,N'A11083114B5',110901608,240
Go
SELECT
t2.*,
RTRIM((t2.row-1)%t1.GroupCount+1)+'/'+RTRIM(t1.GroupCount) AS Frac,
CEILING(t2.row*1.0/t1.GroupCount) AS GROUPCol
INTO #
FROM
(SELECT [Bno],CEILING(COUNT(*)*1.0/2)AS GroupCount FROM #A GROUP BY [Bno]) AS t1
INNER JOIN
(SELECT *,(SELECT COUNT(*) FROM #A WHERE Bno=a.Bno AND [Rno]<=a.[Rno]) as row FROM #A AS a) AS t2 ON t1.Bno=t2.Bno
SELECT
id,bno,rno,width
, bqty=(SELECT SUM([width]) FROM # WHERE Bno=a.Bno AND GROUPCol=a.GROUPCol AND row<=a.row)
, Frac
FROM # AS a
/*
id bno rno width bqty Frac
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 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
*/
中国风的语句有问题:
操作数类型冲突: int 与 void type 不兼容
以下是原表建数据语句: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
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
go
select *,width as bqty,1 as ium,id as flg into # from pssl2 a where not exists(select 1 from pssl2 where BNO=a.BNO and id<a.id )
go
while exists(select 1 from pssl2 a where not exists(select 1 from # where id=a.id))
insert into #
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 # a inner join pssl2 b on a.bno=b.bno and a.id=b.id-1
where not exists(select 1 from # where ID=b.id)select ID,bno,rno,width,bqty,ium,LTRIM(ium)+'/'+(select ltrim(max(ium)) from # where flg=a.flg)Frac from # a order by id
go/*
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,#