sql翻页查询中加去重复数据查询怎么做啊
小弟我的sql语句这样写的：
sql = "select top " + secondly + " * from " + table + " where " + id + " not in(select top " + first + " " + id + " from " + table + ")";
secondly ：设定的翻页大小，
table ：查询的表名，
id ：主键
first 翻页大小*（数据总数-1）
这是控制翻页的，
但现在我要将翻页中去除重复的id数据，并是最新的数据显示。
我不知道怎么写了，还请各位达人帮帮忙！~~
[解决办法]
--这样子翻页来查询如果id唯一是主键，而且只有一个表，翻页是正确的，如果是多表或者id是可以重复的--那么翻页后可能会丢失id重复的数据！--下边的翻页应该可以满足下你的需求，不是很完善！-- 2005 翻页！USE [model]GO/****** 对象: StoredProcedure [dbo].[getpate_2005] 脚本日期: 01/19/2011 19:17:48 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ===================================================-- auhtor : acherat-- modified date : 01/10/2011 20:41:44-- description : 分页存储-- ===================================================ALTER proc [dbo].[getpate_2005](@tbname nvarchar(4000), --表名及多表连接语句@fieldcol nvarchar(4000), --查询的字段名@pagesize int, --分页尺寸@pagecurrent int, --需找寻的页码@strwhere nvarchar(4000) = null, --表查询的限制条件@strorder nvarchar(4000) = null, --表查询的排序@pagecount int output --返回总页数)asbeginset nocount ondeclare @strsql nvarchar(4000) --返回结果集动态SQL语句declare @strtmp nvarchar(4000) --计算总页数动态SQL语句declare @colshow nvarchar(4000)declare @counts int--参数初始化if (@fieldcol is null or @fieldcol = '') set @fieldcol = '*'if (isnull(@pagesize,0) < 1) set @pagesize = 10if (isnull(@pagecurrent,0) < 1) set @pagecurrent = 1if (@strwhere is null or @strwhere = '') set @strwhere = ' 'else set @strwhere = N' where ' + @strwhereif (@strorder is null or @strorder = '') set @strorder = ' 'else set @strorder = N' order by ' + @strorder--总页数设置if @pagecount is null begin set @strtmp = N' select @counts = count(*) from ' + @tbname + @strwhere + @strorder exec sp_executesql @strtmp,N'@counts int output',@counts output set @pagecount = (@counts + @pagesize - 1)/@pagesize end--查询结果设置if (@pagecount >= 1) begin set @colshow = (select dbo.f_strcol(@fieldcol)) set @strsql = N'select ' + @colshow + N' from (select row_number() over (order by getdate())iii,' + @fieldcol +N' from ' + @tbname + @strwhere + @strorder +N')temp where iii between ' + ltrim((@pagecurrent - 1)*@pagesize + 1) +N' and ' + ltrim(@pagecurrent*@pagesize) endexec(@strsql)set nocount offend--辅助函数！USE [model]GO/****** 对象: UserDefinedFunction [dbo].[f_strcol] 脚本日期: 01/19/2011 19:18:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER function [dbo].[f_strcol](@str nvarchar(4000))returns nvarchar(4000)asbegindeclare @strsg nvarchar(4000)declare @strbg nvarchar(4000)declare @stras nvarchar(4000)declare @len intdeclare @dou intdeclare @dian intset @strsg = @str + ','set @strbg = ''set @len = len(@strsg)set @dou = charindex(',',@strsg)set @dian = charindex('.',@strsg)while (@dou > 0)begin set @stras = substring(@strsg,@dian+1,@dou-@dian-1) if charindex('as',@stras) > 0 set @stras = ltrim(rtrim(right(@stras,len(@stras)-charindex('as',@stras)-1))) set @strbg = @strbg + ',' + @stras set @strsg = substring(@strsg,@dou+1,@len-@dou) set @dou = charindex(',',@strsg) set @dian = charindex('.',@strsg) set @len = len(@strsg)endreturn stuff(@strbg,1,1,'')end
[解决办法]
放假了吧！
[解决办法]
sql = "select top " + secondly + " * from (select row_number() over (order by getdate())rn,* from " + table + ") where rn >= " + first;
[解决办法]