Create PROCEDURE [dbo].[P_GetPagedOrders2005]
(@startIndex INT,
@endindex INT
)
AS
select * from (SELECT ROW_NUMBER() OVER(ORDER BY IPid DESC) AS rownum,
[IPid],[IPFrom],[IPTo],[IPLocation],[IPCity],[IPToNumber],[IPFromNumber] from IPInfo) as U
WHERE rownum between @startIndex and @endIndex
GO
代码
复制代码 代码如下:
--下方可以忽略
--我用到的是存储过程：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[pro_pager]
(@startIndex INT,
@endindex INT,
@strwhere varchar(200)
)
AS
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
where 1=1 +@strwhere
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 into ##t 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 )
--select * from ##t
select * from (SELECT ROW_NUMBER() OVER(ORDER BY on_tick_id_int DESC) AS rownum,
* from ##t) as U
WHERE rownum between @startIndex and @endIndex
drop table ##t
您可能感兴趣的文章: