--测试环境declare @t table(id varchar(10),批次 int) insert into @t select '10001',1 union all select '10002',1 union all select '10003',1 union all select '10004',1 union all select '10005',1 union all select '10006',1 union all select '10007',1 union all select '10008',1 union all select '10009',1 union all select '10010',1 union all select '10011',1 union all select '20001',2 union all select '20002',2 union all select '20003',3--查询 select id=case when b.id=b.id1 then b.id else b.id+'-'+b.id1 end,批次=a.批次 from @t a,( select id,id1=(select min(id) from @t a1 where id>=a.id and not exists( select * from @t where 批次=a1.批次 and id=a1.id+1)) from @t a where not exists( select * from @t where 批次=a.批次 and id=a.id-1))b where a.id between b.id and b.id1 group by b.id,b.id1,a.批次--结果 id 批次 --------------------- ----------- 10001-10011 1 20001-20002 2 20003 3(所影响的行数为 3 行)
select id + case when num=1 then '' else cast(num as varchar) end,批次 from (select id,count(*) as num,批次 from 表 group by id,批次) T
--更正显示结果. declare @t table(id varchar(10),批次 int) insert into @t select '10001',1 union all select '10002',1 union all select '10003',1 union all select '10004',1 union all select '10005',1 union all select '10006',1 union all select '10007',1 union all select '10008',1 union all select '10009',1 union all select '10010',1 union all select '10011',1 union all select '20001',2 union all select '20002',2 union all select '20003',3 --查询处理 select id=case when b.id=b.id1 then b.id else b.id+'-'+cast(convert(int,b.id1)-convert(int,b.id)+1 as varchar(10)) end, 批次=a.批次 from @t a,( select id,id1=(select min(id) from @t a1 where id>=a.id and not exists( select * from @t where 批次=a1.批次 and id=a1.id+1)) from @t a where not exists( select * from @t where 批次=a.批次 and id=a.id-1))b where a.id between b.id and b.id1 group by b.id,b.id1,a.批次--结果 id 批次 --------------------- ----------- 10001-11 1 20001-2 2 20003 3(所影响的行数为 3 行)
create table a(id int,pici int) insert into a select 10001,1 union all select 10002,1 union all select 10003,1 union all select 10004,1 union all select 10005,1 union all select 10006,1 union all select 10007,1 union all select 10008,1 union all select 10009,1 union all select 10010,1 union all select 10011,1 union all select 20001,2 union all select 20002,2 union all select 10003,3 select replace(cast(min(id) as char(5))+'-'+right(cast(max(id) as char(5)),2),'-0','-')as id,pici from a group by pici 結果: id pici ---------------- 10001-11 1 20001-2 2 10003-3 3
declare @t table(id varchar(10),批次 int) insert into @t select '10001',1 union all select '10002',1 union all select '10003',1 union all select '10004',1 union all select '10005',1 union all select '10006',1 union all select '10007',1 union all select '10008',1 union all select '10009',1 union all select '10010',1 union all select '10011',1 union all select '20001',2 union all select '20002',2 union all select '20003',3--select id,count(*) as num,批次 from @t group by id,批次select id + case when num=1 then '' else '-'+cast(num as varchar) end,批次 from (select min(id) as id,count(*) as num,批次 from @t group by 批次) T
insert into @t select '10001',1
union all select '10002',1
union all select '10003',1
union all select '10004',1
union all select '10005',1
union all select '10006',1
union all select '10007',1
union all select '10008',1
union all select '10009',1
union all select '10010',1
union all select '10011',1
union all select '20001',2
union all select '20002',2
union all select '20003',3--查询
select id=case when b.id=b.id1 then b.id else b.id+'-'+b.id1 end,批次=a.批次
from @t a,(
select id,id1=(select min(id) from @t a1
where id>=a.id
and not exists(
select * from @t
where 批次=a1.批次 and id=a1.id+1))
from @t a
where not exists(
select * from @t
where 批次=a.批次 and id=a.id-1))b
where a.id between b.id and b.id1
group by
b.id,b.id1,a.批次--结果
id 批次
--------------------- -----------
10001-10011 1
20001-20002 2
20003 3(所影响的行数为 3 行)
(select id,count(*) as num,批次 from 表 group by id,批次) T
declare @t table(id varchar(10),批次 int)
insert into @t select '10001',1
union all select '10002',1
union all select '10003',1
union all select '10004',1
union all select '10005',1
union all select '10006',1
union all select '10007',1
union all select '10008',1
union all select '10009',1
union all select '10010',1
union all select '10011',1
union all select '20001',2
union all select '20002',2
union all select '20003',3
--查询处理
select id=case when b.id=b.id1 then b.id
else b.id+'-'+cast(convert(int,b.id1)-convert(int,b.id)+1 as varchar(10)) end,
批次=a.批次
from @t a,(
select id,id1=(select min(id) from @t a1
where id>=a.id
and not exists(
select * from @t
where 批次=a1.批次 and id=a1.id+1))
from @t a
where not exists(
select * from @t
where 批次=a.批次 and id=a.id-1))b
where a.id between b.id and b.id1
group by
b.id,b.id1,a.批次--结果
id 批次
--------------------- -----------
10001-11 1
20001-2 2
20003 3(所影响的行数为 3 行)
insert into a
select 10001,1 union all
select 10002,1 union all
select 10003,1 union all
select 10004,1 union all
select 10005,1 union all
select 10006,1 union all
select 10007,1 union all
select 10008,1 union all
select 10009,1 union all
select 10010,1 union all
select 10011,1 union all
select 20001,2 union all
select 20002,2 union all
select 10003,3 select replace(cast(min(id) as char(5))+'-'+right(cast(max(id) as char(5)),2),'-0','-')as id,pici from a group by pici
結果:
id pici
----------------
10001-11 1
20001-2 2
10003-3 3
insert into @t select '10001',1
union all select '10002',1
union all select '10003',1
union all select '10004',1
union all select '10005',1
union all select '10006',1
union all select '10007',1
union all select '10008',1
union all select '10009',1
union all select '10010',1
union all select '10011',1
union all select '20001',2
union all select '20002',2
union all select '20003',3--select id,count(*) as num,批次 from @t group by id,批次select id + case when num=1 then '' else '-'+cast(num as varchar) end,批次 from
(select min(id) as id,count(*) as num,批次 from @t group by 批次) T