SQL求助

显示全部楼层 · 2021-1-27 08:21:30

哪位大神帮忙写个语句，怎么能筛选出loc列不连续的信息，感谢感谢

分 -->

千问 · 2021-1-27 08:21:30

--测试数据
DECLARE@TEMPTABLE(kinvarchar(50),locvarchar(50))
insertinto@TEMP
select'A','ZZ-3-63'UNIONALL
select'A','ZZ-3-64'UNIONALL
select'A','ZZ-3-65'UNIONALL
select'A','ZZ-3-67'UNIONALL
select'A','ZZ-3-68'UNIONALL
select'A','ZZ-3-71'
--将‘-’去除，当成序号
;withcteas(
SELECTA.kin,A.loc,
SUBSTRING(REPLACE(A.loc,'-',''),3,LEN(loc)-3)ASseq
FROM@TEMPA)
--查出每个loc的上一笔序号，相减，如果大于1则是跳号
SELECTA.kin,A.loc,
preSqr=isNull((SELECTTOP1seqFROMcteBWHEREB.seq1

千问 · 2021-1-27 08:21:30

CREATETABLE#A
(
KINVARCHAR(20),
LOCVARCHAR(20)
)
INSERTINTO#AVALUES('A','ZZ-3-61')
INSERTINTO#AVALUES('A','ZZ-3-62')
INSERTINTO#AVALUES('A','ZZ-3-63')
INSERTINTO#AVALUES('A','ZZ-3-65')
INSERTINTO#AVALUES('A','ZZ-3-66')
INSERTINTO#AVALUES('A','ZZ-3-67')
INSERTINTO#AVALUES('A','ZZ-3-69')
SELECTAA.KIN,AA.LOCFROM(
SELECTA.*,CONVERT(INT,REPLACE(RIGHT(A.LOC,4),'-',''))LOC1FROM#AA
)AAWHEREAA.LOC1-(SELECTTOP1BB.LOC1FROM(
SELECTA.*,CONVERT(INT,REPLACE(RIGHT(A.LOC,4),'-',''))LOC1FROM#AA
)BBWHEREBB.LOC1