22,209
社区成员
发帖
与我相关
我的任务
分享
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
是,这个条件可变
1楼回答的如果我多加几条数据进去就不行了,如
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
--测试数据结束
SELECT COUNT(DISTINCT id - num)
FROM #a
还是查找2,3,4结果是6的,应该是4才对因为我加的几条不是2,3,4[/quote]
结果为啥是4呢?是求2、3、4的连续次数吗?应该是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