Select TOP 14000000 * FROM enps Where id> (Select max(id) FROM (Select TOP 14000020 id FROM enps orDER BY id) t) ORDER BY id DESC 这个语句是写对了,但是效率不是很高,哪位个我改改呀,我实在想不出来了,谢谢 id列是一个自增列,在id列上做了索引,记录大概在1500W
id列是一个自增列 是不是连续的? 如果是连续的,可以这样 Select TOP 14000000 * FROM enps Where id> 14000020 你的数据太多了,可以考虑更换数据库
Select TOP 14000000 * FROM enps Where id> (Select max(id) FROM (Select TOP 14000020 id FROM enps orDER BY id DESC ) t) ORDER BY id DESC
where id> 后面括号中的结果应该是一个值,而你在括号中的结果好像是一个集合,你用 where id in ()试试呢?
SELECT * ,ROW_NUMBER()OVER( ORDER BY ID) AS NUMID FROM enps WHERE NUMID >14000020
WITH TempT AS ( SELECT * FROM enps ) SELECT TOP 14000000 * FROM TempT WHERE id>(SELECT MAX(id) FROM (SELECT TOP 14000020 id FROM TempT ORDER BY id) ORDER BY id DESC
selcet * from ( SELECT * ,ROW_NUMBER()OVER( ORDER BY ID) AS NUMID FROM enps )a WHERE NUMID >14000020
select top 14000000 * from enps WHERE id> (select max(id) from (select top 14000020 id from enps order by id) t) order by id desc--搞不清楚楼主想要做什么 select top 14000020 id from enps order by id --按id升序取前14000020条 (select max(id) from (select top 14000020 id from enps order by id) t) --然后select max(id) from 嵌套,取出前14000020条的最大idselect top 14000000 * from enps WHERE id> (select max(id) from (select top 14000020 id from enps order by id) t) order by id desc --最后是取出大于那个最大id的前14000000条 /*也就是说如果数据足够多,楼主是意思是要取第14000021-28000020条数据?*/
今天才看到一位高手提到很好SQL, 我引用出来,给大家参考.Create PROCEDURE [dbo].[pagination3]@tblName varchar(255), -- 表名@strGetFields varchar(1000) = '*', -- 需要返回的列 @fldName varchar(255)='', -- 排序的字段名@PageSize int = 10, -- 页尺寸@PageIndex int = 1, -- 页码@doCount bit = 0, -- 返回记录总数, 非 0 值则返回@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)ASdeclare @strSQL varchar(5000) -- 主语句declare @strTmp varchar(110) -- 临时变量declare @strOrder varchar(400) -- 排序类型set @tblName='[' + @tblName + ']'if @doCount != 0 begin if @strWhere !='' set @strSQL = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere else set @strSQL = 'select count(*) as Total from ' + @tblName + ''end --以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况elsebegin if @OrderType != 0begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName +'] desc'--如果@OrderType不是0,就执行降序,这句很重要!endelsebegin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc'end if @PageIndex = 1begin if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder else set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder--如果是第一页就执行以上代码,这样会加快执行速度endelsebegin--以下代码赋予了@strSQL以真正执行的SQL代码set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from ' + @tblName + @strOrder + ') as tblTmp)'+ @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderend end print @strsql exec (@strSQL)
Select TOP 14000000 * FROM enpsWhere id> (Select max(id) FROM)
2 你能把你要实现的说明一下吗 我看到都晕
如果是连续的,可以这样
Select TOP 14000000 * FROM enps Where id> 14000020 你的数据太多了,可以考虑更换数据库
SELECT * ,ROW_NUMBER()OVER( ORDER BY ID) AS NUMID
FROM enps
WHERE NUMID >14000020
TempT
AS
(
SELECT * FROM enps
)
SELECT TOP 14000000 * FROM TempT
WHERE id>(SELECT MAX(id) FROM (SELECT TOP 14000020 id FROM TempT ORDER BY id)
ORDER BY id DESC
2、减少对表的查询参考 "使你的 SQL 语句完全优化"
(
SELECT * ,ROW_NUMBER()OVER( ORDER BY ID) AS NUMID
FROM enps
)a
WHERE NUMID >14000020
select top 14000000 * from enps
WHERE
id>
(select max(id) from
(select top 14000020 id from enps order by id)
t)
order by id desc--搞不清楚楼主想要做什么
select top 14000020 id from enps order by id --按id升序取前14000020条
(select max(id) from
(select top 14000020 id from enps order by id)
t) --然后select max(id) from 嵌套,取出前14000020条的最大idselect top 14000000 * from enps
WHERE
id>
(select max(id) from
(select top 14000020 id from enps order by id)
t)
order by id desc
--最后是取出大于那个最大id的前14000000条
/*也就是说如果数据足够多,楼主是意思是要取第14000021-28000020条数据?*/
我引用出来,给大家参考.Create PROCEDURE [dbo].[pagination3]@tblName varchar(255), -- 表名@strGetFields varchar(1000) = '*', -- 需要返回的列 @fldName varchar(255)='', -- 排序的字段名@PageSize int = 10, -- 页尺寸@PageIndex int = 1, -- 页码@doCount bit = 0, -- 返回记录总数, 非 0 值则返回@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)ASdeclare @strSQL varchar(5000) -- 主语句declare @strTmp varchar(110) -- 临时变量declare @strOrder varchar(400) -- 排序类型set @tblName='[' + @tblName + ']'if @doCount != 0 begin if @strWhere !='' set @strSQL = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere else set @strSQL = 'select count(*) as Total from ' + @tblName + ''end --以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况elsebegin if @OrderType != 0begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName +'] desc'--如果@OrderType不是0,就执行降序,这句很重要!endelsebegin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc'end if @PageIndex = 1begin if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder else set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder--如果是第一页就执行以上代码,这样会加快执行速度endelsebegin--以下代码赋予了@strSQL以真正执行的SQL代码set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from ' + @tblName + @strOrder + ') as tblTmp)'+ @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderend end
print @strsql
exec (@strSQL)