一个复杂SQL查询的结果利用aspnetpager分页显示问题

Question

有一个SQL查询稍微复杂了点,现在想利用aspnetpager进行分页，放到视图中发现视图中不能做临时表，想利用这段SQL或者转换成存储过程结合 aspnetpager 进行分页。下面的SQL语句的查询结果怎么结合aspnetpager进行分页呢？请朋友们帮忙看看。SELECT  tb_On_Tick_Info.On_Tick_ID_Int,tb_On_Tick_Info.On_Tick_SellDatetime_Dtm,tb_On_Tick_Info.On_Tick_TicketsNum_Str,  tb_Department_Info.Dept_Name_Str,  tb_User_Info.User_Name_Str,  
  tb_On_Tick_Info.On_Tick_SellNumber_Str,  tb_On_Tick_Info.On_Tick_ShouldPay_Dec,  tb_On_Tick_Info.On_Tick_Count_Int,  
  tb_On_Tick_Info.On_Tick_Discount_Dec,  tb_On_Tick_Details.On_Tick_Details_StartNo_Int, CHARINDEX(N'a',  
  tb_On_Tick_Info.On_Tick_Note_Text) AS Expr3, tb_User_Info_1.User_Name_Str AS Expr1,  tb_Ticket_Type.TicketType_Name_Dec,  
 COUNT( tb_On_Tick_Details.On_Tick_Details_ID_Int) AS Expr2 ,tb_Department_Info.Dept_ID_Int
into #temp 
FROM tb_User_Info INNER JOIN 
  tb_On_Tick_Info ON  tb_User_Info.User_ID_Int =  tb_On_Tick_Info.On_Tick_SellPerson_Int INNER JOIN 
  tb_Department_Info ON  tb_User_Info.User_DepartID_Int =  tb_Department_Info.Dept_ID_Int INNER JOIN 
  tb_User_Info AS tb_User_Info_1 ON  tb_On_Tick_Info.On_Tick_PayPerson_Int = tb_User_Info_1.User_ID_Int INNER JOIN 
  tb_On_Tick_Details ON  tb_On_Tick_Info.On_Tick_SellNumber_Str =  tb_On_Tick_Details.On_Tick_SellNumber_Str INNER JOIN 
  tb_Ticket_Type ON  tb_On_Tick_Details.On_Tick_Details_TicketsType_Int =  tb_Ticket_Type.TicketType_ID_Int  
GROUP BY  tb_On_Tick_Info.On_Tick_SellDatetime_Dtm,tb_On_Tick_Info.On_Tick_TicketsNum_Str,  tb_Department_Info.Dept_Name_Str,  tb_User_Info.User_Name_Str,  
   tb_On_Tick_Info.On_Tick_SellNumber_Str,  tb_On_Tick_Info.On_Tick_ShouldPay_Dec,  tb_On_Tick_Info.On_Tick_Count_Int,  
   tb_On_Tick_Info.On_Tick_Discount_Dec, CHARINDEX(N'a',  tb_On_Tick_Info.On_Tick_Note_Text), tb_User_Info_1.User_Name_Str,  
   tb_Ticket_Type.TicketType_Name_Dec,  tb_On_Tick_Details.On_Tick_Details_StartNo_Int ,tb_Department_Info.Dept_ID_Int,tb_On_Tick_Info.On_Tick_ID_Int
