表A:employeeno date recordno
001 2009-06-01 353
001 2009-06-02 354
001 2009-06-03 355
002 2009-06-02 120
002 2009-06-04 121
002 2009-06-05 122
003 2009-06-02 100
003 2009-06-03 533
003 2009-06-04 101
003 2009-06-05 102每个雇员的recordno是连续记录的,如有异常记录号(recordno),如何通知SQL语句查询到如下结果:employeeno recordno
003 533
001 2009-06-01 353
001 2009-06-02 354
001 2009-06-03 355
002 2009-06-02 120
002 2009-06-04 121
002 2009-06-05 122
003 2009-06-02 100
003 2009-06-03 533
003 2009-06-04 101
003 2009-06-05 102每个雇员的recordno是连续记录的,如有异常记录号(recordno),如何通知SQL语句查询到如下结果:employeeno recordno
003 533
INSERT @TB
SELECT '001', '2009-06-01', 353 UNION ALL
SELECT '001', '2009-06-02', 354 UNION ALL
SELECT '001', '2009-06-03', 355 UNION ALL
SELECT '002', '2009-06-02', 120 UNION ALL
SELECT '002', '2009-06-04', 121 UNION ALL
SELECT '002', '2009-06-05', 122 UNION ALL
SELECT '003', '2009-06-02', 100 UNION ALL
SELECT '003', '2009-06-03', 533 UNION ALL
SELECT '003', '2009-06-04', 101 UNION ALL
SELECT '003', '2009-06-05', 102SELECT *
FROM @TB AS A
WHERE NOT EXISTS(SELECT * FROM @TB WHERE [recordno]=A.[recordno]-1 OR [recordno]=A.[recordno]+1)
/*
employeeno date recordno
---------- ----------------------- -----------
003 2009-06-03 00:00:00.000 533
*/
FROM tb AS A
WHERE NOT EXISTS
(
SELECT * FROM tb
WHERE employeeno = A.employeeno
AND recordno = A.recordno - 1
)
AND recordno > (SELECT MIN(recordno) FROM tb
WHERE employeeno = A.employeeno);
--> 生成测试数据: @T
DECLARE @T TABLE (employeeno VARCHAR(3),date DATETIME,recordno INT)
INSERT INTO @T
SELECT '001','2009-06-01',353 UNION ALL
SELECT '001','2009-06-02',354 UNION ALL
SELECT '001','2009-06-03',355 UNION ALL
SELECT '002','2009-06-02',120 UNION ALL
SELECT '002','2009-06-04',121 UNION ALL
SELECT '002','2009-06-05',122 UNION ALL
SELECT '003','2009-06-02',100 UNION ALL
SELECT '003','2009-06-03',533 UNION ALL
SELECT '003','2009-06-04',101 UNION ALL
SELECT '003','2009-06-05',102--SQL查询如下:SELECT *
FROM @T AS A
WHERE NOT EXISTS
(
SELECT * FROM @T
WHERE employeeno = A.employeeno
AND recordno = A.recordno - 1
)
AND recordno > (SELECT MIN(recordno) FROM @T
WHERE employeeno = A.employeeno);/*
employeeno date recordno
---------- ----------------------- -----------
003 2009-06-03 00:00:00.000 533(1 行受影响)*/
SELECT *
FROM @TB AS A
WHERE NOT EXISTS(SELECT * FROM @TB WHERE [recordno]=A.[recordno]-1 OR [recordno]=A.[recordno]+1)
insert @tb values('001','2009-06-02',354)
insert @tb values('001','2009-06-03',355)
insert @tb values('002','2009-06-02',120)
insert @tb values('002','2009-06-04',121)
insert @tb values('002','2009-06-05',122)
insert @tb values('003','2009-06-02',100)
insert @tb values('003','2009-06-03',533)
insert @tb values('003','2009-06-04',101)
insert @tb values('003','2009-06-05',102)select * from @tb a where not exists(select 1 from @tb where employeeno=a.employeeno
and (recordno=a.recordno-1 or recordno=a.recordno+1))/*
employeeno date recordno
---------- ----------------------- -----------
003 2009-06-03 00:00:00.000 533
*/