22,209
社区成员
发帖
与我相关
我的任务
分享如何判断某几列数 里面的值从来没有断开过?
CREATE TABLE #tmp(A VARCHAR(20),ID INT,ID1 INT,ID2 INT,ID3 INT,ID4 INT,ID5 INT,ID6 INT)
INSERT #tmp
( A, ID, ID1, ID2, ID3, ID4, ID5, ID6 )
SELECT 'A',1,0,2,3,0,3,2 UNION ALL
SELECT 'B',1,2,2,3,0,3,2 UNION ALL
SELECT 'C',1,0,0,3,3,3,2 UNION ALL
SELECT 'D',1,0,1,3,0,3,2 UNION ALL
SELECT 'E',1,1,0,3,0,4,2 UNION ALL
SELECT 'F',1,0,2,3,0,2,2 UNION ALL
SELECT 'G',1,0,2,3,4,3,4
SELECT * FROM #tmp
IF OBJECT_ID('TEMPDB..#TMP') IS NOT NULL DROP TABLE #tmp
ID到ID6 之间 的数有3个连续 就符合条件 （举例中的有BCG符合条件）
INSERT #tmp
( A, ID, ID1, ID2, ID3, ID4, ID5, ID6 )
SELECT 'A',1,0,2,3,0,3,2 UNION ALL
SELECT 'B',1,2,2,3,0,3,2 UNION ALL
SELECT 'C',1,0,0,3,3,3,2 UNION ALL
SELECT 'D',1,0,1,3,0,3,2 UNION ALL
SELECT 'E',1,1,0,3,0,4,2 UNION ALL
SELECT 'F',1,0,2,3,0,2,2 UNION ALL
SELECT 'G',1,0,2,3,4,3,4
SELECT * FROM #tmp
IF OBJECT_ID('TEMPDB..#TMP') IS NOT NULL DROP TABLE #tmp
ID到ID6 之间 的数有3个连续 就符合条件 （举例中的有BCG符合条件）
...全文
请发表友善的回复…
发表回复
jiangjubo 2017-12-22
- 打赏
- 举报
谢谢你的提示 我写了一个 动态过程处理了！！
CREATE TABLE #tmp(A VARCHAR(20),size1 INT,size2 INT,size3 INT,size4 INT,size5 INT,size6 INT,size7 INT)
INSERT #tmp
( A, size1,size2,size3,size4,size5,size6,size7 )
SELECT 'A',1,0,2,3,0,3,2 UNION ALL
SELECT 'B',1,2,2,3,0,3,2 UNION ALL
SELECT 'C',1,0,0,3,3,3,2 UNION ALL
SELECT 'D',1,0,1,3,0,3,2 UNION ALL
SELECT 'E',1,1,0,3,0,4,2 UNION ALL
SELECT 'F',1,0,2,3,0,2,2 UNION ALL
SELECT 'G',1,0,2,3,4,3,4
DECLARE @desc VARCHAR(200)='size',
@len INT =3, --------连续间隔
@max INT=7, --------字段数
@facelen INT,-------
@sql VARCHAR(8000),
@charenter VARCHAR(5)=CHAR(13)+CHAR(10),
@min INT --第几位开始
SET @sql=''
IF @len>@max
RETURN
SET @min=1
WHILE @min<=@max-@len+1
BEGIN
DECLARE @beginlen INT
SELECT @beginlen=0
WHILE @beginlen<@len
BEGIN
IF @beginlen+1=@len
SET @sql=@sql+@desc+CAST(@beginlen+@min AS VARCHAR(3))+'>0 '
ELSE
SET @sql=@sql+@desc+CAST(@beginlen+@min AS VARCHAR(3))+'>0 and '
SET @beginlen=@beginlen+1
END
IF @min=@max-@len+1
SET @sql=@sql+')'
ELSE
SET @sql=@sql+') or('
SET @min=@min+1
end
SET @sql='('+@sql
SET @sql=' select * from #tmp where '+@sql
PRINT @sql
EXEC (@sql)
IF OBJECT_ID('TEMPDB..#TMP') IS NOT NULL DROP TABLE #tmp
早起晚睡 2017-12-21
- 打赏
- 举报
满足啊
吉普赛的歌 2017-12-21
- 打赏
- 举报
貌似不符合楼主意思哦
zjcxc 2017-12-21
- 打赏
- 举报
SELECT * FROM #tmp
WHERE
(ID = ID1-1 AND ID=ID2-2 AND ID=ID3-3) OR
(ID = ID2-1 AND ID=ID3-2 AND ID=ID4-3) OR
(ID = ID3-1 AND ID=ID4-2 AND ID=ID5-3) OR
(ID = ID4-1 AND ID=ID5-2 AND ID=ID6-3)
吉普赛的歌 2017-12-21
- 打赏
- 举报
不明白你说的没有断开是什么意思
听雨停了 2017-12-21
- 打赏
- 举报
BCG怎么会符合条件呢，你说的连续是连续相同，还是连续差1；比如是3，3，3还是2，3，4这样的才是符合条件的
日月路明 2017-12-21
- 打赏
- 举报
吉普赛的歌 2017-12-21
- 打赏
- 举报
USE tempdb
GO
IF OBJECT_ID('dbo.Fun_GetEqualDiffCnt') IS NOT NULL
DROP FUNCTION dbo.Fun_GetEqualDiffCnt
GO
IF OBJECT_ID('TEMPDB..#TMP') IS NOT NULL DROP TABLE #tmp
CREATE TABLE #tmp(A VARCHAR(20),ID INT,ID1 INT,ID2 INT,ID3 INT,ID4 INT,ID5 INT,ID6 INT)
INSERT #tmp
( A, ID, ID1, ID2, ID3, ID4, ID5, ID6 )
SELECT 'A',1,0,2,3,0,3,2 UNION ALL
SELECT 'B',1,2,2,3,0,3,2 UNION ALL
SELECT 'C',1,0,0,3,3,3,2 UNION ALL
SELECT 'D',1,0,1,3,0,3,2 UNION ALL
SELECT 'E',1,1,0,3,0,4,2 UNION ALL
SELECT 'F',1,0,2,3,0,2,2 UNION ALL
SELECT 'G',1,0,2,3,4,3,4
SELECT * FROM #tmp
WHERE
(id>0 AND id1>0 AND id2>0)
OR
(id1>0 AND id2>0 AND id3>0)
OR
(id2>0 AND id3>0 AND id4>0)
OR
(id3>0 AND id4>0 AND id5>0)
OR
(id4>0 AND id5>0 AND id6>0)
zjcxc 2017-12-21
- 打赏
- 举报
0 nt in (id, id1, id2)
jiangjubo 2017-12-21
- 打赏
- 举报
意思是 id,id1,id2(id1,id2,id3如此类推)同时不为0[/quote]
可以说清楚的不早说清楚，搞得大伙在这猜有意思吗？
[/quote]
大神 我错了！！！！
CREATE TABLE #tmp(A VARCHAR(20),ID INT,ID1 INT,ID2 INT,ID3 INT,ID4 INT,ID5 INT,ID6 INT)
INSERT #tmp
( A, ID, ID1, ID2, ID3, ID4, ID5, ID6 )
SELECT 'A',1,0,2,3,0,3,2 UNION ALL
SELECT 'B',1,2,2,3,0,3,2 UNION ALL
SELECT 'C',1,0,0,3,3,3,2 UNION ALL
SELECT 'D',1,0,1,3,0,3,2 UNION ALL
SELECT 'E',1,1,0,3,0,4,2 UNION ALL
SELECT 'F',1,0,2,3,0,2,2 UNION ALL
SELECT 'G',1,0,2,3,4,3,4
--测试数据结束
--创建一个字符串分割函数
CREATE FUNCTION [dbo].[f_splitSTR](
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
--调用函数
;WITH cte AS (
SELECT *,CAST(id AS VARCHAR(20))
+CAST(id1 AS VARCHAR(20))
+CAST(id2 AS VARCHAR(20))
+CAST(id3 AS VARCHAR(20))
+CAST(id4 AS VARCHAR(20))
+CAST(id5 AS VARCHAR(20))
+CAST(id6 AS VARCHAR(20)) AS num
FROM #tmp
),
cte2 AS (
SELECT * FROM cte a
OUTER APPLY dbo.f_splitSTR(a.num,'0') b
)
SELECT * FROM cte2
WHERE LEN(col)>=3
听雨停了 2017-12-21
- 打赏
- 举报
意思是 id,id1,id2(id1,id2,id3如此类推)同时不为0[/quote]
可以说清楚的不早说清楚，搞得大伙在这猜有意思吗？
CREATE TABLE #tmp(A VARCHAR(20),ID INT,ID1 INT,ID2 INT,ID3 INT,ID4 INT,ID5 INT,ID6 INT)
INSERT #tmp
( A, ID, ID1, ID2, ID3, ID4, ID5, ID6 )
SELECT 'A',1,0,2,3,0,3,2 UNION ALL
SELECT 'B',1,2,2,3,0,3,2 UNION ALL
SELECT 'C',1,0,0,3,3,3,2 UNION ALL
SELECT 'D',1,0,1,3,0,3,2 UNION ALL
SELECT 'E',1,1,0,3,0,4,2 UNION ALL
SELECT 'F',1,0,2,3,0,2,2 UNION ALL
SELECT 'G',1,0,2,3,4,3,4
--测试数据结束
--创建一个字符串分割函数
CREATE FUNCTION [dbo].[f_splitSTR](
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
--调用函数
;WITH cte AS (
SELECT *,CAST(id AS VARCHAR(20))
+CAST(id1 AS VARCHAR(20))
+CAST(id2 AS VARCHAR(20))
+CAST(id3 AS VARCHAR(20))
+CAST(id4 AS VARCHAR(20))
+CAST(id5 AS VARCHAR(20))
+CAST(id6 AS VARCHAR(20)) AS num
FROM #tmp
),
cte2 AS (
SELECT * FROM cte a
OUTER APPLY dbo.f_splitSTR(a.num,'0') b
)
SELECT * FROM cte2
WHERE LEN(col)>=3
jiangjubo 2017-12-21
- 打赏
- 举报
意思是 id,id1,id2(id1,id2,id3如此类推)同时不为0
jiangjubo 2017-12-21
- 打赏
- 举报
意思是 id,id1,id2(id1,id2,id3如此类推)同时不为0
jiangjubo 2017-12-21
- 打赏
- 举报
意思是 id,id1,id2(id1,id2,id3)同时不为0
