USE [xxxxx]
GO
/****** Object: StoredProcedure [dbo].[pro_xxxxx] Script Date: 2023/1/31 14:37:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pro_xxxxx] ---EXEC [proxxxx] '2018-10-22','2018-10-23',68,'PT00004',1,15,0
(
@Benintime date ,
@Endtime date ,
@Mtype INT,
@Lmarkid NVARCHAR(4000),
@pageIndex INT,
@pageSize INT,
@ss NVARCHAR(10),
@isExcel INT =0
)
AS
begin
DECLARE @startRow INT;
DECLARE @endRow INT;
SET @startRow = ( @pageIndex - 1 ) * @pageSize + 1;
SET @endRow = @pageSize * @pageIndex;
DECLARE @Activenum DECIMAL(18,2) = 0;
DECLARE @ActiveSta INT = 0;
DECLARE @AwayFromZerostr INT = 0;
DECLARE @CapvalueActivestr INT = 0;
DECLARE @Active INT
DECLARE @Capvalue INT
DECLARE @Percentage DECIMAL(18,2)
IF(@isExcel=0)
BEGIN
SELECT *
INTO #tem1
FROM (
SELECT [Phonetype],[Lmarkid],[Reg],[Speechnum],[Ddate],[Mtype],[Subname],[Active],[Recent2],[Recent2login],[login3num],[PayQuantityA],
[PayQuantityB],[PayQuantityC],[PayQuantityD] ,[PayAmountA],[PayAmountB],[PayAmountC],[PayAmountD],[Recent7],[Recent7login],[PercentAge],[Unitprice],[Gtype],[Capvalue]
,[NumberofStarts],[Maxuseronlinepeak],[DailyActiveUsers],[Recent3],[Recent3login],[InRoomOne],[InRoomThree],[InRoomFive] ,[Dtake] ,CompanyName,guestnum,
[MaxuseronlineTime],[OnlineTimes],[OnlinetimeOne],[OnlinetimeThree],[OnlinetimeFive],ROW_NUMBER() over(order by Ddate DESC) as pageID
FROM [dbo].[xxxxxx] With ( Nolock )
where [Ddate]>=@Benintime
And [Ddate]<@Endtime
And ( Active > 0 Or Reg > 0 Or PayAmountC > 0 Or PayAmountB > 0 Or PayAmountA > 0 Or PayAmountD > 0 Or [guestnum] > 0 )
And Mtype = @Mtype
And 1 = (Case When @Lmarkid <> '' And Lmarkid IN ( select col from [dbo].[f_splitComma](@Lmarkid,','))Then 1
When @Lmarkid = '' Then 1
Else 0 End )
) as a
--循环判断概率(游标)@@@@@@@@@@@@@@@@@@@
--创建游标
DECLARE @cursor CURSOR;
--设定游标欲操作的数据集
SET @cursor = CURSOR FOR select Active,Capvalue,Percentage from #tem1
--打开游标
OPEN @cursor;
--移动游标指向到第一条数据,提取第一条数据存放在变量中
FETCH NEXT FROM @cursor INTO @Active,@Capvalue,@Percentage;
--如果上一次操作成功则继续循环
WHILE ( @@fetch_status = 0 )
BEGIN
If ( @ss = '1' )
Begin
Set @ActiveSta += Round(@Active * (1 - @Percentage),0);
Set @AwayFromZerostr += Round(@Active * (1 - @Percentage),0);
End
Else
Begin
Set @ActiveSta += @Active;
Set @AwayFromZerostr += @Active
End
If ( @Capvalue <> 0 )
Begin
If ( @Capvalue >= Round(@Active * (1 - @Percentage),0) )
Begin
Set @Activenum += Round(@Active * (1 - @Percentage),0);
End
Else
Begin
Set @Activenum += @Capvalue
End
End
Else
Begin
Set @Activenum += Round(@Active * (1 - @Percentage),0);
End
If ( @Capvalue < Round(@Active * (1 - @Percentage),0) And @Capvalue > 0 )
Begin
Set @CapvalueActivestr += @Capvalue;
End
Else
Begin
Set @CapvalueActivestr += Round(@Active * (1 - @Percentage),0);
End
--继续提下一行
FETCH NEXT FROM @cursor INTO @Active,@Capvalue,@Percentage;
END;
--关闭游标
CLOSE @cursor;
--删除游标
DEALLOCATE @cursor;
SELECT *
FROM #tem1
WHERE pageID BETWEEN @startRow AND @endRow
Select COUNT(1)
From #tem1
Select SUM(Reg) as Reg,
SUM(Login3num) as Login3num,
SUM(PayAmountA) as PayAmountA,
SUM(PayAmountB) as PayAmountB,
SUM(PayAmountC) as PayAmountC,
SUM(PayAmountD) as PayAmountD,
SUM(PayQuantityA) as PayQuantityA,
SUM(PayQuantityB) as PayQuantityB,
SUM(PayQuantityC) as PayQuantityC,
SUM(PayQuantityD) as PayQuantityD,
SUM(DailyActiveUsers) as DailyActiveUsers,
SUM(NumberofStarts) as NumberofStarts,
SUM(Dtake) as Dtake,
SUM(guestnum) as guestnum,
sum([MaxuseronlineTime])as [MaxuseronlineTime],
sum([Speechnum])as [Speechnum],
sum(InRoomOne)as InRoomOne,
sum(InRoomThree)as InRoomThree,
sum(InRoomFive)as InRoomFive,
sum([OnlineTimes])as [OnlineTimes],
sum([OnlinetimeOne])as [OnlinetimeOne],
sum([OnlinetimeThree])as [OnlinetimeThree],
sum([OnlinetimeFive])as [OnlinetimeFive],
@AwayFromZerostr as AwayFromZerostr,
@CapvalueActivestr as CapvalueActivestr,
@Activenum as Activenum,
@ActiveSta as ActiveSta
From #tem1
Drop Table #tem1
END
--导出表格时查询非H5的数据
IF(@isExcel=1 AND (NOT EXISTS (SELECT * FROM dbo.xxx WHERE ItemName LIKE '%H5%' AND PlatId=@Mtype)))
BEGIN
SELECT *
INTO #tem2
FROM (
SELECT [Phonetype],[Lmarkid],[Reg],[Speechnum],[Ddate],[Mtype],[Subname],[Active],[Recent2],[Recent2login],[login3num],[PayQuantityA],
[PayQuantityB],[PayQuantityC],[PayQuantityD] ,[PayAmountA],[PayAmountB],[PayAmountC],[PayAmountD],[Recent7],[Recent7login],[PercentAge],[Unitprice],[Gtype],[Capvalue]
,[NumberofStarts],[Maxuseronlinepeak],[DailyActiveUsers],[Recent3],[Recent3login],[InRoomOne],[InRoomThree],[InRoomFive]
,[Dtake] ,CompanyName, ROW_NUMBER() over(order by Ddate DESC) as pageID
FROM [dbo].[xxxxx] With ( Nolock )
where [Ddate]>=@Benintime
And [Ddate]<@Endtime
And Mtype = @Mtype
And 1 = (Case When @Lmarkid <> '' And Lmarkid IN ( select col from [dbo].[f_splitComma](@Lmarkid,','))Then 1
When @Lmarkid = '' Then 1
Else 0 End )
) as a
--循环判断概率(游标)@@@@@@@@@@@@@@@@@@@
--创建游标
DECLARE @cursorA CURSOR;
--设定游标欲操作的数据集
SET @cursorA = CURSOR FOR select Active,Capvalue,Percentage from #tem2
--打开游标
OPEN @cursorA;
--移动游标指向到第一条数据,提取第一条数据存放在变量中
FETCH NEXT FROM @cursorA INTO @Active,@Capvalue,@Percentage;
--如果上一次操作成功则继续循环
WHILE ( @@fetch_status = 0 )
BEGIN
If ( @ss = '1' )
Begin
Set @ActiveSta += Round(@Active * (1 - @Percentage),0);
Set @AwayFromZerostr += Round(@Active * (1 - @Percentage),0);
End
Else
Begin
Set @ActiveSta += @Active;
Set @AwayFromZerostr += @Active
End
If ( @Capvalue <> 0 )
Begin
If ( @Capvalue >= Round(@Active * (1 - @Percentage),0) )
Begin
Set @Activenum += Round(@Active * (1 - @Percentage),0);
End
Else
Begin
Set @Activenum += @Capvalue
End
End
Else
Begin
Set @Activenum += Round(@Active * (1 - @Percentage),0);
End
If ( @Capvalue < Round(@Active * (1 - @Percentage),0) And @Capvalue > 0 )
Begin
Set @CapvalueActivestr += @Capvalue;
End
Else
Begin
Set @CapvalueActivestr += Round(@Active * (1 - @Percentage),0);
End
--继续提下一行
FETCH NEXT FROM @cursorA INTO @Active,@Capvalue,@Percentage;
END;
--关闭游标
CLOSE @cursorA;
--删除游标
DEALLOCATE @cursorA;
SELECT *
FROM #tem2
WHERE pageID BETWEEN @startRow AND @endRow
Select COUNT(1)
From #tem2
Select SUM(Reg) as Reg,
SUM(Login3num) as Login3num,
SUM(PayAmountA) as PayAmountA,
SUM(PayAmountB) as PayAmountB,
SUM(PayAmountC) as PayAmountC,
SUM(PayAmountD) as PayAmountD,
SUM(PayQuantityA) as PayQuantityA,
SUM(PayQuantityB) as PayQuantityB,
SUM(PayQuantityC) as PayQuantityC,
SUM(PayQuantityD) as PayQuantityD,
SUM(DailyActiveUsers) as DailyActiveUsers,
SUM(NumberofStarts) as NumberofStarts,
SUM(Dtake) as Dtake,
@AwayFromZerostr as AwayFromZerostr,
@CapvalueActivestr as CapvalueActivestr,
@Activenum as Activenum,
@ActiveSta as ActiveSta
From #tem2
Drop Table #tem2
END
--导出表格时查询H5的数据
IF((EXISTS (SELECT * FROM dbo.Item WHERE ItemName LIKE '%H5%' AND PlatId=@Mtype)) AND @isExcel=1)
BEGIN
SELECT *
INTO #tem3
FROM (
SELECT Phonetype, Lmarkid, Reg, TouristNumber, Ddate, Mtype, Subname, Active, login3num, PayQuantityA, PayQuantityB, PayQuantityC, PayQuantityD, PayAmountA, PayAmountB, PayAmountC, PayAmountD, Recent2, Recent2login, Recent3, Recent3login, Recent7,
Recent7login, Percentage, Unitprice, Gtype, Capvalue, RegConsume, TouristConsume, DownloadNumber, DailyActiveUsers, Dtake, param1, OnlyTouristNumber, OnlyDownloadNumber, CompanyName, ROW_NUMBER() over(order by Ddate DESC) as pageID
FROM [dbo].[xxxxxxxxx] With ( Nolock )
where [Ddate]>=@Benintime
And [Ddate]<@Endtime
And Mtype = @Mtype
And 1 = (Case When @Lmarkid <> '' And Lmarkid IN ( select col from [dbo].[f_splitComma](@Lmarkid,','))Then 1
When @Lmarkid = '' Then 1
Else 0 End )
) as a
--循环判断概率(游标)@@@@@@@@@@@@@@@@@@@
--创建游标
DECLARE @cursorB CURSOR;
--设定游标欲操作的数据集
SET @cursorB = CURSOR FOR select Active,Capvalue,Percentage from #tem3
--打开游标
OPEN @cursorB;
--移动游标指向到第一条数据,提取第一条数据存放在变量中
FETCH NEXT FROM @cursorB INTO @Active,@Capvalue,@Percentage;
--如果上一次操作成功则继续循环
WHILE ( @@fetch_status = 0 )
BEGIN
If ( @ss = '1' )
Begin
Set @ActiveSta += Round(@Active * (1 - @Percentage),0);
Set @AwayFromZerostr += Round(@Active * (1 - @Percentage),0);
End
Else
Begin
Set @ActiveSta += @Active;
Set @AwayFromZerostr += @Active
End
If ( @Capvalue <> 0 )
Begin
If ( @Capvalue >= Round(@Active * (1 - @Percentage),0) )
Begin
Set @Activenum += Round(@Active * (1 - @Percentage),0);
End
Else
Begin
Set @Activenum += @Capvalue
End
End
Else
Begin
Set @Activenum += Round(@Active * (1 - @Percentage),0);
End
If ( @Capvalue < Round(@Active * (1 - @Percentage),0) And @Capvalue > 0 )
Begin
Set @CapvalueActivestr += @Capvalue;
End
Else
Begin
Set @CapvalueActivestr += Round(@Active * (1 - @Percentage),0);
End
--继续提下一行
FETCH NEXT FROM @cursorB INTO @Active,@Capvalue,@Percentage;
END;
--关闭游标
CLOSE @cursorB;
--删除游标
DEALLOCATE @cursorB;
SELECT *
FROM #tem3
WHERE pageID BETWEEN @startRow AND @endRow
Select COUNT(1)
From #tem3
Select SUM(Reg) as Reg,
SUM(Login3num) as Login3num,
SUM(PayAmountA) as PayAmountA,
SUM(PayAmountB) as PayAmountB,
SUM(PayAmountC) as PayAmountC,
SUM(PayAmountD) as PayAmountD,
SUM(PayQuantityA) as PayQuantityA,
SUM(PayQuantityB) as PayQuantityB,
SUM(PayQuantityC) as PayQuantityC,
SUM(PayQuantityD) as PayQuantityD,
SUM(DailyActiveUsers) as DailyActiveUsers,
SUM(Dtake) as Dtake,
@AwayFromZerostr as AwayFromZerostr,
@CapvalueActivestr as CapvalueActivestr,
@Activenum as Activenum,
@ActiveSta as ActiveSta
From #tem3
Drop Table #tem3
END
end
经典sql分页
最新推荐文章于 2023-12-20 15:54:35 发布