declare @sql varchar(8000) 
set @sql = 'select  CONVERT(varchar(12) , On_Tick_SellDatetime_Dtm, 111 ) as On_Tick_SellDatetime_Dtm,Dept_Name_Str,User_Name_Str,On_Tick_SellNumber_Str,convert(varchar(15), On_Tick_ShouldPay_Dec) as On_Tick_ShouldPay_Dec,On_Tick_Count_Int,On_Tick_Discount_Dec' 
select @sql=@sql+',sum(case tickettype_name_dec when '''+tickettype_name_dec+''' then expr2 else 0 end) ['+tickettype_name_dec+']' 
 from (select distinct tickettype_name_dec from tb_Ticket_Type ) as a 
set @sql=@sql+' ,expr3,Expr1,On_Tick_TicketsNum_Str,Dept_ID_Int,On_Tick_ID_Int from #temp  
group by  On_Tick_SellDatetime_Dtm,Dept_Name_Str,On_Tick_TicketsNum_Str,User_Name_Str,On_Tick_SellNumber_Str,On_Tick_ShouldPay_Dec,On_Tick_Count_Int, 
On_Tick_Discount_Dec ,expr3,Expr1,Dept_ID_Int,On_Tick_ID_Int order by On_Tick_SellDatetime_Dtm ' 
exec(@sql)  

Answer 1

SQL2005 存储过程参照
http://www.cnblogs.com/lizhao/articles/1527988.html

Answer 2

在网络上查到的是直接对一个表进行分页操作，如下：
ALTER PROCEDURE dbo.Article_GetArticleByState_CategoryID
(
@state int,
@CategoryID int,
@startIndex int,
@pageSize int
)
AS
begin
WITH ArticleList AS (
   SELECT ROW_NUMBER() OVER (ORDER BY PublishTime DESC) AS ROW,ArticleID,title,author,PublishTime,source,state from Article where state = @state and CategoryID = @CategoryID
   )SELECT ArticleID,title,author,PublishTime,source,state from ArticleList WHERE Row between @startIndex and @startIndex+@pageSize-1
end而我这个是个复杂查询，也没有办法放到视图中，不知道怎么搞。

Answer 3

关键在于 我的这个SQL语句查询出来的结果 分页存储过程怎么用呢

Answer 4

如果能将这个SQL语句整合一下，避免产生临时表，也可以
SELECT  tb_On_Tick_Info.On_Tick_ID_Int,tb_On_Tick_Info.On_Tick_SellDatetime_Dtm,tb_On_Tick_Info.On_Tick_TicketsNum_Str,  tb_Department_Info.Dept_Name_Str,  tb_User_Info.User_Name_Str,  
  tb_On_Tick_Info.On_Tick_SellNumber_Str,  tb_On_Tick_Info.On_Tick_ShouldPay_Dec,  tb_On_Tick_Info.On_Tick_Count_Int,  
  tb_On_Tick_Info.On_Tick_Discount_Dec,  tb_On_Tick_Details.On_Tick_Details_StartNo_Int, CHARINDEX(N'a',  
  tb_On_Tick_Info.On_Tick_Note_Text) AS Expr3, tb_User_Info_1.User_Name_Str AS Expr1,  tb_Ticket_Type.TicketType_Name_Dec,  
 COUNT( tb_On_Tick_Details.On_Tick_Details_ID_Int) AS Expr2 ,tb_Department_Info.Dept_ID_Int
into #temp 
FROM tb_User_Info INNER JOIN 
  tb_On_Tick_Info ON  tb_User_Info.User_ID_Int =  tb_On_Tick_Info.On_Tick_SellPerson_Int INNER JOIN 
  tb_Department_Info ON  tb_User_Info.User_DepartID_Int =  tb_Department_Info.Dept_ID_Int INNER JOIN 
  tb_User_Info AS tb_User_Info_1 ON  tb_On_Tick_Info.On_Tick_PayPerson_Int = tb_User_Info_1.User_ID_Int INNER JOIN 
  tb_On_Tick_Details ON  tb_On_Tick_Info.On_Tick_SellNumber_Str =  tb_On_Tick_Details.On_Tick_SellNumber_Str INNER JOIN 
  tb_Ticket_Type ON  tb_On_Tick_Details.On_Tick_Details_TicketsType_Int =  tb_Ticket_Type.TicketType_ID_Int  
GROUP BY  tb_On_Tick_Info.On_Tick_SellDatetime_Dtm,tb_On_Tick_Info.On_Tick_TicketsNum_Str,  tb_Department_Info.Dept_Name_Str,  tb_User_Info.User_Name_Str,  
   tb_On_Tick_Info.On_Tick_SellNumber_Str,  tb_On_Tick_Info.On_Tick_ShouldPay_Dec,  tb_On_Tick_Info.On_Tick_Count_Int,  
   tb_On_Tick_Info.On_Tick_Discount_Dec, CHARINDEX(N'a',  tb_On_Tick_Info.On_Tick_Note_Text), tb_User_Info_1.User_Name_Str,  
   tb_Ticket_Type.TicketType_Name_Dec,  tb_On_Tick_Details.On_Tick_Details_StartNo_Int ,tb_Department_Info.Dept_ID_Int,tb_On_Tick_Info.On_Tick_ID_Int
declare @sql varchar(8000) 
set @sql = 'select  CONVERT(varchar(12) , On_Tick_SellDatetime_Dtm, 111 ) as On_Tick_SellDatetime_Dtm,Dept_Name_Str,User_Name_Str,On_Tick_SellNumber_Str,convert(varchar(15), On_Tick_ShouldPay_Dec) as On_Tick_ShouldPay_Dec,On_Tick_Count_Int,On_Tick_Discount_Dec' 
select @sql=@sql+',sum(case tickettype_name_dec when '''+tickettype_name_dec+''' then expr2 else 0 end) ['+tickettype_name_dec+']' 
 from (select distinct tickettype_name_dec from tb_Ticket_Type ) as a 
set @sql=@sql+' ,expr3,Expr1,On_Tick_TicketsNum_Str,Dept_ID_Int,On_Tick_ID_Int from #temp  
group by  On_Tick_SellDatetime_Dtm,Dept_Name_Str,On_Tick_TicketsNum_Str,User_Name_Str,On_Tick_SellNumber_Str,On_Tick_ShouldPay_Dec,On_Tick_Count_Int, 
On_Tick_Discount_Dec ,expr3,Expr1,Dept_ID_Int,On_Tick_ID_Int order by On_Tick_SellDatetime_Dtm ' 
exec(@sql)

Answer 5

分页存储过程,在SQL2000中,用到了临时表,临时表一般只存储一个自增的标识+关健字
             在SQL2005中,用到视图和ROW_NUMBER()