求一条查询(ID不连续)
如题:
A(字段1)     B(字段2)
AA                 1
AA                 2
BB                 3
BB                 4
BBB               5           ---如何查询到5,7之间有空缺;
CCC               7
...   ...



------解决方案--------------------
--简单的，如:

Select B+1 as B from 表 a
where not exists(
Select * from 表 where B=a.B+1 )
------解决方案--------------------
CREATE TABLE TB(A VARCHAR(10), B INT)
INSERT TB
SELECT 'AA ', 1
UNION SELECT 'AA ', 2
UNION SELECT 'BB ', 3
UNION SELECT 'BB ', 4
UNION SELECT 'BBB ', 5
UNION SELECT 'CCC ', 7

SELECT A,B=CASE WHEN EXISTS(SELECT 1 FROM TB WHERE B=T.B+1) OR EXISTS(SELECT 1 FROM (SELECT M=MAX(B) FROM TB) A WHERE T.B=M)
THEN RTRIM(B)
ELSE RTRIM(B)+ ', '+(SELECT RTRIM(MIN(B)) FROM TB WHERE B> T.B)+ '之间有空缺 ' END
FROM TB T

DROP TABLE TB