CREATE procedure GetProductsInCategoryintCount
( @CategoryID Int,
@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(ProductID) from Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID where productcategory.categoryid = @categoryid
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select ProductID from Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID where productcategory.categoryid = @categoryid order by ProductID desc
select O.* from Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID O,@indextable t where O.ProductID=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
GO
( @CategoryID Int,
@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(ProductID) from Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID where productcategory.categoryid = @categoryid
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select ProductID from Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID where productcategory.categoryid = @categoryid order by ProductID desc
select O.* from Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID O,@indextable t where O.ProductID=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
GO
解决方案 »
- mssql 数据导出为excel表格的问题
- 求一SQL备份语句
- 如何用简单的SQL语句实现这个判断的存储过程?********** 出手从来100分******************
- 问个理论问题,有200000条数据,哪种查询查询速度比较快??
- 一个select语句count多个表的同一个字段问题~~
- 求助哈,一个多表查询问题,希望大家能帮帮我
- 触发器执行操作时如何判断字段的内容满足要求后再执行(insert)
- 求一查詢語句 弄了半天但結果出來還是有誤
- 行列转换的SQL问题,急!!
- 问一下SQL SERVER有在线迁移的功能吗?
- 现在有10万条数据,想加个“ID”主键,并且让它从1到10万编号。怎么操作?
- 求助:取用户列表的存储过程,不知道哪错了?
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id-- 改为
select O.*
from Product
INNER JOIN ProductCategory O
ON Product.ProductID = ProductCategory.ProductID
INNER JOIN @indextable t
ON O.ProductID=t.nid
WHERE t.id>@PageLowerBound and t.id<=@PageUpperBound
order by t.id
JOIN方式要么统一使用WHERE的形式, 要么用JOIN的方式, 不能混用.
列名"productid"不明确
列前缀Productcategory与查询中所用的表名或别名不配