declare @tb table
(
iGroup int,ICK int
)
insert @tb
select 20,1 union
select 20,2 union
select 20,4 union
select 20,5 union
select 21,1 union
select 21,3 union
select 21,5--查询
select top 100 identity(int,1,1) id into # from syscolumns a,syscolumns bselect T.iGroup
,A.id
from (select distinct iGroup from @tb)T
join # A on 1>0
left join @tb B on A.id=B.ICK and T.iGroup=B.iGroup
where A.id<=(select max(ICK) from @tb)
and
B.ICK is null
order by T.iGroupdrop table #--结果
/*
(所影响的行数为 100 行)iGroup id
----------- -----------
20 3
21 2
21 4(所影响的行数为 3 行)
*/
(
iGroup int,ICK int
)
insert @tb
select 20,1 union
select 20,2 union
select 20,4 union
select 20,5 union
select 21,1 union
select 21,3 union
select 21,5--查询
select top 100 identity(int,1,1) id into # from syscolumns a,syscolumns bselect T.iGroup
,A.id
from (select distinct iGroup from @tb)T
join # A on 1>0
left join @tb B on A.id=B.ICK and T.iGroup=B.iGroup
where A.id<=(select max(ICK) from @tb)
and
B.ICK is null
order by T.iGroupdrop table #--结果
/*
(所影响的行数为 100 行)iGroup id
----------- -----------
20 3
21 2
21 4(所影响的行数为 3 行)
*/
select top 100 identity(int,1,1) id into # from syscolumns a,syscolumns b
这个值没有办法定。而且
我说的以iGroup分ICK,并不是用ICK最大的值就可以的