~~~~~~~这样的显示怎样才能实现？
原始数据:
PNAMEPNO Boxes
P4EE1041201017006
P4EE1341203023003
P4EE5.041227017005
P4RM641530296002

需求显示：

PackingNOPNAMEPNO Boxes
1~6 P4EE1041201017006
7~9 P4EE1341203023003
10~14 P4EE5.041227017005
15~16 P4RM641530296002

请问这种显示需求怎样处理，谢谢！

[解决办法]

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 allselect 'P4EE13',4120302300,3 union allselect 'P4EE5.0',4122701700,5 union allselect 'P4RM6',4153029600,2with tas(select ROW_NUMBER()over(order by getdate()) as id,*,[Boxes] as totalfrom tbl),m as(select id,[PNAME],[PNO],[Boxes],total from t where id=1union allselect a.id,a.[PNAME],a.[PNO],a.[Boxes],b.total+a.[Boxes] from t ainner 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    Boxes0-6    P4EE10    4120101700    66-9    P4EE13    4120302300    39-14    P4EE5.0    4122701700    514-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 allselect 'P4EE13',4120302300,3 union allselect 'P4EE5.0',4122701700,5 union allselect '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           67~9                       P4EE13  4120302300           310~14                     P4EE5.0 4122701700           515~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)goselect 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,Boxesfrom tb tdrop table tb/*PackingNO                                                     PNAME      PNO                  Boxes       ------------------------- ---------- -------------------- ----------- 1~6                                                           P4EE10     4120101700           67~9                                                           P4EE13     4120302300           310~14                                                         P4EE5.0    4122701700           515~16                                                         P4RM6      4153029600           2（所影响的行数为 4 行）*/