求一条查询(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
