测试数据:#T
A1 A2 A3
1 156 2009-06-08 05:12:24.787
1 156 2009-06-08 05:12:25.470
1 156 2009-06-08 05:12:26.267
1 156 2009-06-08 05:12:27.030
1 160 2009-06-08 05:13:36.237
1 160 2009-06-08 05:13:37.007
1 160 2009-06-08 05:13:37.703
1 160 2009-06-08 05:13:38.500
1 160 2009-06-08 05:13:39.237
1 160 2009-06-08 05:13:40.000
1 160 2009-06-08 05:13:40.767
1 160 2009-06-08 05:14:10.877
1 160 2009-06-08 05:14:11.657
1 161 2009-06-08 05:14:12.573
1 161 2009-06-08 05:14:13.173
1 161 2009-06-08 05:14:13.940
1 161 2009-06-08 05:14:14.703
1 161 2009-06-08 05:14:15.473
1 169 2009-06-08 05:17:36.803
1 169 2009-06-08 05:17:44.500
1 169 2009-06-08 05:17:45.237
1 170 2009-06-08 05:17:46.050
1 170 2009-06-08 05:17:46.783
1 170 2009-06-08 05:17:47.580
1 170 2009-06-08 05:17:48.327DECLARE @S NVARCHAR(max)
SELECT @S=ISNULL(@S+',','')+CONVERT(NVARCHAR(20),A2) FROM #T with (nolock) WHERE A1=1 AND (A3>='2009-06-08 00:00:00' AND A3<='2009-06-08 12:00:00')
SELECT @S现在结果类似:
156,156,156,156,158,158,158,158,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,161,161,161,161,161,161,161,161,要求结果类似:
156,160,161去掉重复的,有什么好的解决方法没???
A1 A2 A3
1 156 2009-06-08 05:12:24.787
1 156 2009-06-08 05:12:25.470
1 156 2009-06-08 05:12:26.267
1 156 2009-06-08 05:12:27.030
1 160 2009-06-08 05:13:36.237
1 160 2009-06-08 05:13:37.007
1 160 2009-06-08 05:13:37.703
1 160 2009-06-08 05:13:38.500
1 160 2009-06-08 05:13:39.237
1 160 2009-06-08 05:13:40.000
1 160 2009-06-08 05:13:40.767
1 160 2009-06-08 05:14:10.877
1 160 2009-06-08 05:14:11.657
1 161 2009-06-08 05:14:12.573
1 161 2009-06-08 05:14:13.173
1 161 2009-06-08 05:14:13.940
1 161 2009-06-08 05:14:14.703
1 161 2009-06-08 05:14:15.473
1 169 2009-06-08 05:17:36.803
1 169 2009-06-08 05:17:44.500
1 169 2009-06-08 05:17:45.237
1 170 2009-06-08 05:17:46.050
1 170 2009-06-08 05:17:46.783
1 170 2009-06-08 05:17:47.580
1 170 2009-06-08 05:17:48.327DECLARE @S NVARCHAR(max)
SELECT @S=ISNULL(@S+',','')+CONVERT(NVARCHAR(20),A2) FROM #T with (nolock) WHERE A1=1 AND (A3>='2009-06-08 00:00:00' AND A3<='2009-06-08 12:00:00')
SELECT @S现在结果类似:
156,156,156,156,158,158,158,158,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,160,161,161,161,161,161,161,161,161,要求结果类似:
156,160,161去掉重复的,有什么好的解决方法没???
FROM
(SELECT DISTINCT A2 FROM TB WHERE A3>='2009-06-08 00:00:00' AND A3 <='2009-06-08 12:00:00') AS TSELECT @STR
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(A1 int, A2 int, A3 datetime)
go
insert tb SELECT
1, 156 ,'2009-06-08 05:12:24.787'UNION ALL SELECT
1 ,156 ,'2009-06-08 05:12:25.470' UNION ALL SELECT
1, 156 ,'2009-06-08 05:12:26.267' UNION ALL SELECT
1, 156 ,'2009-06-08 05:12:27.030' UNION ALL SELECT
1, 160 ,'2009-06-08 05:13:36.237' UNION ALL SELECT
1, 160 ,'2009-06-08 05:13:37.007' UNION ALL SELECT
1, 160 ,'2009-06-08 05:13:37.703' UNION ALL SELECT
1 ,160 ,'2009-06-08 05:13:38.500' UNION ALL SELECT
1 ,160 ,'2009-06-08 05:13:39.237' UNION ALL SELECT
1 ,160 ,'2009-06-08 05:13:40.000' UNION ALL SELECT
1 ,160 ,'2009-06-08 05:13:40.767' UNION ALL SELECT
1 ,160 ,'2009-06-08 05:14:10.877' UNION ALL SELECT
1, 160 ,'2009-06-08 05:14:11.657' UNION ALL SELECT
1 ,161 ,'2009-06-08 05:14:12.573' UNION ALL SELECT
1 ,161,'2009-06-08 05:14:13.173' UNION ALL SELECT
1 ,161,'2009-06-08 05:14:13.940' UNION ALL SELECT
1 ,161 ,'2009-06-08 05:14:14.703' UNION ALL SELECT
1 ,161 ,'2009-06-08 05:14:15.473' UNION ALL SELECT
1, 169 ,'2009-06-08 05:17:36.803' UNION ALL SELECT
1 ,169 ,'2009-06-08 05:17:44.500' UNION ALL SELECT
1 ,169 ,'2009-06-08 05:17:45.237' UNION ALL SELECT
1, 170 ,'2009-06-08 05:17:46.050' UNION ALL SELECT
1, 170, '2009-06-08 05:17:46.783' UNION ALL SELECT
1, 170, '2009-06-08 05:17:47.580' UNION ALL SELECT
1, 170 ,'2009-06-08 05:17:48.327'
go
DECLARE @STR VARCHAR(500)SELECT @STR=ISNULL(@STR+',','')+LTRIM(A2)
FROM
(SELECT DISTINCT A2 FROM TB WHERE A3>='2009-06-08 00:00:00' AND A3 <='2009-06-08 12:00:00') AS T
SELECT @STR
go
/*------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
156,160,161,169,170-------*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(A1 int, A2 int, A3 datetime)
go
insert tb SELECT
1, 156 ,'2009-06-08 05:12:24.787'UNION ALL SELECT
1 ,156 ,'2009-06-08 05:12:25.470' UNION ALL SELECT
1, 156 ,'2009-06-08 05:12:26.267' UNION ALL SELECT
1, 156 ,'2009-06-08 05:12:27.030' UNION ALL SELECT
1, 160 ,'2009-06-08 05:13:36.237' UNION ALL SELECT
1, 160 ,'2009-06-08 05:13:37.007' UNION ALL SELECT
1, 160 ,'2009-06-08 05:13:37.703' UNION ALL SELECT
1 ,160 ,'2009-06-08 05:13:38.500' UNION ALL SELECT
1 ,160 ,'2009-06-08 05:13:39.237' UNION ALL SELECT
1 ,160 ,'2009-06-08 05:13:40.000' UNION ALL SELECT
1 ,160 ,'2009-06-08 05:13:40.767' UNION ALL SELECT
1 ,160 ,'2009-06-08 05:14:10.877' UNION ALL SELECT
1, 160 ,'2009-06-08 05:14:11.657' UNION ALL SELECT
1 ,161 ,'2009-06-08 05:14:12.573' UNION ALL SELECT
1 ,161,'2009-06-08 05:14:13.173' UNION ALL SELECT
1 ,161,'2009-06-08 05:14:13.940' UNION ALL SELECT
1 ,161 ,'2009-06-08 05:14:14.703' UNION ALL SELECT
1 ,161 ,'2009-06-08 05:14:15.473' UNION ALL SELECT
1, 169 ,'2009-06-08 05:17:36.803' UNION ALL SELECT
1 ,169 ,'2009-06-08 05:17:44.500' UNION ALL SELECT
1 ,169 ,'2009-06-08 05:17:45.237' UNION ALL SELECT
1, 170 ,'2009-06-08 05:17:46.050' UNION ALL SELECT
1, 170, '2009-06-08 05:17:46.783' UNION ALL SELECT
1, 170, '2009-06-08 05:17:47.580' UNION ALL SELECT
1, 170 ,'2009-06-08 05:17:48.327'
go
DECLARE @STR VARCHAR(500)
set @STR=''
SELECT @STR=@str+','+LTRIM(A2)
FROM
(SELECT DISTINCT A2 FROM TB WHERE A3>='2009-06-08 00:00:00' AND A3 <='2009-06-08 12:00:00') AS T
SELECT stuff(@STR,1,1,'')
go
/*------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
156,160,161,169,170-------*/
INSERT TBTEST
SELECT 1, 156, '2009-06-08 05:12:24.787' UNION
SELECT 1, 156, '2009-06-08 05:12:25.470' UNION
SELECT 1, 156, '2009-06-08 05:12:26.267' UNION
SELECT 1, 156, '2009-06-08 05:12:27.030' UNION
SELECT 1, 160, '2009-06-08 05:13:36.237' UNION
SELECT 1, 160, '2009-06-08 05:13:37.007' UNION
SELECT 1, 160, '2009-06-08 05:13:37.703' UNION
SELECT 1, 160, '2009-06-08 05:13:38.500' UNION
SELECT 1, 160, '2009-06-08 05:13:39.237' UNION
SELECT 1, 160, '2009-06-08 05:13:40.000 'UNION
SELECT 1, 160, '2009-06-08 05:13:40.767' UNION
SELECT 1, 160, '2009-06-08 05:14:10.877' UNION
SELECT 1, 160, '2009-06-08 05:14:11.657' UNION
SELECT 1, 161, '2009-06-08 05:14:12.573' UNION
SELECT 1, 161, '2009-06-08 05:14:13.173' UNION
SELECT 1, 161, '2009-06-08 05:14:13.940' UNION
SELECT 1, 161, '2009-06-08 05:14:14.703' UNION
SELECT 1, 161, '2009-06-08 05:14:15.473' UNION
SELECT 1, 169, '2009-06-08 05:17:36.803' UNION
SELECT 1, 169, '2009-06-08 05:17:44.500 'UNION
SELECT 1, 169, '2009-06-08 05:17:45.237 'DECLARE @STR VARCHAR(500)SELECT @STR=ISNULL(@STR+',','')+LTRIM(A2)
FROM
(SELECT DISTINCT A2 FROM TBTEST WHERE A3>='2009-06-08 00:00:00' AND A3 <='2009-06-08 12:00:00') AS TSELECT @STR
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
156,160,161,169(所影响的行数为 1 行)
delete from table
insert into table select field1,field2,field3 from #temp去掉重复记录
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-19 22:17:50
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([A1] int,[A2] int,[A3] datetime)
Insert tb
Select 1,156,'2009-06-08 05:12:24.787' union all
Select 1,156,'2009-06-08 05:12:25.470' union all
Select 1,156,'2009-06-08 05:12:26.267' union all
Select 1,156,'2009-06-08 05:12:27.030' union all
Select 1,160,'2009-06-08 05:13:36.237' union all
Select 1,160,'2009-06-08 05:13:37.007' union all
Select 1,160,'2009-06-08 05:13:37.703' union all
Select 1,160,'2009-06-08 05:13:38.500' union all
Select 1,160,'2009-06-08 05:13:39.237' union all
Select 1,160,'2009-06-08 05:13:40.000' union all
Select 1,160,'2009-06-08 05:13:40.767' union all
Select 1,160,'2009-06-08 05:14:10.877' union all
Select 1,160,'2009-06-08 05:14:11.657' union all
Select 1,161,'2009-06-08 05:14:12.573' union all
Select 1,161,'2009-06-08 05:14:13.173' union all
Select 1,161,'2009-06-08 05:14:13.940' union all
Select 1,161,'2009-06-08 05:14:14.703' union all
Select 1,161,'2009-06-08 05:14:15.473' union all
Select 1,169,'2009-06-08 05:17:36.803' union all
Select 1,169,'2009-06-08 05:17:44.500' union all
Select 1,169,'2009-06-08 05:17:45.237' union all
Select 1,170,'2009-06-08 05:17:46.050' union all
Select 1,170,'2009-06-08 05:17:46.783' union all
Select 1,170,'2009-06-08 05:17:47.580' union all
Select 1,170,'2009-06-08 05:17:48.327'
Go
--Select * from tb-->SQL查询如下:
DECLARE @S NVARCHAR(MAX)
SELECT @S=ISNULL(@S+',','')+LTRIM(A2)
FROM TB WITH (NOLOCK)
WHERE A1=1 AND (A3>='2009-06-08 00:00:00' AND A3 <='2009-06-08 12:00:00')
GROUP BY A2
SELECT @S
/*
---------------------------
156,160,161,169,170(1 行受影响)
*/
Create table [tb]([A1] int,[A2] int,[A3] datetime)
Insert tb
Select 1,156,'2009-06-08 05:12:24.787' union all
Select 1,156,'2009-06-08 05:12:25.470' union all
Select 1,156,'2009-06-08 05:12:26.267' union all
Select 1,156,'2009-06-08 05:12:27.030' union all
Select 1,160,'2009-06-08 05:13:36.237' union all
Select 1,160,'2009-06-08 05:13:37.007' union all
Select 1,160,'2009-06-08 05:13:37.703' union all
Select 1,160,'2009-06-08 05:13:38.500' union all
Select 1,160,'2009-06-08 05:13:39.237' union all
Select 1,160,'2009-06-08 05:13:40.000' union all
Select 1,160,'2009-06-08 05:13:40.767' union all
Select 1,160,'2009-06-08 05:14:10.877' union all
Select 1,160,'2009-06-08 05:14:11.657' union all
Select 1,161,'2009-06-08 05:14:12.573' union all
Select 1,161,'2009-06-08 05:14:13.173' union all
Select 1,161,'2009-06-08 05:14:13.940' union all
Select 1,161,'2009-06-08 05:14:14.703' union all
Select 1,161,'2009-06-08 05:14:15.473' union all
Select 1,169,'2009-06-08 05:17:36.803' union all
Select 1,169,'2009-06-08 05:17:44.500' union all
Select 1,169,'2009-06-08 05:17:45.237' union all
Select 1,170,'2009-06-08 05:17:46.050' union all
Select 1,170,'2009-06-08 05:17:46.783' union all
Select 1,170,'2009-06-08 05:17:47.580' union all
Select 1,170,'2009-06-08 05:17:48.327'
Go
select a2,IDENTITY(int,1,1) as comm
into #
from tb
where A1=1 AND (A3>='2009-06-08 00:00:00' AND A3 <='2009-06-08 12:00:00')
group by a2 declare @s varchar(8000)
select
@s=isnull(@s+',','')+rtrim(a.A2)+case when a.A2!=min(b.A2) then '-'+rtrim(min(b.A2)) else '' end
from
(select c.* from # c where not exists(select 1 from # where comm=c.comm and A2=c.A2-1)) a,
(select c.* from # c where not exists(select 1 from # where comm=c.comm and A2=c.A2+1)) b
where
a.comm=b.comm and a.A2<=b.A2
group by
a.comm,a.A2
order by
a.comm,a.A2select @s
drop table tb,#/*
----------------------
156,160,161,169,170(所影响的行数为 1 行)
*/
Insert tb
Select 1,156,'2009-06-08 05:12:24.787' union all
Select 1,156,'2009-06-08 05:12:25.470' union all
Select 1,156,'2009-06-08 05:12:26.267' union all
Select 1,156,'2009-06-08 05:12:27.030' union all
Select 1,160,'2009-06-08 05:13:36.237' union all
Select 1,160,'2009-06-08 05:13:37.007' union all
Select 1,160,'2009-06-08 05:13:37.703' union all
Select 1,160,'2009-06-08 05:13:38.500' union all
Select 1,160,'2009-06-08 05:13:39.237' union all
Select 1,160,'2009-06-08 05:13:40.000' union all
Select 1,160,'2009-06-08 05:13:40.767' union all
Select 1,160,'2009-06-08 05:14:10.877' union all
Select 1,160,'2009-06-08 05:14:11.657' union all
Select 1,161,'2009-06-08 05:14:12.573' union all
Select 1,161,'2009-06-08 05:14:13.173' union all
Select 1,161,'2009-06-08 05:14:13.940' union all
Select 1,161,'2009-06-08 05:14:14.703' union all
Select 1,162,'2009-06-08 05:14:15.473' union all
Select 1,169,'2009-06-08 05:17:36.803' union all
Select 1,169,'2009-06-08 05:17:44.500' union all
Select 1,169,'2009-06-08 05:17:45.237' union all
Select 1,170,'2009-06-08 05:17:46.050' union all
Select 1,170,'2009-06-08 05:17:46.783' union all
Select 1,170,'2009-06-08 05:17:47.580' union all
Select 1,170,'2009-06-08 05:17:48.327'
Go
select a2,IDENTITY(int,1,1) as comm
into #
from tb
where A1=1 AND (A3>='2009-06-08 00:00:00' AND A3 <='2009-06-08 12:00:00')
group by a2
declare @s varchar(8000)select
@s=isnull(@s+',','')+rtrim(a.a2)+case when a.a2!=min(b.a2) then '-'+rtrim(min(b.a2)) else '' end
from
(select c.* from # c where not exists(select 1 from # where a2=c.a2-1)) a,
(select c.* from # c where not exists(select 1 from # where a2=c.a2+1)) b
where
a.a2<=b.a2
group by
a.a2
order by
a.a2select @s
drop table tb,#/*
---------------------
156,160-162,169-170(所影响的行数为 1 行)
*/
FROM
(SELECT DISTINCT A2 FROM TB WHERE A3>='2009-06-08 00:00:00' AND A3 <='2009-06-08 12:00:00') AS T
SELECT @STR
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(A1 int, A2 int, A3 datetime)
go
insert tb SELECT
1, 156 ,'2009-06-08 05:12:24.787'UNION ALL SELECT
1 ,156 ,'2009-06-08 05:12:25.470' UNION ALL SELECT
1, 158 ,'2009-06-08 05:12:26.267' UNION ALL SELECT
1, 156 ,'2009-06-08 05:12:27.030' UNION ALL SELECT
1, 160 ,'2009-06-08 05:13:36.237' UNION ALL SELECT
1, 161 ,'2009-06-08 05:13:37.007' UNION ALL SELECT
1, 162 ,'2009-06-08 05:13:37.703' UNION ALL SELECT
1 ,163 ,'2009-06-08 05:13:38.500' UNION ALL SELECT
1 ,164 ,'2009-06-08 05:13:39.237' UNION ALL SELECT
1 ,165 ,'2009-06-08 05:13:40.000' UNION ALL SELECT
1 ,166 ,'2009-06-08 05:13:40.767' UNION ALL SELECT
1 ,166 ,'2009-06-08 05:14:10.877' UNION ALL SELECT
1, 160 ,'2009-06-08 05:14:11.657' UNION ALL SELECT
1 ,169 ,'2009-06-08 05:14:12.573' UNION ALL SELECT
1 ,169,'2009-06-08 05:14:13.173' UNION ALL SELECT
1 ,161,'2009-06-08 05:14:13.940' UNION ALL SELECT
1 ,161 ,'2009-06-08 05:14:14.703' UNION ALL SELECT
1 ,161 ,'2009-06-08 05:14:15.473' UNION ALL SELECT
1, 169 ,'2009-06-08 05:17:36.803' UNION ALL SELECT
1 ,169 ,'2009-06-08 05:17:44.500' UNION ALL SELECT
1 ,169 ,'2009-06-08 05:17:45.237' UNION ALL SELECT
1, 170 ,'2009-06-08 05:17:46.050' UNION ALL SELECT
1, 170, '2009-06-08 05:17:46.783' UNION ALL SELECT
1, 170, '2009-06-08 05:17:47.580' UNION ALL SELECT
1, 173 ,'2009-06-08 05:17:48.327'
go
SELECT row_number() over (order by a2) AS ID,a2 INTO #
FROM TB WITH (NOLOCK)
WHERE A1=1 AND (A3>='2009-06-08 00:00:00' AND A3 <='2009-06-08 12:00:00')
GROUP BY A2
SELECT id,begindate=CASE WHEN NOT EXISTS(SELECT * FROM # WHERE a2=t.a2-1 and t.id=ID+1) then a2 end,
enddate=case when not exists(select * from # where a2=t.a2+1 and t.id=id-1) then a2 end
into ## from # t
declare @s varchar(100)
select @S=ISNULL(@S+',','')+rtrim(begindate)+case when begindate=enddate then '' else '-'+RTRIM(enddate) end
from(
select begindate=case when begindate is null then (select top 1 begindate from ## where ID>=t.id and begindate IS not null ) else begindate end,
enddate=case when enddate is null then (select top 1 enddate from ## where ID>=t.id and enddate IS not null ) else enddate end
from ## t ) t
where begindate<=enddate
go
select @s/*
----------------------------------------------------------------------------------------------------
156,160-162,169-170*/
Drop table [tb]
Go
Create table [tb]([A1] int,[A2] int,[A3] datetime)
Insert tb
Select 1,156,'2009-06-08 05:12:24.787' union all
Select 1,156,'2009-06-08 05:12:25.470' union all
Select 1,156,'2009-06-08 05:12:26.267' union all
Select 1,156,'2009-06-08 05:12:27.030' union all
Select 1,160,'2009-06-08 05:13:36.237' union all
Select 1,160,'2009-06-08 05:13:37.007' union all
Select 1,160,'2009-06-08 05:13:37.703' union all
Select 1,160,'2009-06-08 05:13:38.500' union all
Select 1,160,'2009-06-08 05:13:39.237' union all
Select 1,160,'2009-06-08 05:13:40.000' union all
Select 1,160,'2009-06-08 05:13:40.767' union all
Select 1,160,'2009-06-08 05:14:10.877' union all
Select 1,160,'2009-06-08 05:14:11.657' union all
Select 1,161,'2009-06-08 05:14:12.573' union all
Select 1,161,'2009-06-08 05:14:13.173' union all
Select 1,161,'2009-06-08 05:14:13.940' union all
Select 1,161,'2009-06-08 05:14:14.703' union all
Select 1,161,'2009-06-08 05:14:15.473' union all
Select 1,169,'2009-06-08 05:17:36.803' union all
Select 1,169,'2009-06-08 05:17:44.500' union all
Select 1,169,'2009-06-08 05:17:45.237' union all
Select 1,170,'2009-06-08 05:17:46.050' union all
Select 1,170,'2009-06-08 05:17:46.783' union all
Select 1,170,'2009-06-08 05:17:47.580' union all
Select 1,170,'2009-06-08 05:17:48.327' union all
Select 1,171,'2009-06-08 05:17:48.327'
Go
--Select * from tb
declare @s nvarchar(100)
;with t as
(
select A1,A2
from tb
group by A1,A2
),t1 as
(
select rn=row_number() over(order by a1,a2),*
from t a
where not exists(
select 1
from t
where a1=a.a1
and a2=a.a2-1)
union
select rn=row_number() over(order by a1,a2),*
from t a
where not exists(
select 1
from t
where a1=a.a1
and a2=a.a2+1)
),t2 as
(
select distinct stuff((select '-'+ltrim(A2) from t1 where rn=t.rn for xml path('')),1,1,'') a2
from t1 t
)
select @s=stuff((select ','+a2 from t2 for xml path('')),1,1,'')select @s
/*
----------------------------------------------------------------------------------------------------
156,160-161,169-171(1 行受影响)
8/
Drop table [tb]
Go
Create table [tb]([A1] int,[A2] int,[A3] datetime)
Insert tb
Select 1,156,'2009-06-08 05:12:24.787' union all
Select 1,156,'2009-06-08 05:12:25.470' union all
Select 1,156,'2009-06-08 05:12:26.267' union all
Select 1,156,'2009-06-08 05:12:27.030' union all
Select 1,160,'2009-06-08 05:13:36.237' union all
Select 1,160,'2009-06-08 05:13:37.007' union all
Select 1,160,'2009-06-08 05:13:37.703' union all
Select 1,160,'2009-06-08 05:13:38.500' union all
Select 1,160,'2009-06-08 05:13:39.237' union all
Select 1,160,'2009-06-08 05:13:40.000' union all
Select 1,160,'2009-06-08 05:13:40.767' union all
Select 1,160,'2009-06-08 05:14:10.877' union all
Select 1,160,'2009-06-08 05:14:11.657' union all
Select 1,161,'2009-06-08 05:14:12.573' union all
Select 1,161,'2009-06-08 05:14:13.173' union all
Select 1,161,'2009-06-08 05:14:13.940' union all
Select 1,161,'2009-06-08 05:14:14.703' union all
Select 1,161,'2009-06-08 05:14:15.473' union all
Select 1,169,'2009-06-08 05:17:36.803' union all
Select 1,169,'2009-06-08 05:17:44.500' union all
Select 1,169,'2009-06-08 05:17:45.237' union all
Select 1,170,'2009-06-08 05:17:46.050' union all
Select 1,170,'2009-06-08 05:17:46.783' union all
Select 1,170,'2009-06-08 05:17:47.580' union all
Select 1,170,'2009-06-08 05:17:48.327' union all
Select 1,171,'2009-06-08 05:17:48.327'
Go
--Select * from tb
declare @s nvarchar(100)
;with t as
(
select A1,A2
from tb
group by A1,A2
),t1 as
(
select rn=row_number() over(order by a1,a2),*
from t a
where not exists(
select 1
from t
where a1=a.a1
and a2=a.a2-1)
union
select rn=row_number() over(order by a1,a2),*
from t a
where not exists(
select 1
from t
where a1=a.a1
and a2=a.a2+1)
),t2 as
(
select distinct stuff((select '-'+ltrim(A2) from t1 where rn=t.rn for xml path('')),1,1,'') a2
from t1 t
)
select @s=stuff((select ','+a2 from t2 for xml path('')),1,1,'')select @s
/*
----------------------------------------------------------------------------------------------------
156,160-161,169-171(1 行受影响)
8/
Drop table [tb]
Go
Create table [tb]([A1] int,[A2] int,[A3] datetime)
Insert tb
Select 1,156,'2009-06-08 05:12:24.787' union all
Select 1,156,'2009-06-08 05:12:25.470' union all
Select 1,156,'2009-06-08 05:12:26.267' union all
Select 1,156,'2009-06-08 05:12:27.030' union all
Select 1,160,'2009-06-08 05:13:36.237' union all
Select 1,160,'2009-06-08 05:13:37.007' union all
Select 1,160,'2009-06-08 05:13:37.703' union all
Select 1,160,'2009-06-08 05:13:38.500' union all
Select 1,160,'2009-06-08 05:13:39.237' union all
Select 1,160,'2009-06-08 05:13:40.000' union all
Select 1,160,'2009-06-08 05:13:40.767' union all
Select 1,160,'2009-06-08 05:14:10.877' union all
Select 1,160,'2009-06-08 05:14:11.657' union all
Select 1,161,'2009-06-08 05:14:12.573' union all
Select 1,161,'2009-06-08 05:14:13.173' union all
Select 1,161,'2009-06-08 05:14:13.940' union all
Select 1,161,'2009-06-08 05:14:14.703' union all
Select 1,161,'2009-06-08 05:14:15.473' union all
Select 1,169,'2009-06-08 05:17:36.803' union all
Select 1,169,'2009-06-08 05:17:44.500' union all
Select 1,169,'2009-06-08 05:17:45.237' union all
Select 1,170,'2009-06-08 05:17:46.050' union all
Select 1,170,'2009-06-08 05:17:46.783' union all
Select 1,170,'2009-06-08 05:17:47.580' union all
Select 1,170,'2009-06-08 05:17:48.327' union all
Select 1,171,'2009-06-08 05:17:48.327'
Go
--Select * from tb
declare @s nvarchar(100)
;with t as
(
select A1,A2
from tb
group by A1,A2
),t1 as
(
select rn=row_number() over(order by a1,a2),*
from t a
where not exists(
select 1
from t
where a1=a.a1
and a2=a.a2-1)
union
select rn=row_number() over(order by a1,a2),*
from t a
where not exists(
select 1
from t
where a1=a.a1
and a2=a.a2+1)
),t2 as
(
select distinct stuff((select '-'+ltrim(A2) from t1 where rn=t.rn for xml path('')),1,1,'') a2
from t1 t
)
select @s=stuff((select ','+a2 from t2 for xml path('')),1,1,'')select @s
/*
----------------------------------------------------------------------------------------------------
156,160-161,169-171(1 行受影响)
*/