日期:2014-05-18  浏览次数:20540 次

~~~~~~~这样的显示怎样才能实现?
原始数据:
PNAME PNO Boxes
P4EE10 4120101700 6
P4EE13 4120302300 3
P4EE5.0 4122701700 5
P4RM6 4153029600 2

需求显示:

PackingNO PNAME PNO Boxes
1~6 P4EE10 4120101700 6
7~9 P4EE13 4120302300 3
10~14 P4EE5.0 4122701700 5
15~16 P4RM6 4153029600 2

请问这种显示需求怎样处理,谢谢!

------解决方案--------------------
SQL code

--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([PNAME] varchar(7),[PNO] bigint,[Boxes] int)
insert [tbl]
select 'P4EE10',4120101700,6 union all
select 'P4EE13',4120302300,3 union all
select 'P4EE5.0',4122701700,5 union all
select 'P4RM6',4153029600,2

with t
as(
select ROW_NUMBER()over(order by getdate()) as id,*,[Boxes] as total
from tbl
),
m as(
select id,[PNAME],[PNO],[Boxes],total from t where id=1
union all
select a.id,a.[PNAME],a.[PNO],a.[Boxes],b.total+a.[Boxes] from t a
inner join m b on a.id=b.id+1
),
n as(
select a.id,a.PNAME,a.PNO,a.Boxes,a.total,isnull(b.total,0) as newtotal
 from m a left join m b on a.id=b.id+1
 )
 
 select ltrim(newtotal)+'-'+ltrim(total) as [range],
 [PNAME],[PNO],[Boxes] from n
 
 /*
 range    PNAME    PNO    Boxes
0-6    P4EE10    4120101700    6
6-9    P4EE13    4120302300    3
9-14    P4EE5.0    4122701700    5
14-16    P4RM6    4153029600    2
 */

------解决方案--------------------
SQL code
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([PNAME] varchar(7),[PNO] bigint,[Boxes] int)
insert [tb]
select 'P4EE10',4120101700,6 union all
select 'P4EE13',4120302300,3 union all
select 'P4EE5.0',4122701700,5 union all
select 'P4RM6',4153029600,2
--------------开始查询--------------------------

select ltrim((select isnull(sum([Boxes]),0)+1 from tb where [PNO]<t.[PNO]))+'~'+
 ltrim((select isnull(sum([Boxes]),0) from tb where [PNO]<=t.[PNO])) as PackingNO,
* from [tb] t
----------------结果----------------------------
/* 
4 行受影响)
PackingNO                 PNAME   PNO                  Boxes
------------------------- ------- -------------------- -----------
1~6                       P4EE10  4120101700           6
7~9                       P4EE13  4120302300           3
10~14                     P4EE5.0 4122701700           5
15~16                     P4RM6   4153029600           2

(4 行受影响)


*/

------解决方案--------------------
--如果为sql 2000,建议加上个序列号,可如下:

SQL code
create table tb(id int,PNAME varchar(10), PNO varchar(20),Boxes int)
insert into tb values(1,'P4EE10' , '4120101700', 6)
insert into tb values(2,'P4EE13' , '4120302300', 3)
insert into tb values(3,'P4EE5.0', '4122701700', 5)
insert into tb values(4,'P4RM6'  , '4153029600', 2)
go

select cast(isnull((select sum(Boxes) from tb where id < t.id),0) + 1 as varchar) + '~' + 
       cast(isnull((select sum(Boxes) from tb where id <= t.id),0) as varchar) as PackingNO,
       PNAME,PNO,Boxes
from tb t

drop table tb

/*

PackingNO                                                     PNAME      PNO                  Boxes       
------------------------- ---------- -------------------- ----------- 
1~6                                                           P4EE10     4120101700           6
7~9                                                           P4EE13     4120302300           3
10~14                                                         P4EE5.0    4122701700           5
15~16                                                         P4RM6      4153029600           2

(所影响的行数为 4 行)
*/