003-004 这个是根据什么得到的? 是根据数据里的 form to 2 3 3-2=1得到的,也就说该行记录,如果有两条的话,写成比如 003 - 004 如果数据是这样的话 form to 2 就显示为003
--测试数据 create table tb([from] int, [to] int) insert tb select null,1 union all select 1, 1 union all select 2, 3 union all select 1, null go-- 处理 select id=identity(int),* into # from tb select right(1001+isnull((select sum(isnull([to],[from])-isnull([from],[to])+1) from # where id<a.id),0),3) +case when [to]-[from]>0 then '-' +right([to]-[from]+1001+isnull((select sum(isnull([to],[from])-isnull([from],[to])+1) from # where id<a.id),0),3) else '' end [from], [to] from # a drop table # godrop table tb/*--结果 from to ------------- ----------- 001 1 002 1 003-004 3 005 NULL(所影响的行数为 4 行) --*/
--测试数据 create table tb([from] int, [to] int) insert tb select null,1 union all select 1, 1 union all select 2, 3 union all select 1, null go-- 处理 select id=identity(int),* into # from tb select right(1001+isnull((select sum(isnull([to],[from])-isnull([from],[to])+1) from # where id<a.id),0),3) +case when [to]-[from]>0 then '-' +right([to]-[from]+1001+isnull((select sum(isnull([to],[from])-isnull([from],[to])+1) from # where id<a.id),0),3) else '' end, [from], [to] from # a drop table # godrop table tb/*--结果 from to ------------- ----------- ----------- 001 NULL 1 002 1 1 003-004 2 3 005 1 NULL(所影响的行数为 4 行)--*/
是根据数据里的
form to
2 3
3-2=1得到的,也就说该行记录,如果有两条的话,写成比如
003 - 004
如果数据是这样的话
form to
2
就显示为003
create table tb([from] int, [to] int)
insert tb select null,1
union all select 1, 1
union all select 2, 3
union all select 1, null
go-- 处理
select id=identity(int),* into # from tb
select right(1001+isnull((select sum(isnull([to],[from])-isnull([from],[to])+1) from # where id<a.id),0),3)
+case
when [to]-[from]>0 then '-'
+right([to]-[from]+1001+isnull((select sum(isnull([to],[from])-isnull([from],[to])+1) from # where id<a.id),0),3)
else '' end
[from], [to]
from # a
drop table #
godrop table tb/*--结果
from to
------------- -----------
001 1
002 1
003-004 3
005 NULL(所影响的行数为 4 行)
--*/
create table tb([from] int, [to] int)
insert tb select null,1
union all select 1, 1
union all select 2, 3
union all select 1, null
go-- 处理
select id=identity(int),* into # from tb
select right(1001+isnull((select sum(isnull([to],[from])-isnull([from],[to])+1) from # where id<a.id),0),3)
+case
when [to]-[from]>0 then '-'
+right([to]-[from]+1001+isnull((select sum(isnull([to],[from])-isnull([from],[to])+1) from # where id<a.id),0),3)
else '' end,
[from], [to]
from # a
drop table #
godrop table tb/*--结果
from to
------------- ----------- -----------
001 NULL 1
002 1 1
003-004 2 3
005 1 NULL(所影响的行数为 4 行)--*/