----------------------------------------------------------------
-- 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)
---------------------------------*/
--> 生成测试数据表:tb
IF 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
*/
sql2000:
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', 1
ALTER TABLE tgss ADD g INT
DECLARE @i INT,@l VARCHAR(20),@sql VARCHAR(2000)
SET @i=0
UPDATE tgss SET @i=CASE WHEN a*1=@l*1+1 THEN @i ELSE @i+1 end,@l=a,g=@i
go
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
go
CREATE 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
END
go
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
GO
SELECT 值 ,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.tid
create 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
end
select distinct dbo.f_str(val) as 编号,val as 值 from t3
drop 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
GO
SELECT 值 ,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
*/
--> 生成测试数据表:tb先上一下
IF 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查询如下:
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
GO
SELECT DISTINCT dbo.FN_STR(值) AS [编号],值 FROM tb
/*
编号 值
---------------------------------------------------------------------------------------------------- -----------
000055,000057,000059-000064 2
000065 3
000066,000600 1
(3 行受影响)
*/
IF OBJECT_ID('TEMP') IS NOT NULL DROP TABLE TEMP
IF OBJECT_ID('FUN_TEST') IS NOT NULL DROP FUNCTION FUN_TEST
IF OBJECT_ID('FUN_TEST2') IS NOT NULL DROP FUNCTION FUN_TEST2
GO
CREATE 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',1
CREATE 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 T1
SELECT VAL
,DBO.FUN_TEST2(VAL) 'STR'
FROM TEMP
GROUP BY VAL
/*
1 000066,000600
2 000055,000059-000064,000057,000059
3 000065
*/
--> 生成测试数据表:tb优化版
IF 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查询如下:
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
GO
SELECT 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',1
create 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
go
select 值,编号=dbo.f_str(值) from tb
group by 值
值 编号
----------- ----------------------------------------------------------------------------------------------------
1 000066,000600
2 000055,000057,000059-000064
3 000065
(3 行受影响)
drop table tt
drop table #t
