表 a:
编号 值
000055 2
000057 2
000059 2
000060 2
000061 2
000062 2
000063 2
000064 2
000065 3
000066 1
000600 1要求得到如下结果:
编号 值
000055,000057,000059-000064 2
000065 3
000066,000600 1
将值相同的记录合并成一行,同时将连续的编号写成“起始编号-结束编号”的形式。
编号 值
000055 2
000057 2
000059 2
000060 2
000061 2
000062 2
000063 2
000064 2
000065 3
000066 1
000600 1要求得到如下结果:
编号 值
000055,000057,000059-000064 2
000065 3
000066,000600 1
将值相同的记录合并成一行,同时将连续的编号写成“起始编号-结束编号”的形式。
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-17 16:54:11
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] varchar(6),[值] int)
insert [tb]
select '000055',2 union all
select '000057',2 union all
select '000059',2 union all
select '000060',2 union all
select '000061',2 union all
select '000062',2 union all
select '000063',2 union all
select '000064',2 union all
select '000065',3 union all
select '000066',1 union all
select '000600',1
--------------开始查询--------------------------
select 值, [编号]=stuff((select ','+[编号] from tb t where 值=tb.值 for xml path('')), 1, 1, '')
from tb
group by 值
----------------结果----------------------------
/* 值 编号
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 000066,000600
2 000055,000057,000059,000060,000061,000062,000063,000064
3 000065(3 行受影响)
*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-17 16:53:30
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (编号 varchar(6),值 int)
INSERT INTO @tb
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1--SQL查询如下:;WITH Liang AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY 值 ORDER BY 值) - 编号 AS rowid,*
FROM @tb
),
Liang2 AS
(
SELECT 值,rowid,CASE WHEN COUNT(*) > 1 THEN RTRIM(MIN(编号))+'~'+RTRIM(MAX(编号))
ELSE RTRIM(MIN(编号)) END AS flag
FROM Liang
GROUP BY 值,rowid
)
SELECT
STUFF((SELECT ',' + flag AS [text()] FROM Liang2
WHERE 值 = A.值 FOR XML PATH('')),1,1,'') AS 编号,
值
FROM Liang2 AS A
GROUP BY 值/*
编号 值
000600,000066 1
000059~000064,000057,000055 2
000065 3
*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-17 16:53:30
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (编号 varchar(6),值 int)
INSERT INTO @tb
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1--SQL查询如下:;WITH Liang AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY 值 ORDER BY 值) - 编号 AS rowid,*
FROM @tb
),
Liang2 AS
(
SELECT 值,rowid,CASE WHEN COUNT(*) > 1 THEN RTRIM(MIN(编号))+'~'+RTRIM(MAX(编号))
ELSE RTRIM(MIN(编号)) END AS flag
FROM Liang
GROUP BY 值,rowid
)
SELECT
STUFF((SELECT ',' + flag AS [text()] FROM Liang2
WHERE 值 = A.值 ORDER BY ABS(rowid) FOR XML PATH('')),1,1,'') AS 编号,
值
FROM Liang2 AS A
GROUP BY 值/*
编号 值
000066,000600 1
000055,000057,000059~000064 2
000065 3
*/
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-17 16:53:11
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([编号] NVARCHAR(10),[值] INT)
INSERT [tb]
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1
GO
--SELECT * FROM [tb]-->SQL查询如下:
;WITH t AS
(
SELECT CASE WHEN a.编号=b.编号 THEN a.编号 ELSE a.编号+'-'+b.编号 END AS 编号,a.值
FROM (
SELECT rn=ROW_NUMBER()OVER(ORDER BY GETDATE()),*
FROM tb t
WHERE NOT EXISTS(
SELECT 1 FROM tb
WHERE [值]=t.值 AND [编号]=t.[编号]+1)
) AS a
JOIN (
SELECT rn=ROW_NUMBER()OVER(ORDER BY GETDATE()),*
FROM tb t
WHERE NOT EXISTS(
SELECT 1 FROM tb
WHERE [值]=t.值 AND t.[编号]=[编号]+1)
) AS b
ON a.rn=b.rn
)
SELECT DISTINCT 编号=STUFF((SELECT ','+编号 FROM t WHERE 值=a.值 FOR XML PATH('')),1,1,''),值
FROM t a
/*
编号 值
000055,000057,000064-000059 2
000065 3
000066,000600 1
*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] varchar(6),[值] int)
insert [tb]
select '000055',2 union all
select '000057',2 union all
select '000059',2 union all
select '000060',2 union all
select '000061',2 union all
select '000062',2 union all
select '000063',2 union all
select '000064',2 union all
select '000065',3 union all
select '000066',1 union all
select '000600',1
;WITH CTE AS
(SELECT ID = ROW_NUMBER() OVER(ORDER BY [编号],[值]) ,* FROM TB)
,CTE1 AS
(SELECT MIN(ID) AS MINID,MAX(ID) AS MAXID,[值] FROM CTE GROUP BY [值],CAST([编号] AS INT)-ID)
,CTE2 AS
(SELECT MINID,[MIN编号]=(SELECT [编号] FROM (SELECT ID = ROW_NUMBER() OVER(ORDER BY [编号],[值]) ,* FROM TB)B WHERE B.ID = A.MINID),
MAXID,[MAX编号]=(SELECT [编号] FROM (SELECT ID = ROW_NUMBER() OVER(ORDER BY [编号],[值]) ,* FROM TB)B WHERE B.ID = A.MAXID),[值]
FROM CTE1 A
),CTE3 AS
(
SELECT [值],[编号]= CASE WHEN MAXID = MINID THEN CAST([MIN编号] AS VARCHAR(100)) ELSE CAST([MIN编号] AS VARCHAR(100))+'-'+CAST([MAX编号] AS VARCHAR(100)) END
FROM CTE2
)
--------------开始查询--------------------------
select 值, [编号]=stuff((select ','+[编号] from CTE3 t where 值=CTE3.值 for xml path('')), 1, 1, '')
from CTE3
group by 值 /*
1 000066,000600
2 000055,000057,000059-000064
3 000065
*/
CREATE TABLE tgss(a VARCHAR(20),b INT)
INSERT tgss SELECT '000055',2
union all select '000057', 2
union all select '000059', 2
union all select '000060', 2
union all select '000061', 2
union all select '000062', 2
union all select '000063', 2
union all select '000064', 2
union all select '000065', 3
union all select '000066', 1
union all select '000600', 1ALTER TABLE tgss ADD g INT
DECLARE @i INT,@l VARCHAR(20),@sql VARCHAR(2000)
SET @i=0UPDATE tgss SET @i=CASE WHEN a*1=@l*1+1 THEN @i ELSE @i+1 end,@l=a,g=@igo
SELECT b,g,CASE WHEN MIN(a)=MAX(a) THEN MIN(a) ELSE MIN(a)+'-'+MAX(a) END e into tgssA FROM tgss
GROUP BY b,g
goCREATE FUNCTION ltgss(@b INT )
RETURNS VARCHAR(1000)
AS
BEGIN
declare @sql VARCHAR(1000)
SELECT @sql=ISNULL(@sql+',','')+e FROM tgssA WHERE b=@b ORDER BY g
RETURN @sql
ENDgo
SELECT b,dbo.ltgss(b) ee FROM tgssA GROUP BY b--result
/*b ee
----------- ------------------------------
1 000066,000600
2 000055,000057,000059-000064
3 000065(所影响的行数为 3 行)*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-17 17:09:41
-------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (编号 varchar(6),值 int)
INSERT INTO [tb]
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1--SQL查询如下:GO
CREATE FUNCTION dbo.MergeNo(@值 int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @re varchar(8000);
SET @re = '';
SELECT
@re = @re + CASE WHEN EXISTS(SELECT * FROM tb
WHERE 值 = A.值
AND CAST(编号 AS int) -1 = CAST(A.编号 AS int))
THEN
CASE WHEN RIGHT(@re,1) <> '~'
THEN 编号 + '~'
ELSE '' END
ELSE ',' + 编号 END
FROM tb AS A WHERE 值 = @值
ORDER BY 编号;
RETURN STUFF(@re,1,1,'');
END
GOSELECT 值 ,dbo.MergeNo(值) AS 编号
FROM tb
GROUP BY 值
GO
DROP TABLE tb
DROP FUNCTION dbo.MergeNo/*
值 编号
1 000066,000600
2 000055,000057000059~,000064
3 000065
*/
if object_id('[a]') is not null drop table [a]
go
create table [a]([编号] varchar(6),[值] int)
insert [a]
select '000055',2 union all
select '000057',2 union all
select '000059',2 union all
select '000060',2 union all
select '000061',2 union all
select '000062',2 union all
select '000063',2 union all
select '000064',2 union all
select '000065',3 union all
select '000066',1 union all
select '000600',1
select *,tid=identity(int,1,1) into #1 from a t where not exists(select 1 from a where 值=t.值 and 编号=t.编号-1)
select *,tid=identity(int,1,1) into #2 from a t where not exists(select 1 from a where 值=t.值 and 编号=t.编号+1)
select a.编号 as no1,b.编号 as no2,a.值 as val into t3 from #1 a,#2 b where a.tid=b.tidcreate function f_str(@val int)
returns varchar(30)
as
begin
declare @s varchar(30)
select @s=isnull(@s+',','')
+case when no1=no2 then no1 else no1+'~'+no2 end
from t3
where val=@val
order by no1
return @s
endselect distinct dbo.f_str(val) as 编号,val as 值 from t3drop table #1,#2,t3
drop function f_str--测试结果:
/*
编号 值
------------------------------ -----------
000055,000057,000059~000064 2
000065 3
000066,000600 1(3 行受影响)*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-17 17:09:41
-------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (编号 varchar(6),值 int)
INSERT INTO [tb]
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1--SQL查询如下:GO
CREATE FUNCTION dbo.MergeNo(@值 int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @re varchar(8000);
SET @re = '';
SELECT
@re = @re + CASE WHEN EXISTS(SELECT * FROM tb
WHERE 值 = A.值
AND CAST(编号 AS int) -1 = CAST(A.编号 AS int))
THEN
CASE WHEN RIGHT(@re,1) <> '~'
THEN ',' + 编号 + '~'
ELSE '' END
ELSE
CASE WHEN EXISTS(SELECT * FROM tb
WHERE 值 = A.值
AND CAST(编号 AS int) + 1 = CAST(A.编号 AS int))
THEN 编号
ELSE ',' + 编号 END
END
FROM tb AS A WHERE 值 = @值
ORDER BY 编号;
RETURN STUFF(@re,1,1,'');
END
GOSELECT 值 ,dbo.MergeNo(值) AS 编号
FROM tb
GROUP BY 值
GO
DROP TABLE tb
DROP FUNCTION dbo.MergeNo/*
值 编号
1 000066,000600
2 000055,000057,000059~000064
3 000065
*/
我大一大二也是经常逃看NBA的。。
HOHO
DROP TABLE [tb]
GO
CREATE TABLE [tb]([编号] NVARCHAR(10),[值] INT)
INSERT [tb]
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1
GO
--SELECT * FROM [tb]-->SQL查询如下:
IF NOT OBJECT_ID('[fn_str]') IS NULL
DROP FUNCTION fn_str
GO
CREATE FUNCTION fn_str(@i INT)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @s VARCHAR(100)
SELECT @s=ISNULL(@s+',','')+CASE WHEN MIN(a.编号)=b.编号 THEN b.编号 ELSE b.编号+'-'+MIN(a.编号) END
FROM (
SELECT *
FROM tb t
WHERE NOT EXISTS(
SELECT 1 FROM tb
WHERE [值]=t.值 AND [编号]=t.[编号]+1)
) AS a
JOIN (
SELECT *
FROM tb t
WHERE NOT EXISTS(
SELECT 1 FROM tb
WHERE [值]=t.值 AND t.[编号]=[编号]+1)
) AS b
ON a.值=b.值 AND a.编号>=b.编号
GROUP BY a.值,b.编号
HAVING a.值=@i
RETURN @s
END
GOSELECT DISTINCT dbo.FN_STR(值) AS [编号],值 FROM tb
/*
编号 值
---------------------------------------------------------------------------------------------------- -----------
000055,000057,000059-000064 2
000065 3
000066,000600 1(3 行受影响)
*/先上一下
IF OBJECT_ID('FUN_TEST') IS NOT NULL DROP FUNCTION FUN_TEST
IF OBJECT_ID('FUN_TEST2') IS NOT NULL DROP FUNCTION FUN_TEST2
GOCREATE FUNCTION FUN_TEST(@VAL INT,@VAL2 INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @STR VARCHAR(8000)
IF @VAL2<>1
SELECT @STR=ISNULL(@STR+',','')+ID FROM TEMP WHERE VAL=@VAL AND VAL2=@VAL2
ELSE
BEGIN
SELECT @STR=ID2 FROM TEMP T1 WHERE NOT EXISTS(SELECT 1 FROM TEMP T2 WHERE T2.VAL=T1.VAL AND
T2.VAL2=T1.VAL2 AND T2.ID2<T1.ID2) AND T1.VAL=@VAL AND T1.VAL2=@VAL2
SELECT @STR=@STR+'-'+ID FROM TEMP T1 WHERE NOT EXISTS(SELECT 1 FROM TEMP T2 WHERE T2.VAL=T1.VAL AND
T2.VAL2=T1.VAL2 AND T2.ID2>T1.ID2) AND T1.VAL=@VAL AND T1.VAL2=@VAL2
END
RETURN @STR
END
GO
CREATE FUNCTION FUN_TEST2(@VAL INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+',','')+VAL3
FROM (
SELECT VAL,VAL2
,DBO.FUN_TEST(VAL,VAL2) 'VAL3'
FROM TEMP WHERE VAL=@VAL
GROUP BY VAL,VAL2
) T
RETURN @STR
END
GO
DECLARE @tb TABLE (ID varchar(6),VAL int)
INSERT INTO @tb
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1CREATE TABLE TEMP(
ID VARCHAR(10),
VAL INT,
ID2 VARCHAR(10),
VAL2 INT
)
INSERT INTO TEMP
SELECT ID,VAL
,ISNULL((SELECT MAX(ID) FROM @TB T2 WHERE T2.VAL=T1.VAL AND T2.ID<T1.ID),ID) 'ID2'
,ISNULL(CAST(ID AS INT)-(SELECT cast(MAX(ID) AS INT) FROM @TB T2 WHERE T2.VAL=T1.VAL AND T2.ID<T1.ID),0) 'VAL2'FROM @TB T1SELECT VAL
,DBO.FUN_TEST2(VAL) 'STR'
FROM TEMP
GROUP BY VAL/*
1 000066,000600
2 000055,000059-000064,000057,000059
3 000065
*/
DROP TABLE [tb]
GO
CREATE TABLE [tb]([编号] NVARCHAR(10),[值] INT)
INSERT [tb]
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1
GO
--SELECT * FROM [tb]-->SQL查询如下:
IF NOT OBJECT_ID('[fn_str]') IS NULL
DROP FUNCTION fn_str
GO
CREATE FUNCTION fn_str(@i INT)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @s VARCHAR(100)
SELECT @s=ISNULL(@s+',','')+CASE WHEN MIN(a.编号)=b.编号 THEN b.编号 ELSE b.编号+'-'+MIN(a.编号) END
FROM (
SELECT *
FROM tb t
WHERE NOT EXISTS(
SELECT 1 FROM tb
WHERE [值]=t.值 AND [编号]=t.[编号]+1 AND 值=@i)
AND 值=@i
) AS a
JOIN (
SELECT *
FROM tb t
WHERE NOT EXISTS(
SELECT 1 FROM tb
WHERE [值]=t.值 AND t.[编号]=[编号]+1 AND 值=@i)
AND 值=@i
) AS b
ON a.值=b.值 AND a.编号>=b.编号
GROUP BY a.值,b.编号
RETURN @s
END
GOSELECT DISTINCT dbo.FN_STR(值) AS [编号],值 FROM tb
/*
编号 值
----------------------------------------- -----------
000055,000057,000059-000064 2
000065 3
000066,000600 1(3 行受影响)
*/优化版
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] varchar(6),[值] int)
insert [tb]
select '000055',2 union all
select '000057',2 union all
select '000059',2 union all
select '000060',2 union all
select '000061',2 union all
select '000062',2 union all
select '000063',2 union all
select '000064',2 union all
select '000065',3 union all
select '000066',1 union all
select '000600',1create table tt(编号 varchar(20),值 int )
select *,id=identity(int,1,1) into #t from tb insert tt select 编号,值 from #t t
where exists(select * from #t where id=t.id+1 and 值=t.值 and cast(编号 as int)=cast(t.编号 as int)+1)
insert tt select right('00000'+ltrim(cast(max(编号) as int)+1),6),值 from tt group by 值if object_id('f_str')is not null drop function f_str
go
create function f_str(@zhi int)
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s+',','')+编号 from tb
where 值=@zhi and 编号 not in (select 编号 from tt)
if (select count(*) from tt where 值=@zhi)>0
set @s=@s+','+(select min(编号)from tt)+'-'+(select max(编号)from tt)
return @s
end
goselect 值,编号=dbo.f_str(值) from tb
group by 值值 编号
----------- ----------------------------------------------------------------------------------------------------
1 000066,000600
2 000055,000057,000059-000064
3 000065(3 行受影响)drop table tt
drop table #t
我做过:使用游标
只是效率不高
按照架构分层的原则,业务逻辑和技术逻辑分离的原则,SQL不应该处理这类事情,而是应该交由上层的程序(具体点就是表现层)来实现。
把程序写成这样子,要么是没有毕业的大学生,要么就是一些软件公司的考题,纯属无聊。
把什么东西都扔给数据库了
反正也不会移植
可以增加对SQL语句的熟悉度,
--1.建表
create table tmptb
(
num varchar(12)not null primary key,
groupid int
)
--2.向表中插入数据
insert into tmptb
select 'abcdefg',1
union all
select 'hijklmno',1
union all
select 'lmnopqrst',1
union all
select 'afasfdas',2
union all
select 'rgfdgafaw',2
union all
select 'dfewtqwr',3
union all
select 'cvczxa',4
union all
select 'cftreyas',4
--3.建一个函数
alter function myFunc( @groupid int )
returns varchar(100)
as
begin
declare @retnum varchar(100)
set @retnum=''
select @retnum=@retnum+','+num from tmptb where groupid=@groupid
set @retnum=substring(ltrim(@retnum),2,len(@retnum)-2)
return @retnum
end
--4.执行查询
select distinct groupid,dbo.myfunc(groupid) from tmptb查询结果:
groupid
----------- ----------------------------------------------------------------------------------------------------
1 bcdefg,hijklmno,lmnopqrst
2 fasfdas,rgfdgafaw
3 fewtqwr
4 ftreyas,cvczxa(4 行受影响)
(select t.* from a t left join a b on t.编号-1= b.编号 and t.值=b.值 where b.编号 is null) s
inner join
(select t.* from a t left join a b on t.编号+1= b.编号 and t.值=b.值 where b.编号 is null) h
on h.编号>=s.编号 and s.值=h.值
group by s.编号,s.值
前几天自己写了一个函数,不过速度感觉不是特别快,不知道哪位高手能够帮忙写个比较好的或者帮忙优化下。