现在是想实现分页查询,比如每页只显示30个,怎么写存储过程呢。比如怎么根据id写呢?下面是我现在是存储过程
CREATE PROCEDURE QueryKeyEvent
@nid INT,
@StartTime varchar(20),
@EndTime varchar(20),
@DestObject varchar(100),
@nTimeGap INT, ----------------------时间间隔
AS--set@nid=0
--set @DestObject = '管理中心'
begin-----Select * from KeyEvent end
END
GO
CREATE PROCEDURE QueryKeyEvent
@nid INT,
@StartTime varchar(20),
@EndTime varchar(20),
@DestObject varchar(100),
@nTimeGap INT, ----------------------时间间隔
AS--set@nid=0
--set @DestObject = '管理中心'
begin-----Select * from KeyEvent end
END
GO
@nid INT,
@StartTime varchar(20),
@EndTime varchar(20),
@DestObject varchar(100),
@nTimeGap INT, ----------------------时间间隔
@nType INT, ---------- 查询的 组合方式
@nQueryType INT
AS--set@nid=0
--set @nQueryType = 1
--set @nType = 1
--set @DestObject = '管理中心'
begin-----Select * from KeyEvent
-----Select * from KeyEvent
if @nQueryType = 1 -------------模糊查询
Select * from KeyEvent
if @nQueryType = 2 ---------------精确查询
BEGIN
if @nType = 1 ----------------事件对象的精确查询
begin
SELECT *
FROM keyEvent
WHERE DestObject = @DestObject--and (cast(id as INT))
end if @nType = 2 ---------------------事件对象和结束时间的精确查询
begin
SELECT *
FROM keyEvent
WHERE DestObject = @DestObject and EndTime = @EndTime
end if @nType = 3 ------------------事件对象和开始时间的精确查询
begin
SELECT *
FROM keyEvent
WHERE DestObject = @DestObject and StartTime = @StartTime
end if @nType = 4 ------------------------------- 事件对象和时间间隔的精确查询
begin
select * from keyEvent where (cast(EndTime as INT) - cast(StartTime as INT)) = @nTimeGap and DestObject = @DestObject
end
end
END
GO
搜搜就知道了.
2005可以使用ROW_NUMBER()来处理,
返回结果集分区内行的序列号,每个分区的第一行从 1 开始。语法:
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )备注:
ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。参数:
<partition_by_clause>
将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>
确定将 ROW_NUMBER 值分配给分区中的行的顺序。返回类型:
bigint示例:
以下示例将根据年初至今的销售额,返回 AdventureWorks 中销售人员的 ROW_NUMBER。 USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0以下示例将返回行号为 50 到 60(含)的行,并以 OrderDate 排序。 USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;