表如下:
a b
1 1
2 1
3 1
5 1
6 1
7 2
10 2
12 3
13 3
我想把它变成如下的形式
a b
1-3,5-6 | 1
7,10 | 2
10,12,13| 3
就是把a列中,相邻的以b列为组合并,大家帮个忙啊
a b
1 1
2 1
3 1
5 1
6 1
7 2
10 2
12 3
13 3
我想把它变成如下的形式
a b
1-3,5-6 | 1
7,10 | 2
10,12,13| 3
就是把a列中,相邻的以b列为组合并,大家帮个忙啊
调试欢乐多
10,12,13| 3 应该为:
10,12-13| 3
create table tab
(
a int,
b int
)insert into tab select 1, 1
insert into tab select 2, 1
insert into tab select 3, 1
insert into tab select 5, 1
insert into tab select 6, 1
insert into tab select 7, 2
insert into tab select 10, 2
insert into tab select 12, 3
insert into tab select 13, 3--建立函数
create function f_str(@b int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+cast(a as varchar) from t where b = @b
set @ret = stuff(@ret,1,1,'')
return @ret
end
go--语句
select case when a = a2 then cast(a as varchar) else cast(a as varchar) + '-'+cast(a2 as varchar) end as a,b
into t
from
(select a,b,
a2 = (select min(a) from (select a,b from tab aa
where not exists(select 1 from tab where a = aa.a + 1 and b = aa.b))bb
where bb.b = aa.b and bb.a >= aa.a)
from tab aa
where not exists(select 1 from tab where a = aa.a - 1 and b = aa.b)
)ccselect dbo.f_str(b) as a,b from t group by b--结果
1-3,5-6 1
7,10 2
12-13 3--删除环境
drop table tab
drop table t
drop table tab
INSERT INTO @tb
SELECT 1, 1
UNION ALL SELECT 2, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 5, 1
UNION ALL SELECT 6, 1
UNION ALL SELECT 7, 2
UNION ALL SELECT 10, 2
UNION ALL SELECT 12, 3
UNION ALL SELECT 13, 3SELECT CASE WHEN T1.A = MIN(T2.A) THEN CAST(T1.A AS VARCHAR) ELSE CAST(T1.A AS VARCHAR) + '-' + CAST(MIN(T2.A) AS VARCHAR) END AS A, T1.B
INTO TEMP_TABLE FROM
(SELECT A, B FROM @TB T WHERE A NOT IN(SELECT A + 1 FROM @TB WHERE B = T.B))T1,
(SELECT A, B FROM @TB T WHERE A NOT IN(SELECT A - 1 FROM @TB WHERE B = T.B))T2
WHERE T1.B = T2.B AND T1.A <= T2.A
GROUP BY T1.A, T1.B
GOCREATE FUNCTION GetStr(@B INT)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @RE VARCHAR(100)
SET @RE = ''
SELECT @RE = @RE + A + ',' FROM TEMP_TABLE WHERE B = @B
RETURN LEFT(@RE, LEN(@RE) - 1)
END
GOSELECT dbo.GetStr(T.B), T.B FROM (SELECT DISTINCT B FROM TEMP_TABLE)TDROP FUNCTION dbo.GetStr
DROP TABLE TEMP_TABLE