22,301
社区成员




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
*/
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;
这一段改改,SQL05不支持声明+赋默认值
e.g.
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
*/
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
*/
--测试数据
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
--测试数据
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