首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

多条记录合并为一条，同时将连续的编号写成“起始编号-结束编号”的形式解决思路

2012-01-13 
多条记录合并为一条，同时将连续的编号写成“起始编号-结束编号”的形式表 a：编号值000055200005720000592000

多条记录合并为一条，同时将连续的编号写成“起始编号-结束编号”的形式
表 a：
编号 值
0000552
0000572
0000592
0000602
0000612
0000622
0000632
0000642
0000653
0000661
0006001

要求得到如下结果：
编号 值
000055,000057,000059-000064 2
000065 3
000066,000600 1
将值相同的记录合并成一行，同时将连续的编号写成“起始编号-结束编号”的形式。

[解决办法]

SQL code
---------------------------------------  Author : liangCK 梁爱兰--  Comment: 小梁 爱 兰儿--  Date   : 2009-09-17 16:53:30------------------------------------- --> 生成测试数据: @tbDECLARE @tb TABLE (编号 varchar(6),值 int)INSERT INTO @tbSELECT '000055',2 UNION ALLSELECT '000057',2 UNION ALLSELECT '000059',2 UNION ALLSELECT '000060',2 UNION ALLSELECT '000061',2 UNION ALLSELECT '000062',2 UNION ALLSELECT '000063',2 UNION ALLSELECT '000064',2 UNION ALLSELECT '000065',3 UNION ALLSELECT '000066',1 UNION ALLSELECT '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 AGROUP BY 值/*编号    值000600,000066    1000059~000064,000057,000055    2000065    3*/
[解决办法]
SQL code
/*-----------------------------------  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]GOCREATE TABLE [tb]([编号] NVARCHAR(10),[值] INT)INSERT [tb]SELECT '000055',2 UNION ALLSELECT '000057',2 UNION ALLSELECT '000059',2 UNION ALLSELECT '000060',2 UNION ALLSELECT '000061',2 UNION ALLSELECT '000062',2 UNION ALLSELECT '000063',2 UNION ALLSELECT '000064',2 UNION ALLSELECT '000065',3 UNION ALLSELECT '000066',1 UNION ALLSELECT '000600',1GO--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    2000065    3000066,000600    1*/
[解决办法]
SQL code
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', 1ALTER TABLE tgss ADD g INTDECLARE @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=@igoSELECT 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,ggoCREATE 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 @sqlENDgoSELECT b,dbo.ltgss(b) ee FROM tgssA GROUP BY b--result/*b           ee                             ----------- ------------------------------ 1           000066,0006002           000055,000057,000059-0000643           000065（所影响的行数为 3 行）*/ 
 
[解决办法]
用临时表（函数里不能用临时表，所以用了一个t3）写个2000的试试
SQL code
if object_id('[a]') is not null drop table [a]gocreate table [a]([编号] varchar(6),[值] int)insert [a]select '000055',2 union allselect '000057',2 union allselect '000059',2 union allselect '000060',2 union allselect '000061',2 union allselect '000062',2 union allselect '000063',2 union allselect '000064',2 union allselect '000065',3 union allselect '000066',1 union allselect '000600',1select *,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)asbegin  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 @sendselect distinct dbo.f_str(val) as 编号,val as 值 from t3drop table #1,#2,t3drop function f_str--测试结果:/*编号                             值------------------------------ -----------000055,000057,000059~000064    2000065                         3000066,000600                  1(3 行受影响)*/
[解决办法]
SQL code
---------------------------------------  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 ALLSELECT '000057',2 UNION ALLSELECT '000059',2 UNION ALLSELECT '000060',2 UNION ALLSELECT '000061',2 UNION ALLSELECT '000062',2 UNION ALLSELECT '000063',2 UNION ALLSELECT '000064',2 UNION ALLSELECT '000065',3 UNION ALLSELECT '000066',1 UNION ALLSELECT '000600',1--SQL查询如下:GOCREATE FUNCTION dbo.MergeNo(@值 int)    RETURNS varchar(8000)ASBEGIN    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,'');ENDGOSELECT 值 ,dbo.MergeNo(值) AS 编号FROM tbGROUP BY 值GODROP TABLE tbDROP FUNCTION dbo.MergeNo/*值    编号1    000066,0006002    000055,000057,000059~0000643    000065*/
[解决办法]
SQL code
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL    DROP TABLE [tb]GOCREATE TABLE [tb]([编号] NVARCHAR(10),[值] INT)INSERT [tb]SELECT '000055',2 UNION ALLSELECT '000057',2 UNION ALLSELECT '000059',2 UNION ALLSELECT '000060',2 UNION ALLSELECT '000061',2 UNION ALLSELECT '000062',2 UNION ALLSELECT '000063',2 UNION ALLSELECT '000064',2 UNION ALLSELECT '000065',3 UNION ALLSELECT '000066',1 UNION ALLSELECT '000600',1GO--SELECT * FROM [tb]-->SQL查询如下:IF NOT OBJECT_ID('[fn_str]') IS NULL    DROP FUNCTION fn_strGOCREATE FUNCTION fn_str(@i INT)RETURNS VARCHAR(100)ASBEGIN    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 @sENDGOSELECT DISTINCT dbo.FN_STR(值) AS [编号],值 FROM tb/*编号                                                                                                   值---------------------------------------------------------------- -----------000055,000057,000059-000064                                                                          2000065                                                                                               3000066,000600                                                                                        1(3 行受影响)*/ 
 
