select top 3 id from tabel where id not in(select top 3 id from table) union join select top 3 id from table union join select top 3 id from tabel where id not in(select top 6 id from table)
declare @sql nvarchar(4000) set @sql = 'insert into zi' declare @i int set @i = 1 while @i <100 begin set @sql = @sql +' select '+cast(@i as nvarchar(10))+ ' union all ' set @i =@i +1 end set @sql = @sql +' select 100' print @sql exec(@sql)
declare @j int set @j = 4--选择输出的个数 declare @k int set @k = 5--从第几个数开始 declare @sql nvarchar(4000) set @sql = '' declare @sql1 nvarchar(4000) set @sql1 = '' declare @sql2 nvarchar(4000) set @sql2 = '' declare @sql3 nvarchar(4000) set @sql3 = '' set @sql = @sql +'select top '+ cast(@k as nvarchar(10)) +' id from zi' set @sql1 = @sql1 +'select top '+ cast(@j as nvarchar(10)) +'id from zi where id not in( '+@sql +')' set @sql2 = @sql2 +'select id from zi where id not in (select top '+cast((@k+@j) as nvarchar(10))+'id from zi)' set @sql3 = @sql1+' union all ' set @sql3=@sql3+@sql set @sql3=@sql3 +' union all ' set @sql3=@sql3+@sql2 exec(@sql3)
declare@selectTopasvarchar(20)
set@selectTop='4,5,6'--@selectTop是你动态选择首先输出的行的idEXEC('select * from table_1 where id in('+@selectTop+')union all select * from table_2 where id not in('+@selectTop+')')
alter function decide ( @k int, @j int ) returns nvarchar(4000) as begin declare @sql4 nvarchar(4000) set @sql4 = '' declare @sql1 nvarchar(4000) set @sql1 = '' declare @sql2 nvarchar(4000) set @sql2 = '' declare @sql3 nvarchar(4000) set @sql3 = '' set @sql4 = @sql4 +'select top '+ cast(@k as nvarchar(10)) +' id from zi' set @sql1 = @sql1 +'select top '+ cast(@j as nvarchar(10)) +'id from zi where id not in( '+@sql4 +')' set @sql2 = @sql2 +'select id from zi where id not in (select top '+cast((@k+@j) as nvarchar(10))+'id from zi)' set @sql3 = @sql1+' union all ' set @sql3=@sql3+@sql4 set @sql3=@sql3 +' union all ' set @sql3=@sql3+@sql2
--exec(@sql3) return @sql3 end
函数 declare @s nvarchar(4000) set @s=dbo.decide(4,5) exec(@s) 调用
string sql_Select_Org_ByPager = @" declare @PageSize int declare @PageIndex int declare @PageLowerBound int declare @PageUpperBound int declare @RowsToReturn int SET @PageSize = {0} SET @PageIndex = {1}
--需要返回的结果范围 SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageLowerBound + @PageSize + 1 SET @RowsToReturn = @PageSize * (@PageIndex + 1)
--设置最大返回的行数 SET ROWCOUNT @RowsToReturn
CREATE TABLE #PageIndex ( IndexID int IDENTITY (1, 1) NOT NULL, ID varchar(100) )
insert into #PageIndex(ID) select ID from ORG_INFO {2} order by LEVEL
{3} Where ID in ( SELECT ID FROM #PageIndex WHERE #PageIndex.IndexID > @PageLowerBound AND #PageIndex.IndexID < @PageUpperBound ) order by LEVEL
x是id 个数是n select id images from table a where a>x and a <=x+n union select id images from table a where a <=x union select id images from table a where x>x+n and x <=100
