数据库中有很多表，每个平台（充值平台）对应一张表，
表结构共同部分如下：
Username,svrId,gameid,amount,paytime
Gameid由6位正整数组成，前面三位为游戏ID，后面三位为平台（充值平台）ID，
需要查询结果如下：
存储过程如下：
ALTER PROCEDURE proc_getStatisticalInformation
@account VARCHAR(50) = '',
@gameNo INT = -1,--游戏编号，3位整数
@platNo INT = -1,--平台编号，3位整数
@svrNo INT = -1,
@startTime DATETIME='1900-01-01',
@endTime DATETIME='1900-01-01'
--,@isFirst BIT=-1 --是否首次充值，1是，0否
AS
DECLARE @allTable VARCHAR(4000)
SET @allTable = '
select tab.userName,tab.svrId,SUBSTRING(CAST(gameId AS VARCHAR(6)),1,3) AS gameName,SUBSTRING(CAST(gameId AS VARCHAR(6)),4,3) AS platName,tab.amount,tab.payTime from (
SELECT userName,gameId,svrId,realamount/100 AS amount,complatetime AS payTime FROM dbo.skPayorder
UNION ALL
SELECT userName,gameId,svrId,[money],paytime FROM danglePayorder
/*
...省去N多表
*/
) as tab
'
DECLARE @sql VARCHAR(5000)
SET @sql='declare @t table(id int identity(1,1), username varchar(50),svrid INT,gameName INT,platName INT,amount DECIMAL(18,2),paytime DATETIME)
INSERT INTO @t ( username, svrid, gameName, platName, amount, paytime )
SELECT tab.userName,tab.svrId,tab.gameName,tab.platName,tab.amount,tab.payTime FROM
(
'+@allTable+'
) AS tab
WHERE 1=1 '
IF(LTRIM(RTRIM(@account)) != '')
BEGIN
SET @sql += ' AND tab.username LIKE ''%'+@account+'%'''
END
IF(@gameNo != -1)
BEGIN
SET @sql+= ' AND SUBSTRING(CAST(gameId AS VARCHAR(6)),1,3) = '+RTRIM(@gameNo)+' '
END
IF(@platNo != -1)
BEGIN
SET @sql += ' AND SUBSTRING(CAST(gameId AS VARCHAR(6)),4,3) = '+RTRIM(@platNo)+' '
END
IF(@svrNo != -1)
BEGIN
SET @sql += ' AND tab.svrId='+RTRIM(@svrNo)+' '
END
IF(@startTime > '1900-01-01')
BEGIN
SET @sql += ' AND tab.payTime >= '''+RTRIM(@startTime)+''' '
END
IF(@endTime > '1900-01-01')
BEGIN
SET @sql += ' AND tab.payTime <= '''+RTRIM(@endTime)+''' '
END
SET @sql += ' SELECT id,username,svrid,gameName,platName,amount,paytime FROM @t '
--用户充值总金额，用户充值总次数，充值用户名，游戏名称————（按照用户名和用户名分组排序）
--SET @sql += ' SELECT sum(amount) as pricecount,count(username) paycount,username,gamename FROM @t group by username,gamename '
--充值总金额，充值总人数（根据不同的游戏来统计总人数，然后累加）
SET @sql += ' SELECT sum(_tab.pricecount) totalAmountRecharge,count(_tab.username) totalNumberRecharge from ( SELECT sum(amount) as pricecount,count(username) paycount,username,gamename FROM @t group by username,gamename ) AS _tab '
-------------SET @sql += ' select * from ('+@allTable+') as tab where tab.paytime<'''+RTRIM(@startTime)+''''
--首充(注：@t是综合查询的结果集，继承了开始时间和结束时间。)
IF(@startTime > '1900-01-01')
BEGIN
SET @sql += ' select count(tab1.payinfo) newpay from ( SELECT distinct (username+RTRIM(gamename)) payinfo FROM @t where username!='''' and username is not null group by username,gamename ) as tab1
where tab1.payinfo not in ( select distinct (tab2.username+RTRIM(tab2.gamename)) from ('+@allTable+') as tab2 where tab2.username is not null and tab2.username!='''' and tab2.paytime<'''+RTRIM(@startTime)+''' )
'
END
ELSE
BEGIN
IF(@endTime > '1900-01-01')
BEGIN
SET @sql += ' select count(distinct (tab2.username+RTRIM(tab2.gamename))) from ('+@allTable+') as tab2 where tab2.username is not null and tab2.username!='''' and tab2.paytime<'''+RTRIM(@endTime)+''' '
END
ELSE
BEGIN
SET @sql += ' select count(distinct (tab2.username+RTRIM(tab2.gamename))) from ('+@allTable+') as tab2 where tab2.username is not null and tab2.username!='''' '
END
END
EXEC(@sql)
--调用
--最后一个参数没写
--EXECUTE proc_getStatisticalInformation '',-1 ,-1 ,-1 ,'2013-10-28 13:08:45'
查询结果如下：
注意：必须是根据用户名和游戏名来分组
需求：
1、想知道有没有什么可以改进的地方，主要是经验方面的问题。
2、希望大家看看业务，这么实现是否正确。
3、新充值人数的概念就是在筛选的人员（根据游戏和用户名分组）里面，在筛选结果集之前充值的用户。例如：我选取2013-08-10到今天的用户，我得确认这些用户里面哪些在2013-08-10之前没有充值过的。