我要求的SQL语句是这样的
select * from companyview where Keyword=@keyword and CompanyType=@Searchtp
union
select * from companyview where keyword like '%@keyword%' or CompanyName like '%@keyword%' order by KeySequence desc但是不知道要怎么样才能分页显示?本来想套这段代码的,但是总是套不上。
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
select * from companyview where Keyword=@keyword and CompanyType=@Searchtp
union
select * from companyview where keyword like '%@keyword%' or CompanyName like '%@keyword%' order by KeySequence desc但是不知道要怎么样才能分页显示?本来想套这段代码的,但是总是套不上。
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
(
@SQL nVARCHAR(4000),
@Page int,
@RecsPerPage int,
@ID VARCHAR(255),
@Sort VARCHAR(255)
)
ASDECLARE @Str nVARCHAR(4000)SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN
(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@SortPRINT @StrEXEC sp_ExecuteSql @Str
GO这个行吗??
http://community.csdn.net/Expert/topic/3587/3587201.xml?temp=7.497805E-02
因为我的SQL是用union连接两次查询的结果的
另外,关于“风云”的分页字段不知是否可以为非连续的数字?
哪位好心人帮帮我?
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
--KSLID 标识 自动编号
--实现目的:分页显示查询结果
--查询SQL语句为:
--select * from 表 where Keyword=@keyword and CompanyType=@Searchtp union select * from 表 where (Keyword like @keyword OR companyName like @keyword) and CompanyType=@Searchtp order by KeySequence
--以下分页的存储过程出错,麻烦各位大侠帮忙看看哪里错了,感激不尽?CREATE PROCEDURE sp_page
@keyword nvarchar(100), --搜索关键字
@pagesize int, --每页记录数
@Searchtp tinyint, --查询类型
@page int, --指定页
@Total int OUTPUT --记录总数
ASdeclare @tb varchar(20)
declare @sql nvarchar(4000)set @tb='COMPANYVIEW' --视图名称SET @sql='SELECT @Total=COUNT(*) FROM '+@tb+' where Keyword='+@keyword+' and CompanyType='+@Searchtp+' union select * from '+@tb+' where keyword like '+''%'+@keyword+'%'' or CompanyName like '%'+@keyword+'%'
EXEC sp_executesql @sql,N'@Total int OUTPUT',@Total OUTPUT--计算查询到的记录总数set @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+
' * from '+@tb+' where Keyword='+@keyword+' and CompanyType='+@Searchtp+' union select * from '+@tb+' where keyword like '+''%'+@keyword+'%'' or CompanyName like '%'+@keyword+'%'+
' and KSLID<(SELECT MIN(KSLID) '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
' KSLID from '+@tb+' where Keyword='+@keyword+' and CompanyType='+@Searchtp+' union select * from '+@tb+' where keyword like '+''%'+@keyword+'%'' or CompanyName like '%'+@keyword+'% order by KSLID desc) t) order by KeySequence desc'
IF @page=1 --第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+'* FROM '+@tb+
'where Keyword='+@keyword+' and CompanyType='+@Searchtp+' union select * from '+@tb+' where keyword like '+''%'+@keyword+'%'' or CompanyName like '%'+@keyword+'%'+
'ORDER BY KeySequence desc'
EXEC(@sql)
GO