[解决办法]
谨以此SQL代码表达我对梁哥的崇敬之情，梁哥你是我的偶像
SQL code
 
 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 
  
 /* 
 1000066,000600 
 2000055,000059-000064,000057,000059 
 3000065 
 */ 
  
 

[解决办法]
SQL code
if object_id('[tb]') is not null drop table [tb]go create table [tb]([编号] varchar(6),[值] int)insert [tb]select '000055',2 union allselect '000057',2 union allselect '000059',2 union allselect '000060',2 union allselect '000061',2 union allselect '000062',2 union allselect '000063',2 union allselect '000064',2 union allselect '000065',3 union allselect '000066',1 union allselect '000600',1create table tt(编号 varchar(20),值 int )select *,id=identity(int,1,1) into #t from tb insert tt select 编号,值 from #t twhere 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_strgocreate function f_str(@zhi int)returns varchar(100)asbegin    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 @sendgoselect 值,编号=dbo.f_str(值) from tbgroup by 值值           编号----------- ----------------------------------------------------------------1           000066,0006002           000055,000057,000059-0000643           000065(3 行受影响)drop table ttdrop table #t 
 
[解决办法]
这个也不一定是.net，如果数据量太大，从数据库读太多数据确实不划算。我也不知道怎么写，建议楼主可以考虑下用存储过程，会比较快一些。我们公司在做项目时，遇到像你这种问题老大也都是要求放数据库里面操作的，放应用程序处理的话单单传数据就要浪费大量的时间了。考虑业务分层是没错，可是更要考虑数据量，否则分层就成了累赘。
[解决办法]
探讨
这种写法有意义吗？
 按照架构分层的原则，业务逻辑和技术逻辑分离的原则，SQL不应该处理这类事情，而是应该交由上层的程序（具体点就是表现层）来实现。
 把程序写成这样子，要么是没有毕业的大学生，要么就是一些软件公司的考题，纯属无聊。

[解决办法]
SQL code
--1.建表create table tmptb(num varchar(12)not null primary key,groupid int )--2.向表中插入数据insert  into tmptbselect  'abcdefg',1union allselect  'hijklmno',1union allselect  'lmnopqrst',1union allselect  'afasfdas',2union allselect  'rgfdgafaw',2union allselect  'dfewtqwr',3union allselect  'cvczxa',4union allselect  'cftreyas',4--3.建一个函数alter function myFunc( @groupid int )returns varchar(100)asbegindeclare @retnum varchar(100)set @retnum=''select @retnum=@retnum+','+num from tmptb where groupid=@groupidset @retnum=substring(ltrim(@retnum),2,len(@retnum)-2)return @retnumend--4.执行查询select distinct groupid,dbo.myfunc(groupid) from tmptb
[解决办法]
探讨
引用:
这个也不一定是.net，如果数据量太大，从数据库读太多数据确实不划算。我也不知道怎么写，建议楼主可以考虑下用存储过程，会比较快一些。我们公司在做项目时，遇到像你这种问题老大也都是要求放数据库里面操作的，[color=#FF0000]放应用程序处理的话单单传数据就要浪费大量的时间了[color]。考虑业务分层是没错，可是更要考虑数据量，否则分层就成了累赘。


这句话没道理,数据总会从数据库传输到应用程序的，不存在浪费时间之说

[解决办法]
探讨
引用:
这个也不一定是.net，如果数据量太大，从数据库读太多数据确实不划算。我也不知道怎么写，建议楼主可以考虑下用存储过程，会比较快一些。我们公司在做项目时，遇到像你这种问题老大也都是要求放数据库里面操作的，[color=#FF0000]放应用程序处理的话单单传数据就要浪费大量的时间了[color]。考虑业务分层是没错，可是更要考虑数据量，否则分层就成了累赘。


这句话没道理,数据总会从数据库传输到应用程序的，不存在浪费时间之说
读书人精选
热点排行