• 主页

# 怎样查找连续纪录的重复

hansonboy 2017-12-09 12:46:31

a表字段
id,num
1 2
2 3
3 4
4 2
5 3
6 4
7 5
8 2
9 3
10 4

...全文
99 点赞 收藏 15

15 条回复
hansonboy 2017年12月09日

``````

if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[num] int)
Insert #a
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,2 union all
select 5,3 union all
select 6,4 union all
select 7,5 union all
select 8,2 union all
select 9,3 union all
select 10,3 union all
select 11,3 union all
select 12,3 union all
select 13,4
GO
DECLARE @str VARCHAR(1000),@Rows INT;

SET @str='2,3,4'	--连续数条件

SET @str='select '+REPLACE(@str,',',' union all select ')
DECLARE @Nr TABLE(ID INT IDENTITY,Num int);
INSERT INTO @Nr
( Num )
EXEC(@str)
SET @Rows=@@ROWCOUNT

--1.显示总记录数
SELECT
COUNT(*) AS 记录数
FROM
(
SELECT (a.ID-b.id) AS GroupRows
FROM @Nr AS a
INNER JOIN #a AS b ON b.num=a.Num
GROUP BY a.ID-b.id
HAVING COUNT(*)=@Rows
) AS t
/*

2
*/
--2.显示记录
;WITH CTET
AS
(
SELECT (a.ID-b.id) AS Grp,b.*
FROM @Nr AS a
INNER JOIN #a AS b ON b.num=a.Num
)
SELECT  a.ID,a.Num
FROM    CTET AS a
INNER JOIN ( SELECT Grp
FROM   CTET
GROUP BY Grp
HAVING COUNT(*) = @Rows
) AS b ON a.Grp = b.Grp;
/*
ID	Num
1	2
2	3
3	4
4	2
5	3
6	4
*/

``````

hansonboy 2017年12月09日

``````DECLARE @str VARCHAR(1000)='2,3,4'    --连续数条件
,@Rows INT=0;``````

``````

if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[num] int)
Insert #a
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,2 union all
select 5,3 union all
select 6,4 union all
select 7,5 union all
select 8,2 union all
select 9,3 union all
select 10,3 union all
select 11,3 union all
select 12,3 union all
select 13,4
GO
DECLARE @str VARCHAR(1000),@Rows INT;

SET @str='2,3,4'	--连续数条件

SET @str='select '+REPLACE(@str,',',' union all select ')
DECLARE @Nr TABLE(ID INT IDENTITY,Num int);
INSERT INTO @Nr
( Num )
EXEC(@str)
SET @Rows=@@ROWCOUNT

SELECT
COUNT(*) AS 记录数
FROM
(
SELECT (a.ID-b.id) AS GroupRows
FROM @Nr AS a
INNER JOIN #a AS b ON b.num=a.Num
GROUP BY a.ID-b.id
HAVING COUNT(*)=@Rows
) AS t

/*

2
*/``````

``````CREATE FUNCTION dbo.F_Split
(
@SplitString nvarchar(max),  --源字符串
@Separator nvarchar(10)=' '  --分隔符号，默认为空格
)
RETURNS @SplitStringsTable TABLE  --输出的数据表
(
[id] int identity(1,1),
[value] nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);

SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
GO
``````

``````if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[num] int)
Insert #a
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,2 union all
select 5,3 union all
select 6,4 union all
select 7,5 union all
select 8,2 union all
select 9,3 union all
select 10,3 union all
select 11,3 union all
select 12,3 union all
select 13,4
Go
--测试数据结束
DECLARE @str NVARCHAR(100)='2,3,4'  --传过来的查询条件
SELECT COUNT(1) FROM (
SELECT  COUNT(1) AS rncount ,
#a.id - t.id AS rn
FROM    #a
JOIN dbo.F_Split(@str, ',') t ON t.value = #a.num
GROUP BY #a.id - t.id
HAVING  COUNT(1) = LEN(REPLACE(@str,',','')))tt``````

``````DECLARE @str VARCHAR(1000)='2,3,4'    --连续数条件
,@Rows INT=0;``````

``````

if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[num] int)
Insert #a
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,2 union all
select 5,3 union all
select 6,4 union all
select 7,5 union all
select 8,2 union all
select 9,3 union all
select 10,3 union all
select 11,3 union all
select 12,3 union all
select 13,4
GO
DECLARE @str VARCHAR(1000),@Rows INT;

SET @str='2,3,4'	--连续数条件

SET @str='select '+REPLACE(@str,',',' union all select ')
DECLARE @Nr TABLE(ID INT IDENTITY,Num int);
INSERT INTO @Nr
( Num )
EXEC(@str)
SET @Rows=@@ROWCOUNT

SELECT
COUNT(*) AS 记录数
FROM
(
SELECT (a.ID-b.id) AS GroupRows
FROM @Nr AS a
INNER JOIN #a AS b ON b.num=a.Num
GROUP BY a.ID-b.id
HAVING COUNT(*)=@Rows
) AS t

/*

2
*/``````

hansonboy 2017年12月09日

hansonboy 2017年12月09日

``````

if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[num] int)
Insert #a
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,2 union all
select 5,3 union all
select 6,4 union all
select 7,5 union all
select 8,2 union all
select 9,3 union all
select 10,3 union all
select 11,3 union all
select 12,3 union all
select 13,4
GO
DECLARE @str VARCHAR(1000)='2,3,4'	--连续数条件
,@Rows INT=0;

SET @str='select '+REPLACE(@str,',',' union all select ')
DECLARE @Nr TABLE(ID INT IDENTITY,Num int);
INSERT INTO @Nr
( Num )
EXEC(@str)
SET @Rows=@@ROWCOUNT

SELECT
COUNT(*) AS 记录数
FROM
(
SELECT (a.ID-b.id) AS GroupRows
FROM @Nr AS a
INNER JOIN #a AS b ON b.num=a.Num
GROUP BY a.ID-b.id
HAVING COUNT(*)=@Rows
) AS t

/*

2
*/``````

hansonboy 2017年12月09日

``````--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[num] int)
Insert #a
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,2 union all
select 5,3 union all
select 6,4 union all
select 7,5 union all
select 8,2 union all
select 9,3 union all
select 10,4
Go
--测试数据结束
SELECT  COUNT(DISTINCT id - num)
FROM    #a
``````
[quote=引用 5 楼 hansonboy 的回复:] [quote=引用 4 楼 sinat_28984567 的回复:] 上边的例子2，3，4是查询条件？(这个条件可变)

``````--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[num] int)
Insert #a
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,2 union all
select 5,3 union all
select 6,4 union all
select 7,5 union all
select 8,2 union all
select 9,3 union all
select 10,4
Go
--测试数据结束
SELECT  COUNT(DISTINCT id - num)
FROM    #a
``````

[quote=引用 4 楼 sinat_28984567 的回复:] 上边的例子2，3，4是查询条件？(这个条件可变)

hansonboy 2017年12月09日

hansonboy 2017年12月09日

``````--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[num] int)
Insert #a
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,2 union all
select 5,3 union all
select 6,4 union all
select 7,5 union all
select 8,2 union all
select 9,3 union all
select 10,4
Go
--测试数据结束
SELECT  COUNT(DISTINCT id - num)
FROM    #a
``````

9304

12.1w+

MS-SQL Server 疑难问题