Select Top 12 hmName,hmID,hmInDate from HumanMain where hmID not in(Select Top 12 hmID from HumanMain)这个没错
可 Select Top 12 hmName,hmID,hmInDate from HumanMain where hmID not in(Select Top 12*2 hmID from HumanMain)
这个就 服务器: 消息 170,级别 15,状态 1,行 1 第 1 行: '2' 附近有语法错误。
到底怎么回事呢? 还有很重要的是我这个 Select Top 12 hmName,hmID,hmInDate from HumanMain where ocid=@ocid and hmID not in (Select Top 12*('"+PageNumber+"' - 1) hmID from HumanMain where ocid=@ocid) 老提示:第 1 行: '(' 附近有语法错误。
到底怎么回事呢?请高手帮忙
可 Select Top 12 hmName,hmID,hmInDate from HumanMain where hmID not in(Select Top 12*2 hmID from HumanMain)
这个就 服务器: 消息 170,级别 15,状态 1,行 1 第 1 行: '2' 附近有语法错误。
到底怎么回事呢? 还有很重要的是我这个 Select Top 12 hmName,hmID,hmInDate from HumanMain where ocid=@ocid and hmID not in (Select Top 12*('"+PageNumber+"' - 1) hmID from HumanMain where ocid=@ocid) 老提示:第 1 行: '(' 附近有语法错误。
到底怎么回事呢?请高手帮忙
set @i=12*2
Select Top 12 hmName,hmID,hmInDate from HumanMain where hmID not in(Select Top (@i) hmID from HumanMain)declare @i int ,@PageNumber int
set @i=12*(@PageNumber-1)
Select Top 12 hmName,hmID,hmInDate from HumanMain
where ocid=@ocid and hmID not in (Select Top ( @i) hmID from HumanMain where ocid=@ocid)
你是分页的吧,写个分存储过程
/*分页查找数据*/
CREATE PROCEDURE [dbo].[GetRecordSet]
@strSql varchar(8000),--查询sql,如select * from [user]
@PageIndex int,--查询当页号
@PageSize int--每页显示记录
AS
set nocount on
declare @p1 int
declare @currentPage int
set @currentPage = 0
declare @RowCount int
set @RowCount = 0
declare @PageCount int
set @PageCount = 0
exec sp_cursoropen @p1 output,@strSql,@scrollopt=1,@ccopt=1,@rowcount=@rowCount output --得到总记录数
select @PageCount=ceiling(1.0*@rowCount/@pagesize) --得到总页数
,@currentPage=(@PageIndex-1)*@PageSize+1
select @RowCount,@PageCount
exec sp_cursorfetch @p1,16,@currentPage,@PageSize
exec sp_cursorclose @p1
set nocount off
GO 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/10/28/4740536.aspx
Select Top 12 hmName,hmID,hmInDate from HumanMain where hmID not in(Select Top (12*2) hmID from HumanMain)
Select Top 12 hmName,hmID,hmInDate from HumanMain where ocid=@ocid and hmID not in (Select Top (12*(@PageNumber - 1)) hmID from HumanMain where ocid=@ocid)
/*
在TOP后面使用变量
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2008-01-02 广东深圳)
*/--SQL SERVER 2005 的写法
use adventureworks
goDECLARE @Percentage int
SET @Percentage = 1
SELECT TOP (@Percentage) PERCENT
Name
FROM Production.Product
ORDER BY Name/*
Name
----------------------
Adjustable Race
All-Purpose Bike Stand
AWC Logo Cap
BB Ball Bearing
Bearing Ball
Bike Wash - Dissolver(6 行受影响)
*/-----------------------------------
--SQL SERVER 2000 的写法
create table a([id] [int])
insert into a(id) values(1)
insert into a(id) values(2)
insert into a(id) values(3)
insert into a(id) values(4)
insert into a(id) values(5)declare @num as int
declare @sql as varchar(2000)
set @num = 2
set @sql = 'select top ' + cast(@num as char) + ' * from a'
exec(@sql)drop table a
/*
id
-----------
1
2
*/
declare @sql varchar(8000)
declare @PageSize int
set @PageSize =12
declare @PageNumber int
set @PageNumber =2
set @sql='select top ' + Convert(varchar, @PageSize)+ ' hmName,hmID,hmInDate from HumanMain where hmID not in (select top'+ Convert(varchar,@PageSize*(@PageNumber - 1))+' hmID from HumanMain)'
exec (@sql)拼串后报这个错误 服务器: 消息 207,级别 16,状态 3,行 1
列名 'top12' 无效。
top 后面少个空格declare @sql varchar(8000)
declare @PageSize int
set @PageSize =12
declare @PageNumber int
set @PageNumber =2
set @sql='select top ' + Convert(varchar, @PageSize)+ ' hmName,hmID,hmInDate from HumanMain where hmID not in (select top '+ Convert(varchar,@PageSize*(@PageNumber - 1))+' hmID from HumanMain)'
exec( @sql)