呵呵,几乎成标题党了,今天要写个脚本查询7天内所有有登录玩家的数据,要求按注册日期统计,虽然是个简单的问题,但是关键是用户表有30个,分别是user0, user1......到user29 ,光是把它们UNION ALL起来,我都够郁闷了,你想象下脚本有多长吧,一大堆堆在那儿,光是复制都让你难以忍受。
SELECT
Id,
CONVERT
(
VARCHAR
(
10
),Create_Time,
120
), Last_Login_Time
FROM
user0
UNION ALL
SELECT Id, CONVERT ( VARCHAR ( 10 ),Create_Time, 120 ), Last_Login_Time FROM user1
UNION ALL
SELECT Id, CONVERT ( VARCHAR ( 10 ),Create_Time, 120 ), Last_Login_Time FROM user2
.........
UNION ALL
SELECT Id, CONVERT ( VARCHAR ( 10 ),Create_Time, 120 ), Last_Login_Time FROM user1
UNION ALL
SELECT Id, CONVERT ( VARCHAR ( 10 ),Create_Time, 120 ), Last_Login_Time FROM user2
.........
呵呵,下面是我用动态SQL 改写的,呵呵,简洁多了
DECLARE
@cmdText
VARCHAR
(
8000
);
DECLARE @userIndex INT ;
SET @cmdText = '' ;
SET @userIndex = 0 ;
WHILE @userIndex < 30
BEGIN
IF ( @userIndex != 29 )
SELECT @cmdText = @cmdText + ' SELECT Id,Create_Time, Last_Login_Time FROM '
+ ' dbo.user ' + CONVERT ( VARCHAR , @userIndex )
+ ' UNION ALL ' + CHAR ( 10 ); -- 换行
ELSE
SELECT @cmdText = @cmdText + ' SELECT Id,Create_Time, Last_Login_Time FROM '
+ ' dbo.user ' + CONVERT ( VARCHAR , @userIndex ) ;
SET @userIndex = @userIndex + 1 ;
END ;
SELECT @cmdText = ' SELECT CONVERT(VARCHAR(10),T.Create_Time, 120) AS Create_Time ,COUNT(0) AS RecordNum FROM ( '
+ @cmdText +
' ) T WHERE DATEDIFF(d,Last_Login_Time,GETDATE()) < 7 GROUP BY CONVERT(VARCHAR(10),Create_Time, 120) ' ;
-- PRINT @cmdText
EXEC ( @cmdText );
DECLARE @userIndex INT ;
SET @cmdText = '' ;
SET @userIndex = 0 ;
WHILE @userIndex < 30
BEGIN
IF ( @userIndex != 29 )
SELECT @cmdText = @cmdText + ' SELECT Id,Create_Time, Last_Login_Time FROM '
+ ' dbo.user ' + CONVERT ( VARCHAR , @userIndex )
+ ' UNION ALL ' + CHAR ( 10 ); -- 换行
ELSE
SELECT @cmdText = @cmdText + ' SELECT Id,Create_Time, Last_Login_Time FROM '
+ ' dbo.user ' + CONVERT ( VARCHAR , @userIndex ) ;
SET @userIndex = @userIndex + 1 ;
END ;
SELECT @cmdText = ' SELECT CONVERT(VARCHAR(10),T.Create_Time, 120) AS Create_Time ,COUNT(0) AS RecordNum FROM ( '
+ @cmdText +
' ) T WHERE DATEDIFF(d,Last_Login_Time,GETDATE()) < 7 GROUP BY CONVERT(VARCHAR(10),Create_Time, 120) ' ;
-- PRINT @cmdText
EXEC ( @cmdText );
这里得提提 CHAR(10)这个,个人感觉非常有用,刚开始的时候,没有加换行符,把这段脚本输出的时候,格式比较乱,很多时候,复杂的动态脚本,都需要输出,查看,调试一番, 就像PRINT @cmdText 那样,如果输出格式比较乱,自己调整需要花费一定功夫,在写的时候,如果注意脚本格式了,那就要省很多功夫了。而且也有助于以后维护,修改。