哪位大神幫忙寫個陳述句，怎么能篩選出loc列不連續的資訊，感謝感謝
uj5u.com熱心網友回復：
--測驗資料
DECLARE @TEMP TABLE(kin varchar(50),loc varchar(50))
insert into @TEMP
select 'A','ZZ-3-63' UNION ALL
select 'A','ZZ-3-64' UNION ALL
select 'A','ZZ-3-65' UNION ALL
select 'A','ZZ-3-67' UNION ALL
select 'A','ZZ-3-68' UNION ALL
select 'A','ZZ-3-71'
--將‘-’去除，當成序號
;with cte as (
SELECT A.kin,A.loc,
SUBSTRING(REPLACE(A.loc,'-',''),3,LEN(loc)-3) AS seq
FROM @TEMP A)
--查出每個loc的上一筆序號，相減，如果大于1則是跳號
SELECT A.kin,A.loc,
preSqr=isNull((SELECT TOP 1 seq FROM cte B WHERE B.seq<A.seq ORDER BY SEQ DESC),seq)
FROM CTE A
where A.seq-CAST(isNull((SELECT TOP 1 seq FROM cte B WHERE B.seq<A.seq ORDER BY SEQ DESC),seq)AS INT)>1
uj5u.com熱心網友回復：
CREATE TABLE #A
(
KIN VARCHAR(20),
LOC VARCHAR(20)
)
INSERT INTO #A VALUES ('A','ZZ-3-61')
INSERT INTO #A VALUES ('A','ZZ-3-62')
INSERT INTO #A VALUES ('A','ZZ-3-63')
INSERT INTO #A VALUES ('A','ZZ-3-65')
INSERT INTO #A VALUES ('A','ZZ-3-66')
INSERT INTO #A VALUES ('A','ZZ-3-67')
INSERT INTO #A VALUES ('A','ZZ-3-69')
SELECT AA.KIN,AA.LOC FROM(
SELECT A.*,CONVERT(INT,REPLACE(RIGHT(A.LOC,4),'-','')) LOC1 FROM #A A
) AA WHERE AA.LOC1-(SELECT TOP 1 BB.LOC1 FROM (
SELECT A.*,CONVERT(INT,REPLACE(RIGHT(A.LOC,4),'-','')) LOC1 FROM #A A
) BB WHERE BB.LOC1<AA.LOC1 ORDER BY BB.LOC1 DESC)!=1 ORDER BY AA.LOC1 DESC
DROP TABLE #A
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/238266.html
標籤：應用實例
上一篇：如何向資料庫表中插入千萬條資料