表A
employeeno consumemoney balancemoney date count
0001 1 10 2007-10-01 10:05:02 5
0002 3 50 2007-10-01 10:10:23 30
0001 2 8 2007-10-01 15:01:20 6
0001 1 6 2007-10-02 12:13:28 8
0001 2 4 2007-10-05 08:09:43 9
0002 2 48 2007-10-06 19:23:00 31
0002 5 43 2007-10-07 08:20:16 32
0002 3 10 2007-10-08 17:10:40 34
0002 2 8 2007-10-09 08:01:07 35
0001 1 3 2007-10-09 16:38:16 10
......9999如何写SQL语句查出count(记录号)缺少/丢失记录号的人员,
如:员工0001丢失记录号7
员工0002丢失记录号33结果如下:----------------
employeeno
0001
0002
employeeno consumemoney balancemoney date count
0001 1 10 2007-10-01 10:05:02 5
0002 3 50 2007-10-01 10:10:23 30
0001 2 8 2007-10-01 15:01:20 6
0001 1 6 2007-10-02 12:13:28 8
0001 2 4 2007-10-05 08:09:43 9
0002 2 48 2007-10-06 19:23:00 31
0002 5 43 2007-10-07 08:20:16 32
0002 3 10 2007-10-08 17:10:40 34
0002 2 8 2007-10-09 08:01:07 35
0001 1 3 2007-10-09 16:38:16 10
......9999如何写SQL语句查出count(记录号)缺少/丢失记录号的人员,
如:员工0001丢失记录号7
员工0002丢失记录号33结果如下:----------------
employeeno
0001
0002
insert into tb values('0001',1, 10, '2007-10-01 10:05:02', 5 )
insert into tb values('0002',3, 50, '2007-10-01 10:10:23', 30)
insert into tb values('0001',2, 8, '2007-10-01 15:01:20', 6 )
insert into tb values('0001',1, 6, '2007-10-02 12:13:28', 8 )
insert into tb values('0001',2, 4 , '2007-10-05 08:09:43', 9 )
insert into tb values('0002',2, 48, '2007-10-06 19:23:00', 31)
insert into tb values('0002',5, 43, '2007-10-07 08:20:16', 32)
insert into tb values('0002',3, 10, '2007-10-08 17:10:40', 34)
insert into tb values('0002',2, 8, '2007-10-09 08:01:07', 35)
insert into tb values('0001',1, 3, '2007-10-09 16:38:16', 10)
goselect m.employeeno,m.[count]+1 缺号 from
(select px=(select count(1) from tb where employeeno=a.employeeno and [count]<a.[count])+1 , * from tb a) m,
(select px=(select count(1) from tb where employeeno=a.employeeno and [count]<a.[count])+1 , * from tb a) n
where m.employeeno = n.employeeno and m.px = n.px - 1 and m.[count] <> n.[count] - 1drop table tb/*
employeeno 缺号
---------- -----------
0001 7
0002 33(所影响的行数为 2 行)
*/
create table tb(employeeno varchar(10),consumemoney int, balancemoney int,date datetime,[count] int)
insert into tb values('0001',1,10,'2007-10-01 10:05:02', 5 )
insert into tb values('0002',3,50,'2007-10-01 10:10:23', 30)
insert into tb values('0001',2, 8,'2007-10-01 15:01:20', 6 )
insert into tb values('0001',1, 6,'2007-10-02 12:13:28', 8 )
insert into tb values('0001',2, 4,'2007-10-05 08:09:43', 9 )
insert into tb values('0002',2,48,'2007-10-06 19:23:00', 31)
insert into tb values('0002',5,43,'2007-10-07 08:20:16', 32)
insert into tb values('0002',3,10,'2007-10-08 17:10:40', 34)
insert into tb values('0002',2, 8,'2007-10-09 08:01:07', 35)
insert into tb values('0001',1, 3,'2007-10-09 16:38:16', 10)
insert into tb values('0001',1, 3,'2007-10-09 16:38:16', 12)
insert into tb values('0002',2, 8,'2007-10-09 08:01:07', 37)
goselect m.employeeno,m.[count]+1 缺号 from
(select px=(select count(1) from tb where employeeno=a.employeeno and [count]<a.[count])+1 , * from tb a) m,
(select px=(select count(1) from tb where employeeno=a.employeeno and [count]<a.[count])+1 , * from tb a) n
where m.employeeno = n.employeeno and m.px = n.px - 1 and m.[count] <> n.[count] - 1
order by m.employeeno,缺号drop table tb/*
employeeno 缺号
---------- -----------
0001 7
0001 11
0002 33
0002 36(所影响的行数为 4 行)
*/
insert into tb values('0001',1, 10, '2007-10-01 10:05:02', 5 )
insert into tb values('0002',3, 50, '2007-10-01 10:10:23', 30)
insert into tb values('0001',2, 8, '2007-10-01 15:01:20', 6 )
insert into tb values('0001',1, 6, '2007-10-02 12:13:28', 9 )
insert into tb values('0001',2, 4 , '2007-10-05 08:09:43', 10 )
insert into tb values('0002',2, 48, '2007-10-06 19:23:00', 31)
insert into tb values('0002',5, 43, '2007-10-07 08:20:16', 32)
insert into tb values('0002',3, 10, '2007-10-08 17:10:40', 34)
insert into tb values('0002',2, 8, '2007-10-09 08:01:07', 36)
insert into tb values('0001',1, 3, '2007-10-09 16:38:16', 12)
goCREATE PROCEDURE PRO_TOTALNOTEXIST
@s INT
AS
BEGINDECLARE @m INT
SELECT @m = MAX([count]) FROM tbDECLARE @t TABLE ( id INT IDENTITY(1,1) NOT NULL, m bit )
--生成一个补号表,根据该表中最大的记录号数
SET ROWCOUNT @m
INSERT INTO @t SELECT 0 FROM sysobjects
SET ROWCOUNT 0--如果指定开始编号不为0
IF @s > 0
BEGIN SELECT employeeno,[count] = id FROM
(
SELECT A.employeeno,
start = A.[count]+1,
[end] = (SELECT MIN([count]) FROM tb AA WHERE employeeno = A.employeeno AND [count] > A.[count]
AND NOT EXISTS ( SELECT * FROM tb WHERE employeeno = AA.employeeno AND [count] = AA.[count] - 1) )-1
FROM (SELECT employeeno,[count] FROM tb
UNION ALL
SELECT DISTINCT employeeno,@s-1 FROM tb) A,
(SELECT employeeno,[count]=MAX([count]) FROM tb B GROUP BY employeeno) B
WHERE A.employeeno = B.employeeno AND B.[count]>A.[count] AND
NOT EXISTS ( SELECT * FROM tb WHERE [count] = A.[count] + 1 AND employeeno = A.employeeno )
) B, @t A WHERE A.id BETWEEN B.start AND B.[END] ORDER BY employeeno, [count]
END
ELSE
SELECT employeeno,[count] = id FROM
(
SELECT A.employeeno,
start = A.[count]+1,
[end] = (SELECT MIN([count]) FROM tb AA WHERE employeeno = A.employeeno AND [count] > A.[count]
AND NOT EXISTS ( SELECT * FROM tb WHERE employeeno = AA.employeeno AND [count] = AA.[count] - 1) )-1
FROM tb A,
(SELECT employeeno,[count]=MAX([count]) FROM tb B GROUP BY employeeno) B
WHERE A.employeeno = B.employeeno AND B.[count]>A.[count] AND
NOT EXISTS ( SELECT * FROM tb WHERE [count] = A.[count] + 1 AND employeeno = A.employeeno )
) B, @t A WHERE A.id BETWEEN B.start AND B.[END] ORDER BY employeeno, [count]
END
GO
--不指定编号的情况下
EXEC dbo.PRO_TOTALNOTEXIST 0
--指定编号为3
EXEC DBO.PRO_TOTALNOTEXIST 3DROP PROCEDURE PRO_TOTALNOTEXIST
drop table tb
employeeno count
---------- -----------
0001 7
0001 8
0001 11
0002 33
0002 35(所影响的行数为 5 行)
(所影响的行数为 36 行)employeeno count
---------- -----------
0001 3
0001 4
0001 7
0001 8
0001 11
0002 3
0002 4
0002 5
0002 6
0002 7
0002 8
0002 9
0002 10
0002 11
0002 12
0002 13
0002 14
0002 15
0002 16
0002 17
0002 18
0002 19
0002 20
0002 21
0002 22
0002 23
0002 24
0002 25
0002 26
0002 27
0002 28
0002 29
0002 33
0002 35(所影响的行数为 34 行)如果间隔数据大于1,那么就需要用存储过程进行补号,一条语句应该完成不了!