从表中按顺序间隔的那些数据中过滤掉 [英] Filter those data from table where there is a gap in sequence

问题描述

如果下面提到的任何目标之间的数据为空，我想将doprocess标志设置为0

示例:

create table TestSAMP (
  id int identity(1,1),
  modelid navrchar(max),
  target1 nvarchar(max),
  target2 nvarchar(max),
  target3 nvarchar(max),
  target4 nvarchar(max),
  doprcoess  int default(1)
)

--VALID SET DOPROCESS FLAG TO 1
INSERT INTO TestSAMP(modelid,target1,target2,target3,target4) 
VALUES('1','T1','T2','T3','T4')

--NOTVALID SET DOPROCESS FLAG TO 0 DUE TO THE DATA IS MISSING IN SEQUENCE 
INSERT INTO TestSAMP(modelid,target1,target2,target3,target4)
VALUES('2','TR','','T3','T4')

--VALID SET DOPROCESS FLAG TO 1 As if data is present
-- it should be present insequence in below t1 t2 as they arfe in sequence
INSERT INTO TestSAMP(modelid,target1,target2,target3,target4)
VALUES('3','T1','T2','','')

--NOTVALID SET DOPROCESS FLAG TO 0 DUE TO THE DATA IS MISSING IN SEQUENCE 
--where T4 data is provided and not in T3
INSERT INTO TestSAMP(modelid,target1,target2,target3,target4) 
VALUES('4','T1','T2','','T4')

我有很多解决方案，人们可​​以尝试找到顺序 数字，但这里是nvarchar

解决方案

您可以将间隙和孤岛视为字符串，即'IGGI'，压扁它并搜索'IGI'模式:

SELECT *, CASE WHEN squashed LIKE '%IGI%' THEN 0 ELSE 1 END AS new_doprocess
FROM TestSAMP t
CROSS APPLY(SELECT STRING_AGG(CASE WHEN t = '' THEN 'G' ELSE 'I'END, '') r
             FROM (VALUES (1,id, target1), (2,id, target2),
                          (3,id, target3), (4,id, target4)) sub(rn, id, t)) s
CROSS APPLY (SELECT replace(replace(replace(replace(replace(replace(s.r,'G','<>')
   ,'><',''),'<>','G'),'I','<>'),'><',''),'<>','I')) AS sub(squashed)
ORDER BY t.id;

db<>小提琴演示

示例:

  id  | target1  | target2  | target3  | target4  |  r    | squashed  | doprocess 
 -----|----------|----------|----------|----------|-------|-----------|----------- 
   1  | T1       | T2       | T3       | T4       | IIII  | I         |         1 
   2  | T1       |          | T3       | T4       | IGII  | IGI       |         0 
   3  | T1       | T2       |          |          | IIGG  | IG        |         1 
   4  | T1       | T2       |          | T4       | IIGI  | IGI       |         0 
   5  |          |          |          | T4       | GGGI  | GI        |         1 
   6  |          |          |          |          | GGGG  | G         |         1 

I want to set the doprocess flag to 0 if the below data is empty between any of the targets mentioned

Example:

create table TestSAMP (
  id int identity(1,1),
  modelid navrchar(max),
  target1 nvarchar(max),
  target2 nvarchar(max),
  target3 nvarchar(max),
  target4 nvarchar(max),
  doprcoess  int default(1)
)

--VALID SET DOPROCESS FLAG TO 1
INSERT INTO TestSAMP(modelid,target1,target2,target3,target4) 
VALUES('1','T1','T2','T3','T4')

--NOTVALID SET DOPROCESS FLAG TO 0 DUE TO THE DATA IS MISSING IN SEQUENCE 
INSERT INTO TestSAMP(modelid,target1,target2,target3,target4)
VALUES('2','TR','','T3','T4')

--VALID SET DOPROCESS FLAG TO 1 As if data is present
-- it should be present insequence in below t1 t2 as they arfe in sequence
INSERT INTO TestSAMP(modelid,target1,target2,target3,target4)
VALUES('3','T1','T2','','')

--NOTVALID SET DOPROCESS FLAG TO 0 DUE TO THE DATA IS MISSING IN SEQUENCE 
--where T4 data is provided and not in T3
INSERT INTO TestSAMP(modelid,target1,target2,target3,target4) 
VALUES('4','T1','T2','','T4')

I have got lot of solution where people try out to find the sequential number but here the case is nvarchar

解决方案

You could treat gaps and islands as string i.e. 'IGGI', squash it, and search for 'IGI' pattern:

SELECT *, CASE WHEN squashed LIKE '%IGI%' THEN 0 ELSE 1 END AS new_doprocess
FROM TestSAMP t
CROSS APPLY(SELECT STRING_AGG(CASE WHEN t = '' THEN 'G' ELSE 'I'END, '') r
             FROM (VALUES (1,id, target1), (2,id, target2),
                          (3,id, target3), (4,id, target4)) sub(rn, id, t)) s
CROSS APPLY (SELECT replace(replace(replace(replace(replace(replace(s.r,'G','<>')
   ,'><',''),'<>','G'),'I','<>'),'><',''),'<>','I')) AS sub(squashed)
ORDER BY t.id;

db<>fiddle demo

Example:

  id  | target1  | target2  | target3  | target4  |  r    | squashed  | doprocess 
 -----|----------|----------|----------|----------|-------|-----------|----------- 
   1  | T1       | T2       | T3       | T4       | IIII  | I         |         1 
   2  | T1       |          | T3       | T4       | IGII  | IGI       |         0 
   3  | T1       | T2       |          |          | IIGG  | IG        |         1 
   4  | T1       | T2       |          | T4       | IIGI  | IGI       |         0 
   5  |          |          |          | T4       | GGGI  | GI        |         1 
   6  |          |          |          |          | GGGG  | G         |         1 

这篇关于从表中按顺序间隔的那些数据中过滤掉的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！