xml地图|网站地图|网站标签 [设为首页] [加入收藏]
从指定字符串中获取数字的方法,统计分页一些
分类:数据库

USE [QPTreasureDB]
GO
/****** Object: StoredProcedure [dbo].[GameStatistics] Script Date: 2018/8/16 10:33:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[GameStatistics]
@Type INT,
@GameType INT,
@Year INT,
@Month INT,
@Day INT,
@PageIndex INT,
@PageSize INT
AS -- 属性设置
SET NOCOUNT ON;

-- 执行逻辑
BEGIN
IF (@Type = 0)
BEGIN
IF (@GameType = 2)
BEGIN
SELECT SUM(-lwinscore) AS TotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS PlayIn, --玩入
SUM(- (lwinscore + lalladdscore)) AS PlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = CASE @Year
WHEN -1 THEN YEAR(insertTime)
ELSE @Year END
AND MONTH(insertTime) = CASE @Month
WHEN -1 THEN MONTH(insertTime)
ELSE @Month END
AND DAY(insertTime) = CASE @Day
WHEN -1 THEN DAY(insertTime)
ELSE @Day END;
END;
IF (@GameType = 3)
BEGIN
SELECT YEAR(insertTime) Year,
SUM(-lwinscore) AS RichTotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS RichPlayIn, --玩入
SUM(- (lwinscore + lalladdscore)) AS RichPlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
GROUP BY YEAR(insertTime);
END;
END;
ELSE IF (@Type = 1) --月统计
BEGIN
SELECT @Year Year,
MONTH(insertTime) Month,
SUM(-lwinscore) AS RichTotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS RichPlayIn, --玩入
SUM(- (lwinscore + lalladdscore)) AS RichPlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
GROUP BY MONTH(insertTime);
END;
ELSE IF (@Type = 2) --日统计
BEGIN
SELECT @Year Year,
@Month Month,
DAY(insertTime) Day,
SUM(-lwinscore) AS RichTotalProfitAndLoss, --总盈亏
SUM(lalladdscore) AS RichPlayIn, --玩入
SUM(- (lwinscore + lalladdscore)) AS RichPlayOut --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
GROUP BY DAY(insertTime);
END;
ELSE IF (@Type = 3) --日统计
BEGIN
DECLARE @RecordCount INT;
SELECT @RecordCount = COUNT(*)
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
AND DAY(insertTime) = @Day
GROUP BY dwUserID,
lwinscore,
lalladdscore,
lAreaScore1,
lAreaScore2,
lAreaScore3,
lAreaScore4,
lAreaScore5,
insertTime;
DECLARE @TotalProfitAndLoss VARCHAR(255)
DECLARE @TotalPlayIn VARCHAR(255)
DECLARE @TotalPlayOut VARCHAR(255)
DECLARE @PageCount INT

SELECT 
@PageCount=COUNT(dwUserID),
@TotalProfitAndLoss=SUM(-lwinscore), --总盈亏
@TotalPlayIn=SUM(lalladdscore), --玩入
@TotalPlayOut=SUM(- (lwinscore + lalladdscore)) --玩出
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
AND DAY(insertTime) = @Day;
DECLARE @StartIndex INT ,
@EndIndex INT
SELECT @StartIndex = ( @PageIndex - 1 ) * @PageSize + 1 ,
@EndIndex = @PageIndex * @PageSize;
WITH Pager
AS ( SELECT ROW_NUMBER() OVER (ORDER BY dwUserID ASC) AS Id,
dwUserID,
@PageCount PageCount,
@TotalProfitAndLoss AS TotalProfitAndLoss, --总盈亏
@TotalPlayIn AS TotalPlayIn, --玩入
@TotalPlayOut AS TotalPlayOut, --玩出
-lwinscore AS RichProfitAndLoss, --总盈亏
lalladdscore AS RichPlayIn, --玩入
- (lwinscore + lalladdscore) AS RichPlayOut, --玩出
lAreaScore1,
lAreaScore2,
lAreaScore3,
lAreaScore4,
lAreaScore5,
insertTime
FROM [QPPlatformDB].[dbo].[FiveStarRecord]
WHERE YEAR(insertTime) = @Year
AND MONTH(insertTime) = @Month
AND DAY(insertTime) = @Day
GROUP BY dwUserID,
lwinscore,
lalladdscore,
lAreaScore1,
lAreaScore2,
lAreaScore3,
lAreaScore4,
lAreaScore5,
insertTime)
SELECT *
FROM Pager
WHERE Id BETWEEN @StartIndex AND @EndIndex

END;
END;

转自:http://www.maomao365.com/?p=6410

昨天一台SQL Server 2008R2的数据库在凌晨5点多抛出下面告警信息:

 

摘要:

 

下文主要分享从指定字符串或列中获取数字信息,如下所示:
实验环境:sql server 2000

 The log scan number (620023:3702:1) passed to log scan in database 'xxxx' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:从指定字符串中获取数字的方法,统计分页一些

上一篇:没有了 下一篇:记开发个人图书收藏清单小程序开发,mysql如何把
猜你喜欢
热门排行
精彩图文