SELECT TOP 10 * FROM table WHERE (dTime NOT IN (SELECT TOP 20 * FROM table ORDER BY dTime))
ORDER BY dTime字查询的表名与 table 重复时会出错:当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。怎么解决? 我不想用游标 听说数据量大时效率不高
谢谢!
ORDER BY dTime字查询的表名与 table 重复时会出错:当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。怎么解决? 我不想用游标 听说数据量大时效率不高
谢谢!
ORDER BY dTime
SELECT TOP 10 * FROM table1 a WHERE dTime NOT IN (SELECT TOP 20 dtime FROM table1 ORDER BY dTime)
ORDER BY dTime
(
@pageSize int,
@pageIndex int
)
as
BEGIN
declare @Sql nvarchar(Max)
declare @sqlCount nvarchar(max)
declare @TableName nvarchar(max)
declare @Fields nvarchar(max)
declare @where nvarchar(max)
declare @orderBy nvarchar(max)
declare @groupBy nvarchar(max)
declare @BeginIndex int
declare @EndIndex intset @TableName=N' person '
set @Fields =N'id, name, age '
set @where=N' age >12 '
set @orderBy=N' id '
set @groupBy=N' '
set @BeginIndex=(@pageIndex-1) * @pageSize
set @EndIndex=@pageIndex * @pageSizeset @sql=N'
BEGIN WITH NewTable As(select ' +@fields+ ', Row_Number() over(order by' +@orderBy+ ') as NewRowNumber
from ' +@TableName+ ' where ' +@where+ ')
select * from NewTable where NewRowNumber > ' +convert(nvarchar(255),@BeginIndex)+ ' AND NewRowNumber <= ' +convert(nvarchar(255),@EndIndex)+'
End'
set @sqlCount= N'
BEGIN WITH otherTable As(
select ' +@fields+ ',Row_Number() over(order by ' +@orderBy+ ') as RowNumber
from ' +@TableName+ ' where ' +@where+ ')
select Count(*) as Row from otherTable
End'Execute Sp_Executesql @Sql
Execute Sp_ExecuteSql @sqlCount
END
ORDER BY dTime